Syntax error help

[Holmes]

Searched through for help but to no avail.

I'm usnig 2 combo box the with the following code in the after update event of the first/master combo box:

Combo34.RowSource = "Select tblMasterDB.Instrument " & _
"FROM tblMasterDB " & _
"WHERE tblMasterDB.Hospital Name = '" & Combo32.Value & "' " & _
"ORDER BY tblMasterDB.Instrument;"

So the frist combo box selects the site address. THe second then selects the
modules on that site. All info is contained withing the table MasterDB.

When I run the form and select the site then go to select a module from the second combo box I am presented with this error message:

Syntax error (missing operator) in query expression tblMasterDB.Site Name = 'The site name'

Any ideas?

Many thanks.

 

[allan57]

the problen is the space in your field name 'Hospital Name', when you have a space in your field name enclose it in square brackets ie

Combo34.RowSource = "Select tblMasterDB.Instrument " & _
"FROM tblMasterDB " & _
"WHERE tblMasterDB.[Hospital Name] = '" & Combo32.Value & "' " & _
"ORDER BY tblMasterDB.Instrument;"

for the future try to avoid uses spaces in field name as it makes coding a lot easier.

 

[Holmes]

I have not got the syntax error anymore but when I try to select the value from the second combo I am persented with:
enter parameter value
tblMaster.Instrument
"Field to enter text"
Ok button or Cancel.

any ideas?

 

[Dreamweaver]

Try changing:
"ORDER BY tblMasterDB.Instrument;"
For
" ORDER BY Instrument"

And This I would do Like:
'" & Combo32.Value & "' "
Like
'" & Combo32 & "'"

 

[Holmes]

Still the same problem.

Any other ideas?

 

[Bat17]

check spelling.
is there really a field called "Instrument" in table "tblMaster"?

Peter

 

[boblarson]

Just as an additional side note - You should change your object names to be meaningful so that people who need to support it after you are gone, or if you come back to it after not working on it for a long time, can know what is what.

For example, you should change Combo32 to a meaningful name like cboInstrument (not saying it should necessarily be that name, but something meaningful to what the combo does).

It will save you a lot of hassle later trying to figure out what each is referring to in your code.

 

[Holmes]

Have changed names, just did not re-paste them.
Also annotate my code and use indents for future proofiong. Funnily enough also produce flow charts for my code.