Combo Box/query

[Prayder]

I have a combo box that provides a drop down arrow with a list of vendors and I need to have it changed to only display the vendor that is associated with the item ordered. The Row source for the combo box is:

SELECT First(VENDORS.Vendor) AS FirstOfVendor, PartVendor.VendorID
FROM VENDORS INNER JOIN (Parts INNER JOIN PartVendor ON Parts.PartNum = PartVendor.PartNum) ON VENDORS.Vendor_ID = PartVendor.VendorID
GROUP BY PartVendor.VendorID;

CODE]
 
Would really appreciate any help that can be provided!

 

[Privateer]

You might have a problem with the way the tables are built. You have a one to many relationship between the vendor and the parts with a linker table in the middle. You might want to rename the fields so you can follow the logic easier. In the vendor's table the primary key, (VendorID) and in the parts table (PartsID) are the only two you need in the linker table. Then in the table between then, (the linker), your PartsVendor table, you have just those two fields. So it looks something like this:

VendorID PartID
1 1
1 2
2 5
3 1
3 5

What this is telling you is that vendors one and three sell part one and that vendor two and three sell part five, etc, which is what you want in the combo box so...

In the gotfocus event of the combo, you create the sql string below and assign it as the recordsource for the combo. And I will include the error trapping should the part number be empty.

I create a criteria string and an strSQL string, so I will give you all the code

Dim Criteria as string
Dim strSQL as string

strSQL = "Select tblVendors.Vendor from tblVendors Inner Join tblPartsVendor On tblVendor.VendorID=tblPartVendor.VendorID) Inner Join tblParts on tblPaftVendor.PartID = tblParts.PartsID"

if isnull(me.partsnum.value) = True then
strsql = strsql & ";" 'This gets all vendors
else
Criteria = "([tblParts].[PartsID]=" & me.PartsNum.value & ")"
strsql = strsql & " WHERE (" & Criteria & ");"
end if

The me part refers to the name of the field being displayed on the form. You are concatenating a number into a text string. When it evaluates it will look like:

([tblParts].[PartsID]=1)

Now the last thing is to set this string to the recordsource for the combo box:

me.VendorOption.rowsource = strsql

I am worried that this is too much change for you but this logic is what I use for all my combos so I hope this helps you out. Good luck.

 

[Prayder]

you mentioned the "recordsource" for the combo box but dont you mean rowsource? The recordsource would be for the entire form not just the combo box right?