Prev: select value form combo box on a web page
Next: Ms Access got an error and quit
From: Goldar on 1 Nov 2009 19:06
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...

From: Stuart McCall on 1 Nov 2009 19:24
"Goldar" <Goldar(a)discussions.microsoft.com> wrote in message
news:8BB80B78-9EFE-4A40-8D4C-44A4B5D252C8(a)microsoft.com...
>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


From: BB on 1 Nov 2009 22:11
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"
--
B.

Live Long and Prosper...


"Goldar" wrote:

> 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...
>
From: Klatuu on 2 Nov 2009 08:37
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.

"Goldar" <Goldar(a)discussions.microsoft.com> wrote in message
news:8BB80B78-9EFE-4A40-8D4C-44A4B5D252C8(a)microsoft.com...
>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...
>


From: Dirk Goldgar on 2 Nov 2009 09:25
"Klatuu" <dahargis(a)verizon.net> wrote in message
news:%23lu5UG8WKHA.3696(a)TK2MSFTNGP02.phx.gbl...
> 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.)

--
Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html

(please reply to the newsgroup)

 |  Next  |  Last
Pages: 1 2
Prev: select value form combo box on a web page
Next: Ms Access got an error and quit