Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

SQL select from....using variable question 1

Status
Not open for further replies.

h3lm3t

Technical User
May 27, 2003
16
GB
Hi ther thanx for looking, i need help with a SQL syntax question. I'm not very good with SQL and have never used in within VBA.

I have 2 list boxes that provide the criteria for the SQL
They work correctly and return the correct values, they are both string data types:

manufactName as string
categoryName as string

' set values of both

manufactName = Me.lstManufacturer.Value
categoryName = Me.lstStockCat.Value

'my sql statement

DoCmd.RunSQL "SELECT tblStock.[fkManufacturer ID], tblStock.[fkStockCategory ID], tblStock.[Stock ID], tblStock.[Stock Description], tblStock.[Stock Boundary Level], tblStock.[Stock Level] FROM tblStock WHERE (((tblStock.[fkManufacturer ID])= manufactName ) AND ((tblStock.[fkStockCategory ID])= categoryName));"

ERROR 2343, statement requires an SQL statement


Any help is appreciated, thanx very much in advance.
 
Hi, VBA doesn't know that the variables you've defined are inside the " " of your SQL statement so I think you want it to look like this:

DoCmd.RunSQL "SELECT tblStock.[fkManufacturer ID], tblStock.[fkStockCategory ID], tblStock.[Stock ID], tblStock.[Stock Description], tblStock.[Stock Boundary Level], tblStock.[Stock Level] FROM tblStock WHERE (((tblStock.[fkManufacturer ID])= " & manufactName & " ) AND ((tblStock.[fkStockCategory ID])= " & categoryName & "));"
 
Hi h3lm3t,

I think you do have some quotes missing, I haven’t checked properly, but your main problem is that you don’t do it that way. You use DoCmd.RunSQL for Action queries (updates, etc.); they don't return Selected records.

To Select you need to open a recordset:

Code:
Dim tjDb as DAO.Database
Dim tjRst as DAO.Recordset

Set tjDb = CurrentDb
Set tjRst = tjDb.OpenRecordset(
Code:
"Your SQL goes here"
Code:
,dbOpenDynaset)
Code:
 ‘ Now move amongst your records using tjRst.MoveFirst, .Movenext, etc
‘ Reference your fields as , for example, tjRst![StockID]
Code:

Enjoy,
Tony
 
Thanx for the help, Tony i really didnt want to go down the recordset route, coz i thought it would be a major can of worms, but you forced me, and now using recordsets i have the info i needed from the tables. Thank you very much !!!!
 
SQL String should be:
"select * from tblStock WHERE [fkManufacturer ID]= '" & manufactName & "'"
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top