Cascading Combo Boxes - Dependent Box is Blank

Question

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

Is tblPOHeader.PONumber really defind as string? If it is defined as number then try it without the single-quotes: "WHERE tblPOHeader.PONumber = " & Me.PONumber & " " & _. — Pᴇʜ

Newbie Newbie · Accepted Answer · 2019-08-22T15:50:12

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.

Cascading Combo Boxes - Dependent Box is Blank

1 Answers