How to use Seek vs FindFirst

  • Thread starter Thread starter Goldar
  • Start date Start date
G

Goldar

I am trying to update a table with data from a query, but I keep getting
errors like "invalid command" ,etc. What I have is:
My query calculates a sum for each type of record in another table (TOTAL).
I have a master table (MASTER) I want to update with these sums. My VBA code
is:
Case 1-- using seek, edit and update
rstSum=db.openRecordset(qryBudgetAsTotals,dbOpenDynaset)
rst=db.openrecordset(master,dbOpenTable)
rstSum.index="Primary Key"
rst.MoveLast
rst.MoveFirst
with rst
do while not .eof
.seek ="=",rst![Key1],rst![Key2]
if not .nomatch then
.edit
.![Total field] = rstSum![Total field]
.update
endif
.movenext
.loop
' I never get this far. The code compiles ok, but I get errors trying to seek

Alternatively, I tried the same code using FindFirst with a similar result.
The code is
CASE 2--Using FindFirst logic
rstSum=db.openRecordset(qryBudgetAsTotals,dbOpenDynaset)
rst=db.openrecordset(master,dbOpenTable)
rst.MoveLast
rst.MoveFirst
with rst
do while not .eof
findfirst "[key1]='" & rstSum![Key1] & "' and [Key2]='" &
rstSum![key2] & "'"
if not .nomatch then
.edit
.![Total field] = rstSum![Total field]
.update
endif
.movenext
.loop
I know I could do this by using a query or two, but why don't these work?

Thanks for your help...
 
Goldar said:
I am trying to update a table with data from a query, but I keep getting
errors like "invalid command" ,etc. What I have is:
My query calculates a sum for each type of record in another table
(TOTAL).
I have a master table (MASTER) I want to update with these sums. My VBA
code
is:
Case 1-- using seek, edit and update
rstSum=db.openRecordset(qryBudgetAsTotals,dbOpenDynaset)
rst=db.openrecordset(master,dbOpenTable)
rstSum.index="Primary Key"
rst.MoveLast
rst.MoveFirst
with rst
do while not .eof
.seek ="=",rst![Key1],rst![Key2]
if not .nomatch then
.edit
.![Total field] = rstSum![Total field]
.update
endif
.movenext
.loop
' I never get this far. The code compiles ok, but I get errors trying to
seek

Alternatively, I tried the same code using FindFirst with a similar
result.
The code is
CASE 2--Using FindFirst logic
rstSum=db.openRecordset(qryBudgetAsTotals,dbOpenDynaset)
rst=db.openrecordset(master,dbOpenTable)
rst.MoveLast
rst.MoveFirst
with rst
do while not .eof
findfirst "[key1]='" & rstSum![Key1] & "' and [Key2]='" &
rstSum![key2] & "'"
if not .nomatch then
.edit
.![Total field] = rstSum![Total field]
.update
endif
.movenext
.loop
I know I could do this by using a query or two, but why don't these work?
Click to expand...

Because these lines:

rstSum=db.openRecordset(qryBudgetAsTotals,dbOpenDynaset)
rst=db.openrecordset(master,dbOpenTable)

both need to be prefixed with the keyword Set, ie:

Set rstSum=db.openRecordset(qryBudgetAsTotals,dbOpenDynaset)
Set rst=db.openrecordset(master,dbOpenTable)

When assigning objects to variables, Set must be used.

Also, at the end of the procedure, it is best practice to close the
recordsets you opened, and to release the variable by setting it to nothing,
eg:

rstSum.Close
Set rstSum = Nothing
 
Goldar

A quick question, in your code, you are attempting to open a recordset base
a variable called: qryBudgetAsTotals

Is this supposed to be a variable, or is that the actual name of a query you
want to open, in whic case you need to enclose it in quotation marks, ie:
"qryBudgetAsTotals"

The same goes with "master"
 
Use the FindFirst. You code is DAO code, but Seek is actually an ADO
method. It will work with DAO if the recordset is a "table" recordset.
That is, not a linked table, but a table in the front end mdb.
 
Klatuu said:
Use the FindFirst. You code is DAO code, but Seek is actually an ADO
method.
Click to expand...

David, I don't think I would put it this way. Both the DAO and ADO
Recordset objects have a "Seek" method. They have slightly different
restrictions, though, related to the differences between DAO and ADO. As
you mentioned, the DAO Seek method can only be used with a table-type
recordset, while the ADO Seek method can only be used with a server-side
cursor *and* a command-type of adCmdTableDirect. (I got the ADO information
from the help file; I've never used the ADO Seek method.)
 
Nor have used ADO. Perhaps I wasn't clear enough. I did not mean to say
that it is only ADO.
 
Back
Top