I Need Help with a query please

[Jon123]

Ok please dont tear me apart to much, I will try my very best to explain what I'm trying to do.

I have 3 tables "Tble-PartProductCodeID", Tble-PartsLookup", Tble-ChamberCodes"

The Tble-PartProductCodeID hold the PartID and ChamberID.

The Tble-PartsLookup holds the Partnumber, description, and chambertype with a PartID that is linked to the PartID in tble-PartProductCodeID.

The tble-ChamberCodes holds the ChamberID and the Chambertype, the chamberID is also linked to the ChamberID in the tble-PartProductcodeID

So basically each chamber type has its own unique ID and each partnumber has its own unique ID as well.

So in a drop down list on a form I only want to see a list of parts that match a field on my form. On my form I have a field called ChamberType that will have a value but my list is blank. If I remove the criteria and run the query I get all the records in the partlookup. Here is my query SQL

SELECT [Tble-PartsLookup].PartID, [Tble-PartsLookup].PartNumber, [Tble-PartsLookup].Description, [Tble-PartsLookup].ProcessKitPart, [Tble-ChamberCodes].ChamberID, [Tble-ChamberCodes].Chambertype
FROM ([Tble-PartProductCodeID] INNER JOIN [Tble-PartsLookup] ON [Tble-PartProductCodeID].PartID = [Tble-PartsLookup].PartID) INNER JOIN [Tble-ChamberCodes] ON [Tble-PartProductCodeID].ChamberID = [Tble-ChamberCodes].ChamberID
WHERE ((([Tble-ChamberCodes].Chambertype)=[Forms]![Frm-RadiancePlusCentura]![PartList]));

 

[CJ_London]

You do not need to refer to the control by going via the form - try

WHERE [Tble-ChamberCodes].Chambertype=[PartList]

Note, if your form is continuous then you need to refresh the combo for each row - the rowsource relates to the control which is the same for each row so if partlist changes the 'other' rows will not display the data unless they happen to be common to both partlists. (Hope that makes sense!)

 

[Jon123]

Where do I put this Where statement? In the query?

jon

 

[CJ_London]

yes - to replace

WHERE ((([Tble-ChamberCodes].Chambertype)=[Forms]![Frm-RadiancePlusCentura]![PartList]))

in your original post - I'm assuming this is the rowsource to your combo box and not a separate query

 

[Jon123]

So the combo box is on a sub form and the partlist is on the main form

 

[Jon123]

Still can't get it working. If I build a query the where statement puts a expr1 in front of the where? When I put the where statement on the row source of the combo I get an error "the record source 'where statement' specified on this form or report does not exist.

 

[CJ_London]

So the combo box is on a sub form and the partlist is on the main form

In that case you need

WHERE [Tble-ChamberCodes].Chambertype=[Parent].[PartList]

And your SQL code needs to be the rowsouce of the combo, not a separate query

 

[Jon123]

Still having problems. This time I do not get errors but the combo box is empty. Just to confirm this is the code I pasted into the Row source of my combobox?

SELECT [Tble-PartsLookup].PartID, [Tble-PartsLookup].PartNumber, [Tble-PartsLookup].Description, [Tble-PartsLookup].ProcessKitPart, [Tble-ChamberCodes].Chambertype, [Tble-ChamberCodes].ChamberID
FROM ([Tble-PartProductCodeID] INNER JOIN [Tble-PartsLookup] ON [Tble-PartProductCodeID].PartID = [Tble-PartsLookup].PartID) INNER JOIN [Tble-ChamberCodes] ON [Tble-PartProductCodeID].ChamberID = [Tble-ChamberCodes].ChamberID WHERE (("WHERE [Tble-ChamberCodes].Chambertype"=[Parent].[PartList]));

 

[CJ_London]

you have not understood my instructions - paste this code directly into your rowsource - do not open the query builder.

SELECT [Tble-PartsLookup].PartID, [Tble-PartsLookup].PartNumber, [Tble-PartsLookup].Description, [Tble-PartsLookup].ProcessKitPart, [Tble-ChamberCodes].Chambertype, [Tble-ChamberCodes].ChamberID
FROM ([Tble-PartProductCodeID] INNER JOIN [Tble-PartsLookup] ON [Tble-PartProductCodeID].PartID = [Tble-PartsLookup].PartID) INNER JOIN [Tble-ChamberCodes] ON [Tble-PartProductCodeID].ChamberID = [Tble-ChamberCodes].ChamberID WHERE [Tble-ChamberCodes].Chambertype=[Parent].[PartList]

 

[Jon123]

sorry I will try this now