.rowsource not working

Question

My form has an unbound combo box (comboManager) that shows the name of a manager (the actual bound data called LevelID is a number.) ComboManager's internal query is as follows: SELECT DISTINCT tblLevelPosition.LevelID, tblLevelPosition.FullName, tblLevelPosition.Level, tblLevelPosition.Active FROM tblLevelPosition WHERE (((tblLevelPosition.Level)="Manager") AND ((tblLevelPosition.Active)=-1)) ORDER BY tblLevelPosition.FullName;

Column ct: 4

Column Width: 0";1";0";0"

Bound Column: 1

Many times the name (or rather the bound number) fits within the query parameters. For example, in this record: Smith is a manager and shows up in the combo box listing when the form is opened.  However sometimes the manager gets promoted and their level changes to "Director".  Now they don't show up in the combo box because I am specifically querying for "Manager".  So to compensate I removed the "Manager" criteria and once again all is well.  However, when the user clicks on the combo box they see all the Managers in the dropdown list along with Reviewers, Directors, Staffers, etc…

So to compensate once again I have tried to introduce code into Event Procedures.  In the "On Mouse Down" EP I created the following:

Me.comboManager.RowSource

= "SELECT DISTINCT tblLevelPosition.LevelID, tblLevelPosition.FullName, tblLevelPosition.Level, tblLevelPosition.Active " & vbCrLf & _

                        "FROM tblLevelPosition " & vbCrLf & _

                        "WHERE (((tblLevelPosition.Level)=""Manager"") AND ((tblLevelPosition.Active)=-1)) " & vbCrLf & _

                        "ORDER BY tblLevelPosition.FullName;"

This drop down menu is exactly what I need to see... just the "Managers".  But this is where the problem occurs. I can scroll down through the choices but cannot click on a user and thus cannot update my combo box.   One other note: since the combo box is unbound, I have an update code of sorts in the After Update EP.

To makes this somewhat more convoluted I have altered the rowsource again on the “on lost focus” ep to revert the choices back to the original internal query. That way, this would (in theory) allow for the original manager to still be viewed. I realize this is a long winded presentation but there is a lot of stuff going on here and I wanted to portray it as best as I could so a solution can be found. Thank you.

Answer 1

The culprit was the Mouse Event.  I moved the code from OnMouse to GotFocus and viola!  Works great.  I can't tell you how many hours I spent trying to troubleshoot this issue.  I could have sworn that I tried the GotFocus EP (as well as most of the others)... looks like I should start writing these attempts down.  Thanks Dirk.

Answer 2

I suspect the mouse event is getting in the way.  Try using the Enter and Exit events.

Answer 3

My form has an unbound combo box (comboManager) that shows the name of a manager (the actual bound data called LevelID is a number.) ComboManager's internal query is as follows: SELECT DISTINCT tblLevelPosition.LevelID, tblLevelPosition.FullName, tblLevelPosition.Level, tblLevelPosition.Active FROM tblLevelPosition WHERE (((tblLevelPosition.Level)="Manager") AND ((tblLevelPosition.Active)=-1)) ORDER BY tblLevelPosition.FullName;

Column ct: 4

Column Width: 0";1";0";0"

Bound Column: 1

Many times the name (or rather the bound number) fits within the query parameters. For example, in this record: Smith is a manager and shows up in the combo box listing when the form is opened.  However sometimes the manager gets promoted and their level changes to "Director".  Now they don't show up in the combo box because I am specifically querying for "Manager".  So to compensate I removed the "Manager" criteria and once again all is well.  However, when the user clicks on the combo box they see all the Managers in the dropdown list along with Reviewers, Directors, Staffers, etc…

So to compensate once again I have tried to introduce code into Event Procedures.  In the "On Mouse Down" EP I created the following:

Me.comboManager.RowSource

= "SELECT DISTINCT tblLevelPosition.LevelID, tblLevelPosition.FullName, tblLevelPosition.Level, tblLevelPosition.Active " & vbCrLf & _

                        "FROM tblLevelPosition " & vbCrLf & _

                        "WHERE (((tblLevelPosition.Level)=""Manager"") AND ((tblLevelPosition.Active)=-1)) " & vbCrLf & _

                        "ORDER BY tblLevelPosition.FullName;"

This drop down menu is exactly what I need to see... just the "Managers".  But this is where the problem occurs. I can scroll down through the choices but cannot click on a user and thus cannot update my combo box.   One other note: since the combo box is unbound, I have an update code of sorts in the After Update EP.

To makes this somewhat more convoluted I have altered the rowsource again on the “on lost focus” ep to revert the choices back to the original internal query. That way, this would (in theory) allow for the original manager to still be viewed. I realize this is a long winded presentation but there is a lot of stuff going on here and I wanted to portray it as best as I could so a solution can be found. Thank you.

 

Would it work to use the combo's GotFocus event instead, to set its rowsource to the more restricted query?  That may be good enough for you, though it would still blank out the combo if the user tabs through it while it holds a value that represents a no-longer-manager.

I can see a problem with the MouseDown event, that event will fire when the user clicks their selection, and assigning the rowsource will requery the combo box and generally make the combo do things you don't want.  *But* maybe if you check the rowsource first (in the MouseDown event) and only reassign it if it hasn't already been set to the more restricted query, then -- maybe -- the problem wouldn't occur.  It may be worth a try.

---

Dirk Goldgar, MS Access MVP

Access tips: www.datagnostics.com/tips.html