SQL select from....using variable question 1

[h3lm3t]

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.

 

[Keph]

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 & "));"

 

[TonyJollans]

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:

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

Set tjDb = CurrentDb
Set tjRst = tjDb.OpenRecordset(

"Your SQL goes here"

,dbOpenDynaset)

 ‘ Now move amongst your records using tjRst.MoveFirst, .Movenext, etc
‘ Reference your fields as , for example, tjRst![StockID]

Enjoy,
Tony

 

[h3lm3t]

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 !!!!

 

[young]

SQL String should be:
"select * from tblStock WHERE [fkManufacturer ID]= '" & manufactName & "'"