How to use Seek vs FindFirst

[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...

 

[Stuart McCall]

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?

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

 

[BB]

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"

 

[Klatuu]

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.

 

[Dirk Goldgar]

Use the FindFirst. You code is DAO code, but Seek is actually an ADO
method.

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.)

 

[Klatuu]

Nor have used ADO. Perhaps I wasn't clear enough. I did not mean to say
that it is only ADO.