Updating A Field With Lookup

[ineedahelp]

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

[MMcCarthy]

 
Private Sub cmbCaptureLSData_Click()
Dim db As DAO.Database
Dim 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   '** If symbol is field in recordset but use ! **
varRate = DLookup("[symbol]", "DailyPrice", "[symbol] = '" & strSymbol & "' AND [DATE] = " & #10/20/06# & ")"
 
If IsNull(varRate) Then
    rst.Edit
    rst!LSRate=0
    rst.Update
Else
    rst.Edit
    rst!LSRate=varRate
    rst.Update
End IF
 
 '***what about it being a number/long field? How can I put "NA" in this field***
'You can't I've set it to 0
 
rst.MoveNext
Loop
 
End If
 
rst.Close
Set rst = Nothing
Set db = Nothing
 
End Sub
 
 

[ineedahelp]

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!

[ineedahelp]

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

[NeoPa]

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.

[ineedahelp]

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!

[MMcCarthy]

Have you created a query called qryDummy? If not then this won't work.

Set qdf = db.QueryDefs("qryDummy")

[ineedahelp]

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

[MMcCarthy]

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