SQL relationship code problem

[wizcow]

hi, i have a find form where info is selected from combo boxes
then displays it in the embedded sub form.

table = Parts
form=Search
subform=SearchSub
combo box =Vendor
=PartType
=Model

when i select something from "cboVendor" my code works, but because
the table "Parts.VendorID" is in a relationship with the table "Vendors" it only displays a number in "cboVendor". I need help with coding the relationship in.
here is the afterupdate procedure from the combo box "cboVendor"

Private Sub cboVendor_AfterUpdate()

Dim strSQL As String
Dim strSQLSF As String

'clear the combo boxes
cboPartType = Null
cboModel = Null

'set the list in the sub form
strSQLSF = "SELECT * FROM qrySearchParts "
strSQLSF = strSQLSF & " WHERE qrySearchParts.VendorID = " & cboVendor & ""
strSQLSF = strSQLSF & " WHERE qrySearchParts.VendorName = " & cboVendor & ""

Me!SearchSub.LinkChildFields = "VendorID"
Me!SearchSub.LinkMasterFields = "VendorID"
Me.RecordSource = strSQLSF
Me.Requery

End Sub


[This message has been edited by wizcow (edited 12-30-2001).]

[This message has been edited by wizcow (edited 12-30-2001).]

[This message has been edited by wizcow (edited 12-30-2001).]

[This message has been edited by wizcow (edited 01-02-2002).]

 

[Jack Cowley]

Create a query using the two tables. Base your SQL statement on the query.

 

[wizcow]

The query worked great, but...

When I changed the "VendorID" to "VendorName" in the query and code, the combo box "cboVendor" displays the name of the vendor (just the way I want it to), but when the code from the afterupdate function runs I get an error message box, 'Enter Parameter Value' naming the vendor that I selected and under the vendor name is a textbox waiting for an entry.

What did I miss here?

Tom

[This message has been edited by wizcow (edited 12-30-2001).]

 

[Jack Cowley]

You want to maintain the VendorID in the query as well as the Vendor Name. You need the VendorID and Vendor name in the Combo box so you can select a Vendor based in the VendorID.... Hope that makes sense.

 

[wizcow]

Thank you for your patience Jack.

The query was the key.

I have changed the code in the original message.

Tom

[This message has been edited by wizcow (edited 12-30-2001).]

[This message has been edited by wizcow (edited 01-02-2002).]