Link to home
Start Free TrialLog in
Avatar of urjudo
urjudoFlag for United States of America

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
Avatar of PatHartman
PatHartman
Flag of United States of America image

The rowsource combo is what controls the list.  Regardless of what value is actually stored in the column, the combo will only show items in the list.  So, if you select all employers, you see the employer name but if you change the rowsource to select only active employers and the one you are looking at is inactive, it will not show.

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.
Avatar of urjudo

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:
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

Open in new window

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.
Avatar of urjudo

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
Avatar of urjudo

ASKER

here is the code I put

Private Sub Combo84_BeforeUpdate(Cancel 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
ASKER CERTIFIED SOLUTION
Avatar of PatHartman
PatHartman
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial