urjudo
asked on
combo box query
Hi Experts,
I have a problem that I could not figure it out. I have a combo box in a employer form, this combo box is list all the employers. on the employer table I have a field Status (A- for active, I- for Inactive). on the employer form the combo box row source is "qryEmployerName" this query show every employer, doesn't matter A or I. I have another query "qryEmployerNameActive" that I put on the on Enter under the combo box , code is combo84.rowsource = "qryEmployerNameActive", however what ever the employer we entered before now is Inactive did not show, it should be show it if you don't click on it because it is not in the on Entered query. I did this on some other combo box, it works fine but not on this one. I totally confused. any ideas. (I would like users to see the previous employer)
Thanks
I have a problem that I could not figure it out. I have a combo box in a employer form, this combo box is list all the employers. on the employer table I have a field Status (A- for active, I- for Inactive). on the employer form the combo box row source is "qryEmployerName" this query show every employer, doesn't matter A or I. I have another query "qryEmployerNameActive" that I put on the on Enter under the combo box , code is combo84.rowsource = "qryEmployerNameActive", however what ever the employer we entered before now is Inactive did not show, it should be show it if you don't click on it because it is not in the on Entered query. I did this on some other combo box, it works fine but not on this one. I totally confused. any ideas. (I would like users to see the previous employer)
Thanks
ASKER
do you have an example of what you suggested such as code on before update and change the rowsource query
I'm not sure my code will make any sense to you but here it is:
The columns of the combo's rowSource are a zero-based array so the first column is 0, the second is 1, the third is 2. Adjust the column number to point to where in the select clause the ActiveFlg occurs.
Private Sub LanguagePreference_BeforeUpdate(Cancel As Integer)
If Me.LanguagePreference.Column(2) = False Then
MsgBox "This preference is inactive and may not be selected. Choose another.", vbOKOnly
Cancel = True
Me.LanguagePreference.Undo
Exit Sub
End If
End Sub
This is just about the only place that I erase the user's entry with .undo. Normally, I just give them an error message and leave the invalid entry.The columns of the combo's rowSource are a zero-based array so the first column is 0, the second is 1, the third is 2. Adjust the column number to point to where in the select clause the ActiveFlg occurs.
ASKER
I tried it but when I select the inactive one, I still can do that and no message pop up.
here is my query columns
Employer Addr City St Zip EmployerID Status
we only store the employerID in the table
here is my query columns
Employer Addr City St Zip EmployerID Status
we only store the employerID in the table
ASKER
here is the code I put
Private Sub Combo84_BeforeUpdate(Cance l As Integer)
If Me.Combo84.Column(5) = False Then
MsgBox "This preference is inactive and may not be selected. Choose another.", vbOKOnly
Cancel = True
Me.Combo84.Undo
Exit Sub
End If
End Sub
Private Sub Combo84_BeforeUpdate(Cance
If Me.Combo84.Column(5) = False Then
MsgBox "This preference is inactive and may not be selected. Choose another.", vbOKOnly
Cancel = True
Me.Combo84.Undo
Exit Sub
End If
End Sub
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
What I do in this situation is to always leave the rowsource showing the entire list. I sort by the active flag so the active items are at the top of the list. Then I have code in the combo's before update event that checks the active flag and I prevent them from selecting an inactive item. Therefore, the list always shows everhthing but my code prevents inactive items from being selected. Sorting the inactive items to the bottom, keeps them out of the user's way when he wants to select a new item. He has to go out of his way and scroll the list to get to the inactive items and if he does that, I still won't let him choose one. I also set the column widths properties so that the inactive flag shows and I set the headers property. ONe more thing - if there are a lot of inactive items, I change the rowsource query so that "active" is hidden since I don't need to see it and I find it clutters the list.