Cascading combo boxes on form. (1 Viewer)

[beckie1234]

I am using this code to force the population of a combo box on my form.

Private Sub Line_AfterUpdate()
Me.MachineCenter.Enabled = True
Me.MachineCenter = Empty
Me.MachineCenter.RowSource = " SELECT tblLine.lineName, tblLineMach.lineID, tblLineMach.machID, tblMachCent.machName" & _
" FROM tblMachCent INNER JOIN (tblLine INNER JOIN tblLineMach ON tblLine.lineID = tblLineMach.lineID)" & _
" WHERE tblLineMach.lineID" & _
" ON tblLine.lineID = tblLineMach.machID = tblMachCent.machID;"

End Sub

It is going from tblLine to tblLineMach to tblMachCent.
tblLineMach is a bridge table to reduce redundant data.
IT is having a problem with the From statement
Any Suggestions?????????

 

[rainman89]

you need to select from all the tables not just the tblmachcent

 

[beckie1234]

I add all the tables to the from clause but I still get a syntex error in the from clause. Any suggestions of how to state it correctly?

 

[beckie1234]

If anyone has any suggestions please I really need help. Is there any other way to force cascading combo boxes other than this or should I keep plugging away at the code I am currently working with?

 

[RoyVidar]

It seems you've entered the WHERE clause in the middle of the FROM clause.

I'm hopeless with SQL, but do try and create the same query in the query designer, make it work as it should, then copy/paste from the SQL view to VBE - all in one line at first, then enter the line continuation/concatenation, after you know it works.

You'd probably have a criterion, too, wouldn't you, something like

...WHERE tblLineMach.lineID = " & Me![Line].Value

 

[beckie1234]

Actually that is what I did. I used the query in design view to see the results I was looking for and this is what I got from it. So I putit in the code and I get a syntax error in the from statement. Help?!?

 

[RoyVidar]

I'm sorry, that's not produced by the query designer. You have, probably when copying it, put the WHERE clause in the middle of the FROM clause.

 

[beckie1234]

When I created it in design view I went to the SQL and that is how I came up with this particular code for the query. I know the origional code worked along these same lines and I got the help for it on this forum, so I am not sure what you are meaning about placing the where clause in this statement. I can't figure out what I am missing?

 

[RoyVidar]

So, as I've alredy said, create it again, and look at the differences. In particular the FROM clause and the WHERE clause. If you've done it once, it shouldn't take much time, should it?

Basically, the syntax is

SELECT <column list>
FROM someTable INNER JOIN someOtherTable on someTable.ID = someOtherTable.ID
WHERE someField = someValue

while you have

SELECT <column list>
FROM someTable INNER JOIN someOtherTable  <- start of FROM clause
WHERE someField = someValue               <- WHERE clause
on someTable.ID = someOtherTable.ID       <- end of FROM clause

Your WHERE clause is within the FROM clause, which is why it doesn't like this statement. And again, that is not something the query designer has done. You have, probably when copying or concatenating in code, you have switched the two last lines, or something. There could be more errors that I don't see, but that should be easily found by just creating the query again.

 

[beckie1234]

" SELECT tblLine.lineID, tblLineMach.lineID, tblLineMach.machID,
tblMachCent.machID" & _
" FROM tblLine, tblLineMach, tblMachCent" & _
" INNER JOIN tblLine = tblLineMach = tblMachCent ON tblLine.lineID = tblLineMach.lineID AND tblLineMach.machID = tblMachCent.machID" & _
" WHERE tblLine.lineID = tblLineMach.machID = tblMachCent.machID;"

I still get a Syntax error with this modified version.
tblLIne = lineID (PK), lineName
tblLineMach = lineID , machID (Composite PK)
tblMachCent = machID (PK), machName.
These are the tables that I am using.
Please don't think I am an idiot. I am just trying to figure out how to make it follow a path to force it to put in particular choices when the line is chosen, by using the PK's.

 

[beckie1234]

Can anyone help I am still stuck and I looked at the regular cascading combo boxes that go from one box to another but so for none are trying to cross a bridge table and I am at a complete standstill?????????????????????????

 

[beckie1234]

This is the new code and it is pulling values now but only the numbers not names and it is not differentiating between the choices to force the cascading effect of making a particular choice. Any suggestions??

Private Sub Line_AfterUpdate()
Me.MachineCenter.Enabled = True
Me.MachineCenter = Empty
Me.MachineCenter.RowSource = " SELECT tblLine.lineID, tblLineMach.lineID, tblLineMach.machID, tblMachCent.machID" & _
" FROM tblLine, tblLineMach, tblMachCent" & _
" WHERE tblLine.lineID = tblLineMach.lineID and tblLineMach.machID = tblMachCent.machID;"


End Sub

Private Sub MachineCenter_AfterUpdate()
Me.Category.Enabled = True
Me.Category = Empty
Me.Category.RowSource = " SELECT tblMachCent.machID, tblMachCat.machID, tblMachCat.catID, tblCategory.catID" & _
" FROM tblMachCent, tblMachCat, tblCategory" & _
" WHERE tblMachCent.machID = tblMachCat.machID and tblMachCat.catID = tblCategory.catID;"

End Sub

Private Sub Category_AfterUpdate()
Me.Category.Requery
End Sub