j e
asked on
Problem updating recordset
I have a recordset open and want to update the value of a field in the current record (see code below). However, I keep getting an "INSERT" error message. What am I missing? Thanks for your help. je
Dim rsstep As Recordset
Set rsstep = CurrentDb.OpenRecordset("t
rsstep.MoveFirst
Do While Not rsstep.EOF
rsstep.Edit
rsstep("JCL0-template").Va
'rsstep![JCL0-template] = """" & ststepstring & """" (I tried this too...)
rsstep.Update
rsstep.MoveNext
Loop
Dim rsstep As Recordset
Set rsstep = CurrentDb.OpenRecordset("t
rsstep.MoveFirst
Do While Not rsstep.EOF
rsstep.Edit
rsstep("JCL0-template").Va
'rsstep![JCL0-template] = """" & ststepstring & """" (I tried this too...)
rsstep.Update
rsstep.MoveNext
Loop
Last Comment
ASKER
Jeff,
Thanks for the quick resonse... I keep getting err.number 3265
"INSERT INTO tbl_report_HLQ_temp SELECT tbl_abc.* FROM tbl_abc;"
I have no idea where it's getting that from. The tbl_report_HLQ_temp table is populated several steps earlier by appending data from the tbl_ace table.
I am attaching the sample code....
Thanks for the help,
Je
C---SOX-Temp-2--Data-Lockdown-Issue-Anal
Thanks for the quick resonse... I keep getting err.number 3265
"INSERT INTO tbl_report_HLQ_temp SELECT tbl_abc.* FROM tbl_abc;"
I have no idea where it's getting that from. The tbl_report_HLQ_temp table is populated several steps earlier by appending data from the tbl_ace table.
I am attaching the sample code....
Thanks for the help,
Je
C---SOX-Temp-2--Data-Lockdown-Issue-Anal
I am confused...
The code you posted was using VBA to update a recordset.
The error you are reporting is as if you were using SQL to update the value...?
Furthermore, you state that you want to:
"update the value of a field in the current record
...yet the code you posted loops through all the records in the table...
Not sure abut all your code there or how it all works together.
But to update a value of the current record, a recordset is typically not required...
In any event, ...This is a simple, working example of how I "update the value of a field in the current record"
...and how to update ALL records in the table
JeffCoachman
Database41.mdb
The code you posted was using VBA to update a recordset.
The error you are reporting is as if you were using SQL to update the value...?
Furthermore, you state that you want to:
"update the value of a field in the current record
...yet the code you posted loops through all the records in the table...
Not sure abut all your code there or how it all works together.
But to update a value of the current record, a recordset is typically not required...
In any event, ...This is a simple, working example of how I "update the value of a field in the current record"
...and how to update ALL records in the table
JeffCoachman
Database41.mdb
ASKER
Jeff..... yes that is the confusing thing....I am using vba code to update a recordset (or at least trying to).
I am looping through tbl_HLQ (Dim rs As Recordset) and for each record in that table I am executing several other things resulting in a report related to the tbl_HLQ record being processed. the final step before running the report is to loop through the tbl_report_HLQ_temp table (Dim rsstep As Recordset) and update the value of the first field for each record. So I'm not sure if the loop within a loop is causing the problem.
Can't you have more than one recordset open at a time? I am using different record set variables.
Thanks again for the help,
je
I am looping through tbl_HLQ (Dim rs As Recordset) and for each record in that table I am executing several other things resulting in a report related to the tbl_HLQ record being processed. the final step before running the report is to loop through the tbl_report_HLQ_temp table (Dim rsstep As Recordset) and update the value of the first field for each record. So I'm not sure if the loop within a loop is causing the problem.
Can't you have more than one recordset open at a time? I am using different record set variables.
Thanks again for the help,
je
SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER CERTIFIED SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
Isolating the code allowed a closer look at the statements. Including the dbOpenDynaset option resolved the problem.
Microsoft Access
Microsoft Access is a rapid application development (RAD) relational database tool. Access can be used for both desktop and web-based applications, and uses VBA (Visual Basic for Applications) as its coding language.
226K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
TRUSTED BY
Quick guess, ...
Try something like this:
Dim rsstep As Recordset
Dim ststepstring as String
ststepstring ="YourText"
Set rsstep = CurrentDb.OpenRecordset("t
rsstep.MoveFirst
Do While Not rsstep.EOF
rsstep.Edit
rsstep("JCL0-template").Va
' Or like this:
'rsstep![JCL0-template] = ststepstring
rsstep.Update
rsstep.MoveNext
Loop
Let me know if I am missing, or not understanding something...
JeffCoachman