Stumped on form design

  • Thread starter Thread starter Don Moore
  • Start date Start date
D

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.
 
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
 
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.
 
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
 
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)/
 
Back
Top