CODEWITHSUNDEEP
vbams-access
Newbie
Newbie

Reputation: 55

Cascading Combo Boxes - Dependent Box is Blank

I have a table called POHeader, which shows PO Numbers and the Vendor Number for the provider, as well as other data points for each PO.

Sometimes, we exempt a specific PO from all of our delivery rules, and I created an Access form to store these exemptions. Right now, there are two combo boxes, one for PO Number and one for Vendor Number, on the form. I want the Vendor Number combo box to filter to only vendors who match the PO Number selected by the user (PO numbers may not be unique in some cases).

Here is the VBA I wrote for the AfterUpdate event on the PO Number combo box:

Private Sub PONumber_AfterUpdate()
    Me.VendorNumber.RowSource = "SELECT tblPOHeader.VendorNumber " & _
                           "FROM tblPOHeader " & _
                           "WHERE tblPOHeader.PONumber = '" & Me.PONumber & "' " & _
                           "ORDER BY tblPOHeader.VendorNumber"

End Sub

My issue is that the Vendor Number combo box goes blank once a PO Number is selected. What did I do wrong?

Private Sub PONumber_AfterUpdate()
    Me.VendorNumber.RowSource = "SELECT tblPOHeader.VendorNumber " & _
                           "FROM tblPOHeader " & _
                           "WHERE tblPOHeader.PONumber = '" & Me.PONumber & "' " & _
                           "ORDER BY tblPOHeader.VendorNumber"

End Sub

Upvotes: 0

Views: 124

Answers (1)

Newbie
Newbie

Reputation: 55

I solved it! Instead of a VBA for AfterUpdate, I updated the row source for the Vendor Number field to: enter image description here

Then just did a requery for AfterUpdate.

Upvotes: 0

Related Questions