Stumped on form design


Don Moore

I am building a purchase order database and I want to have a combo box on my
form that when I select a vendor from the vendor field it narrows down the
list in the vendor contact field to just the contacts related to the vendor.
I was sucessful in getting it to work by putting a criteria in the row source
but it will not update from page to page without hitting the refresh
everytime. Any suggestions?

Hopefully I've explained myself well enough.



Scott Lichtenberg

Try changing the row source of your contacts combo box in the AfterUpdate
event of your vendor field.

cboContacts.Rowsource = "SELECT ContactName FROM Contacts WHERE Vendor =
" & Me!Vendor

Don Moore

I tried that code and I get a compile error. I of course changed the field
names to match my tables. I admit VBA is beyond my skill level.

Jeff Boyce


" ... update from page to page ... "

We aren't there, we can only guess what you mean by that.

More info, please...


Jeff Boyce
Microsoft Office/Access MVP




Did you compile the code? If not, open the code window and click
Debug.Compile. This will highlight the line that is giving the error.

Two observations: If Vendor is a text field you need:
Me.cboContacts.Rowsource = "SELECT ContactName FROM Contacts " & _
"WHERE Vendor = """ &
Me.Vendor & """"
Also, you will need the same code in the form's Current event, except you
will need to allow for Null in the Vendor field:
Dim strRow as String

If IsNull(Me.Vendor) Then
strRow = ""
strRow = "SELECT ContactName FROM Contacts " & _
"WHERE Vendor = """ & Me.Vendor & """"
End If

Me.cboContacts.RowSource = strRow

You could omit Dim strRow as String, use Me.cboContacts.RowSource instead of
strRow in the If IsNull ...End If block of code, and leave out:
Me.cboContacts.RowSource = strRow

Just showing another approach (still assuming Vendor is a text field)/

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question