Linked Lists using multiple tables in where conditions 1

[MrMode]

I have a form with multiple list boxes on it.
Users are able to filter results as they make selections in list boxes.
Everything works well until...

In the third list box they make a selection from, the field is held in tblRegion rather than tblSupplierMapCodes and the list box the results appear in goes blank.

Me.List4.RowSource = _
"SELECT PortalData.[Company ID], PortalData.[Company Name] " & _
"FROM tblSupplierMapCodes INNER JOIN (tblSupplierMappingLinkTable INNER JOIN (PortalData INNER JOIN (tblUKPostcodes INNER JOIN SCCDataForServer ON tblUKPostcodes.ID = SCCDataForServer.PostalBrickID) ON PortalData.[Company ID] = SCCDataForServer.[Company ID]) ON tblSupplierMappingLinkTable.SCCID = SCCDataForServer.[SCC Data ID]) ON tblSupplierMapCodes.ID = tblSupplierMappingLinkTable.SupplierCode " & _
"Where tblSupplierMapCodes.[Category Heading] = " & Chr(34) & Me.List0 & Chr(34) & _
"And tblSupplierMapCodes.[Requirements Heading] = " & Chr(34) & Me.List2 & Chr(34) & _
"And tblRegion.[Region] = " & Chr(34) & Me.List6 & Chr(34)

I have had this working really nicely when all the data is in the same table, but this is stressing me out. How do I set the From Statement so that the last filter works, or is there a better way around this?

Thanks for your help

 

[dhookom]

What happens when you add a line of code after the assignment above

debug.Print Me.List4.RowSource

Then open the debug window (press Ctrl+G) and copy the SQL statement into a new, blank query.

Duane
Hook'D on Access
MS Access MVP

 

[MrMode]

OMG!

Seems to be working.

Let me try and break it, but thank you!

 

[MrMode]

Sorry, spoke too soon, was looking in all the wrong places.

This is what came back in the immediate window when I added the text to the end of the statement and tried making a selection from the list box:

SELECT PortalData.[Company ID], PortalData.[Company Name] FROM tblSupplierMapCodes INNER JOIN (tblSupplierMappingLinkTable INNER JOIN (PortalData INNER JOIN (tblUKPostcodes INNER JOIN SCCDataForServer ON tblUKPostcodes.ID = SCCDataForServer.PostalBrickID) ON PortalData.[Company ID] = SCCDataForServer.[Company ID]) ON tblSupplierMappingLinkTable.SCCID = SCCDataForServer.[SCC Data ID]) ON tblSupplierMapCodes.ID = tblSupplierMappingLinkTable.SupplierCode Where tblSupplierMapCodes.[Category Heading] = "Catering"And tblSupplierMapCodes.[Requirements Heading] = "Catering (Equipment) Supplies"And tblRegion.[Region] = "North Somerset"

The words in quotation marks, are the ones that I selected, but the list box is still blank.

Hmm...

 

[PHV]

Lack of spaces in the WHERE clause between the " & AND

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[dhookom]

MrMode,
You were asked to "copy the SQL statement into a new, blank query." Running the query should have pointed out the missing spaces or other issues.

Duane
Hook'D on Access
MS Access MVP

 

[MrMode]

It keeps throwing up the Enter parameter dialogue box asking me for tblRegion.Region

 

[MrMode]

It does not seem to be making the link between the tblUKPostcodes and tblRegion to pull the tblRegion.Region value from...

 

[PHV]

asking me for tblRegion.Region
So, what is the real name of the Region column in the tblRegion table ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[MrMode]

Sorry, I know this is painful for you.

I reattached the region tbl to the query, and put in the parameter, this is what the query syntax looks like now:

SELECT PortalData.[Company ID], PortalData.[Company Name], tblRegion.region
FROM (tblSupplierMapCodes INNER JOIN (tblSupplierMappingLinkTable INNER JOIN (PortalData INNER JOIN (tblUKPostcodes INNER JOIN SCCDataForServer ON tblUKPostcodes.ID = SCCDataForServer.PostalBrickID) ON PortalData.[Company ID] = SCCDataForServer.[Company ID]) ON tblSupplierMappingLinkTable.SCCID = SCCDataForServer.[SCC Data ID]) ON tblSupplierMapCodes.ID = tblSupplierMappingLinkTable.SupplierCode) INNER JOIN tblRegion ON tblUKPostcodes.RegionId = tblRegion.RegionId
WHERE (((tblSupplierMapCodes.[Category Heading])="Catering") AND ((tblSupplierMapCodes.[Requirements Heading])="Drinking water") AND ((tblRegion.region)="somerset"));


The From statement starts with tblSupplierMapCodes, but Region is in a different table. I will try and VBA this SQl and see if it works. What do you think?

 

[MrMode]

Nope, lost. How do I get it to 'nest' the region part within the syntax that I already have?

"FROM tblSupplierMapCodes INNER JOIN (tblSupplierMappingLinkTable INNER JOIN (PortalData INNER JOIN (tblUKPostcodes INNER JOIN SCCDataForServer ON tblUKPostcodes.ID = SCCDataForServer.PostalBrickID) ON PortalData.[Company ID] = SCCDataForServer.[Company ID]) ON tblSupplierMappingLinkTable.SCCID = SCCDataForServer.[SCC Data ID]) ON tblSupplierMapCodes.ID = tblSupplierMappingLinkTable.SupplierCode " & _
"Where tblSupplierMapCodes.[Category Heading] = " & Chr(34) & Me.List0 & Chr(34) & _
"And tblSupplierMapCodes.[Requirements Heading] = " & Chr(34) & Me.List2 & Chr(34) & _
"And tblRegion.[Region] = " & Chr(34) & Me.List6 & Chr(34)

 

[MrMode]

sorted

This seems to be working, I will keep testing and confirm it is done - thanks for your help so far!

Me.List4.RowSource = _
"SELECT PortalData.[Company ID], PortalData.[Company Name], tblRegion.region " & vbCrLf & _
"FROM (tblSupplierMapCodes INNER JOIN (tblSupplierMappingLinkTable INNER JOIN (PortalData INNER JOIN (tblUKPostcodes INNER JOIN SCCDataForServer ON tblUKPostcodes.ID = SCCDataForServer.PostalBrickID) ON PortalData.[Company ID] = SCCDataForServer.[Company ID]) ON tblSupplierMappingLinkTable.SCCID = SCCDataForServer.[SCC Data ID]) ON tblSupplierMapCodes.ID = tblSupplierMappingLinkTable.SupplierCode) INNER JOIN tblRegion ON tblUKPostcodes.RegionId = tblRegion.RegionId " & vbCrLf & _
"Where tblSupplierMapCodes.[Category Heading] = " & Chr(34) & Me.List0 & Chr(34) & _
" And tblSupplierMapCodes.[Requirements Heading] = " & Chr(34) & Me.List2 & Chr(34) & _
" And tblRegion.[Region] = " & Chr(34) & Me.List6 & Chr(34)

It is all about the damn brackets and sneaking tblRegion into the select element! Who knew

 

[dhookom]

It isn't clear if you have pasted the SQL from your "what the query syntax looks like now" into a blank query to see if it returns any records. If it doesn't, have you fiddled with the SQL to see if you can make it work?

Duane
Hook'D on Access
MS Access MVP

 

[MrMode]

I did, and it does, it all works now - thanks again for giving me a push in the right direction.

It was not picking up the tblRegion because it was not Selecting it in the first part of the statement and not able to link to it in the second, messing around with the query in design and sql view allowed me to sort out the link and capture the need to refer to it in the select part of the statement!

Man computers are relentless - just like being married

Thanks again.