Show All records in a combo box that is based on a combo box

[Robert M]

I have develeped a select query that allows me to list in a secondary combo box only those items that are based from the first combo box. However, I need to have the secondary combo box be able to show it's entire list if the first combo box has nothing selected. I.E. IsNull

The following Select statements and vba program is what I am using.

First Combo Box - Select Statement from Source Properties
SELECT qryTypeList.TypeID, qryTypeList.Type FROM qryTypeList;

VBA is
Private Sub TypeFilter_AfterUpdate()
On Error GoTo Oops
Me.LkUpItm = Null
Me.LkUpItm.Requery
Me.LkUpItm = Me.LkUpItm.ItemData(0)
If IsNull(Me.TypeFilter) Then
Me.FilterOn = False
Else
Me.Filter = "Type = """ & Me.TypeFilter & """"
Me.FilterOn = True
End If
Get_Out:
Exit Sub
Oops:
MsgBox Err.Description
Resume Get_Out
End Sub

Secondary Combo Box Select Statement from Source Properties
SELECT qryItems.Item_ID, qryItems.Item, qryItems.Type FROM qryItems WHERE (((qryItems.Type)=Forms!InvtBal_frm!TypeFilter));

VBA is
Private Sub LkUpItm_AfterUpdate()
On Error GoTo Oops
Dim rs As DAO.Recordset
If Not IsNull(Me.LkUpItm) Then
If Me.Dirty Then
Me.Dirty = False
End If
Set rs = Me.RecordsetClone
rs.FindFirst "[Item]='" & Me![LkUpItm] & "'"
If rs.NoMatch Then
MsgBox [LkUpItm] & " Not Found"
Else
Me.Bookmark = rs.Bookmark
End If
Set rs = Nothing
[LkUpItm] = Null
End If
Get_Out:
Exit Sub
Oops:
MsgBox Err.Description
Resume Get_Out
End Sub

Thank you for your time and patience with any help on this matter

Robert M

 

[GregRun]

The best way to do this is to set the SQL for the second combo box's row source in the first box's afterupdate.

Private Sub TypeFilter_AfterUpdate()
    Dim mySQL as String
    
    If Me.Dirty Then
        Me.Dirty = False
    End If
    
    mySQL = "SELECT Item_ID, Item, Type FROM qryItems"
    
    If Nz(Me.TypeFilter) <> "" Then
        mySQL = mySQL & " WHERE Type='" & Me.TypeFilter & "'"
    End If

    Me.LkUpItm.RowSource = mySQL
End Sub

 

[Robert M]

Thank you GregRun

That does make very good sence, Probably better than what I found on the internet that I have in a "Remove Filter" button. and when the form Opens.
Me.FilterOn = False
[TypeFilter] = ""
[LkUpItm] = ""
If (TypeFilter) = "" Then
Me.LkUpItm.RowSource = "Select qryItems.Item_ID,qryItems.Item FROM qryItems"
Else
Me.LkUpItm.RowSource = "Select qryItems.Item_ID,qryItems.Item FROM qryItems WHERE (((qryItems.Type) = Forms!InvtBal_frm!TypeFilter));"
End If
After about 2 hours of entering and tweaking it I have it working well enough for me.

I have another form that also needs this done for it, I'll use your suggestion for that one. I might tweek it a bit for asthetics. then copy the whole program to my first form. as it doesn't take up as much memory.

Thanks so much for your help on this matter.

Robert M

 

[GregRun]

No problem. Now that you know how to do it, I'm sure you'll find plenty of other places where that will come in handy. It did for me!