By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,945 Members | 1,692 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,945 IT Pros & Developers. It's quick & easy.

VBA value into SQL query

P: 44
Afternoon all
is it possiable to put a value from my VBA code into a SQL statement? my VBA code is as follows
Expand|Select|Wrap|Line Numbers
  1. strLowestRack = "SQLtoFindOldestStockfrmAssignOrders"
  2.  
  3. Stop
  4.  
  5. Set rs = Me.[sqlOrderDetails subform].Form.RecordsetClone
  6.  
  7. Do While Not rs.EOF
  8.  
  9. prodnumber = rs!ProdID
  10. q = rs!Quantity
  11.  
  12.  
  13.     For i = 1 To q
  14.  
  15.         l = DLookup("RackID", strLowestRack) 'change this sql
  16.  
  17.         DoCmd.RunSQL "UPDATE [Location] SET [Location].ProdID = 1 WHERE [Location].RackID =" & l
  18.  
  19.     Next i
  20.  
  21. rs.MoveNext
  22.  
  23. Loop
The value from within this code that is called prodnumber i would like to use in this sql query
Expand|Select|Wrap|Line Numbers
  1. SELECT Location.RackID
  2. FROM Location
  3. WHERE (((Location.ProdID)="prodnumber") 
  4.     AND ((Location.StockAge)=(SELECT MIN(StockAge) 
  5.     FROM Location 
  6.         WHERE Location.ProdID = "prodnumber")));
Where ive put "prodnumber" is actually where i need that value. i have tried [rs]![prodnumber] and [rs]![prodID] with no luck. If someone could point me in the right direction
Thanks very much and for all the previous help i have recieved.
Aug 22 '12 #1

✓ answered by twinnyfo

315Hughes,

Your code would look someting like this:

Expand|Select|Wrap|Line Numbers
  1. Dim strSQL as String
  2.  
  3. strSQL = "SELECT Location.RackID" & _
  4.     " FROM Location" & _
  5.     " WHERE Location.ProdID = " & prodnumber & _
  6.     " AND Location.StockAge = " & _
  7.         " (SELECT MIN(StockAge) FROM Location " & _
  8.         " WHERE Location.ProdID = " & prodnumber & ");" 
  9.  
I've stepped out the code to make it more readable, but it could be on one line. This would have to be within the same sub as above, so that prodnumber is a variable the DB recognizes.

Share this Question
Share on Google+
4 Replies


TheSmileyCoder
Expert Mod 100+
P: 2,321
Dlookup takes 3 parameters, where the last one is optional. You can specify a Where clause as the third parameter, allthough I am not sure it would work in your case.

You cannot use [rs] directly in your query since that is a value only available to the VBA environment.

I could offer up some code that would set the querydefinition to a parsed string which would solve your immediate problem, however, I would like if you could explain WHAT you are trying to achieve, as I feel there may be more efficient ways to approach your problem.
Aug 22 '12 #2

twinnyfo
Expert Mod 2.5K+
P: 3,284
315Hughes,

Your code would look someting like this:

Expand|Select|Wrap|Line Numbers
  1. Dim strSQL as String
  2.  
  3. strSQL = "SELECT Location.RackID" & _
  4.     " FROM Location" & _
  5.     " WHERE Location.ProdID = " & prodnumber & _
  6.     " AND Location.StockAge = " & _
  7.         " (SELECT MIN(StockAge) FROM Location " & _
  8.         " WHERE Location.ProdID = " & prodnumber & ");" 
  9.  
I've stepped out the code to make it more readable, but it could be on one line. This would have to be within the same sub as above, so that prodnumber is a variable the DB recognizes.
Aug 22 '12 #3

P: 44
@TheSmileyCoder
Hi what i am trying to do is a stock system i use the value from prodnumber to search through a location table to find the oldest stock location number based on that prodnumber value.
The SQL query that does it is the one i posted (also called "strLowestRack" in the vba code). The value that is returned is held in the value "l"(rack number with the oldest stock) which comes from the Dlookup search. The value "l" is then used to update work sheets.
I hope this explains it better
Aug 22 '12 #4

zmbd
Expert Mod 5K+
P: 5,397
315Hughes,
It appears that your query would return the newest stock age not the oldest. (line 7 of TwinnyFo's code)
-z
Aug 22 '12 #5

Post your reply

Sign in to post your reply or Sign up for a free account.