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]

Don

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

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

More info, please...

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[BruceM]

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 = ""
Else
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)/