Search code examples
vbams-access

MS Access - Updating Combobox Selection after another Combobox's Selection in Form

I have a combobox in a form called VendorName under a Form. Under a table, I have inserted VendorCurrency in each entry for vendors. Now what I want to do is once the VendorName in the form is chosen, I want the QuotationCurrency combobox to update and select the respective VendorCurrency .

Note that I have another table called Currency, holding all currencies and I use them as Row Source for the combobox QuotationCurrency.

I had written vba script as follows but it didn't work. What should I do?

Private Sub VendorName_AfterUpdate()
Me.QuotationCurrency = DLookup("VendorCurrency", "Vendor", "VendorName = 'Me!VendorName '")
Me.QuotationCurrency.Requery
End Sub
Solution

  • Concatenate the name value:

    Private Sub VendorName_AfterUpdate()
    
    Me!QuotationCurrency.Value = DLookup("VendorCurrency", "Vendor", "VendorName = '" & Me!VendorName.Value & "'")

End Sub

    A requery shouldn't be needed if the rowsource is unchanged.