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

Updating A Field With Lookup

P: 98
Can anyone help me with the code for updating a table of stock symbols with a rate field (LSRate) - number/long - by using dLOOKUP on another database (DAILYPRICE)? I will often get a variable list of symbols that may or may not have a RATE for that day. I need the symbols to stay in the list. I would like to put "NA" , but can leave them blank if necessary. I have made an attempt at the code but know that some of it isn't going to work. Here it is:

Private Sub cmbCaptureLSData_Click()
Dim db As DAO.Database, rst As DAO.Recordset
Dim varRate As Variant
Dim strSymbol As String

Set db = CurrentDb()
Set rst = db.OpenRecordset("LaBranche_102006")

If rst.BOF And rst.EOF Then
MsgBox "No records to process"
Else
rst.MoveFirst
Do Until rst.EOF
strSymbol = rst.symbol ***CAN I DO THIS?****
varRate = DLookup("symbol", "DailyPrice", "symbol = " & strSymbol & "" ***AND DAILYPRICE.DATE = "10/20/06"*****)
****IF THERE IS NO SYMBOL TO MATCH THIS SYMBOL...PUT "na" IN THE FIELD ELSE PUT varRate*****'if there is no value for that day...put "na" in the Rate column...what about it being a number/long field? How can I put "NA" in this field

rst.MoveNext
Loop
End If

End Sub
Oct 25 '06 #1
Share this Question
Share on Google+
8 Replies


MMcCarthy
Expert Mod 10K+
P: 14,534
Expand|Select|Wrap|Line Numbers
  1.  
  2. Private Sub cmbCaptureLSData_Click()
  3. Dim db As DAO.Database
  4. Dim rst As DAO.Recordset
  5. Dim varRate As Variant
  6. Dim strSymbol As String
  7.  
  8. Set db = CurrentDb()
  9. Set rst = db.OpenRecordset("LaBranche_102006")
  10.  
  11. If rst.BOF And rst.EOF Then
  12.     MsgBox "No records to process"
  13. Else
  14.  
  15. rst.MoveFirst
  16. Do Until rst.EOF
  17. strSymbol = rst!symbol   '** If symbol is field in recordset but use ! **
  18. varRate = DLookup("[symbol]", "DailyPrice", "[symbol] = '" & strSymbol & "' AND [DATE] = " & #10/20/06# & ")"
  19.  
  20. If IsNull(varRate) Then
  21.     rst.Edit
  22.     rst!LSRate=0
  23.     rst.Update
  24. Else
  25.     rst.Edit
  26.     rst!LSRate=varRate
  27.     rst.Update
  28. End IF
  29.  
  30. '***what about it being a number/long field? How can I put "NA" in this field***
  31. 'You can't I've set it to 0
  32.  
  33. rst.MoveNext
  34. Loop
  35.  
  36. End If
  37.  
  38. rst.Close
  39. Set rst = Nothing
  40. Set db = Nothing
  41.  
  42. End Sub
  43.  
  44.  
Oct 26 '06 #2

P: 98
Your solutions was fantastic! Now that I see the code it makes great sense! My only problem was that when I ran it on a table with 3700 records, it got through 400 records successfully and then the program "stopped responding" and I needed to close out of everything. This happened twice. Any suggestions as to why this happened? DailyPrice is an extremely large table with 200,000 plus records. Maybe I should query only the date I am interested in. Should this make a difference? Thank you again for all your support with my many questions!
Oct 26 '06 #3

P: 98
I have tried to create a query with only the info I need instead of the 200,000 plus record table...see code below...I only think that I didn't assign or set the query properly. I get an error #3078 ...can't find the input table or query. Any suggestions? thanks!!!


Private Sub cmbCaptureLSData_Click()

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim varRate As Variant
Dim strSymbol As String
Dim strSQL As String

Set db = CurrentDb()
Set rst = db.OpenRecordset("LaBranche_102006")

strSQL = "SELECT DailyPrice.LocateDate, DailyPrice.Symbol, DailyPrice.MarketPrice " & _
"FROM DailyPrice WHERE (((DailyPrice.LocateDate) = #10/20/2006#)) " & _
"ORDER BY DailyPrice.Symbol;"

Set qdf = db.QueryDefs("qryDummy")
qdf.SQL = strSQL
If rst.BOF And rst.EOF Then
MsgBox "No records to process"
Else

rst.MoveFirst
Do Until rst.EOF
strSymbol = rst!Symbol '** If symbol is field in recordset but use ! **
varRate = DLookup("[MarketPrice]", "" & strSQL & "", "[symbol] = '" & strSymbol & "' AND [LocateDate] = #10/20/06#")

If IsNull(varRate) Then
rst.Edit
rst!LSRate = 0
rst.Update
Else
rst.Edit
rst!LSRate = varRate
rst.Update
End If

rst.MoveNext
Loop

End If

rst.Close
qdf.Close
Set rst = Nothing
Set db = Nothing
Set qdf = Nothing

End Sub
Oct 26 '06 #4

NeoPa
Expert Mod 15k+
P: 31,342
Not Responding is just Access's way of saying that it's still processing or waiting for results.
It's pretty crappy I know, but if you leave it to continue it will often finish happily.
Oct 26 '06 #5

P: 98
I have an alphabetical listing of symbols and after 2 minutes it only got through the b's. Should I not create a subset using a query? If so, I am having trouble assigning it properly so that access recognizes it. any suggestions? Thanks!
Oct 26 '06 #6

MMcCarthy
Expert Mod 10K+
P: 14,534
Have you created a query called qryDummy? If not then this won't work.

Set qdf = db.QueryDefs("qryDummy")
Oct 26 '06 #7

P: 98
Could you please look at my code in post #4? I did use the qrydef you suggested. I ran this program with an existing table and it worked. It must be something with my variable to assign the query OR could it be possible that a TABLE is required? any help? Thanks!!
Oct 26 '06 #8

MMcCarthy
Expert Mod 10K+
P: 14,534
Set qdf = db.QueryDefs("qryDummy")

What I mean is that this only works if there is already a query in the database called qryDummy. Just create a query of anything and name it qryDummy.

Then try running your code again.

The other way to do this is:

Set qdf = db.CreateQueryDefs("qryDummy", strSQL)

But you would have to delete the query each time.




Could you please look at my code in post #4? I did use the qrydef you suggested. I ran this program with an existing table and it worked. It must be something with my variable to assign the query OR could it be possible that a TABLE is required? any help? Thanks!!
Oct 26 '06 #9

Post your reply

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