How do I use a variable in a select statement in vba?
This is a standard query
Select IngredientID, Quantity, from Recipes where RecipeID = 7
This is what I would like to do, but do not know how to.....
dim ID as Integer
Select IngredientID, Quantity, from Recipes where RecipeID = 'ID'
The user will select an ID from a Combobox and then the ID will be used to get specific information from the db.
Any help will be appreciated.
PS: I am using RSView32 and SQLExpress 2008R2.
You must use concatenation to get your variable into the SQL string. The concatenation operator is the ampersand symbol in VBA.
Your answer might be found here: http://stackoverflow.com/questions/11017036/how-to-use-a-sql-select-statement-with-access-vba
I would do something like the following:
Dim SQL as String
Dim ID as String
SQL = "Select IngredientID, Quantity, from Recipes where RecipeID = " & ID
Then run or modify the SQL of the query using the usual ADODB or Microsoft Data query method.
This might help: http://www.fontstuff.com/ebooks/free/fsadoconnectexcel.pdf