ACCESS FEED: Re: [MS_AccessPros] Update table using a recordset

Jumat, 21 Juni 2019

Re: [MS_AccessPros] Update table using a recordset

hi Toukey,

adding on,

this statement affects the record that is showing on the form:

?DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70

not the record in the recordset. If the record on the form is what you want to change, instead of DoMenuItem (which is? archaic), and to be sure it needs to be saved, use this:

with me
?if .dirty then
?.dirty = false
?end if
end with

~~~

you should release your object variables. After

?rst.Close
?rst1.Close

you should have:

set rst = nothing
set rst1 = nothing
set dbs = nothing

~~~

like Duane, I am also wondering if an update query would work better. What is the logic you are trying to accomplish?

have an awesome day,
crystal

free code you can use in your projects
https://msaccessgurus.com/code.htm

On 6/21/2019 8:00 AM, Duane Hookom? [MS_Access_Professionals] wrote:

Toukey,

Have you tried any trouble-shooting such as setting break points, debug.print, or others? What were your results?

Is there a reason you didn't simply use an update query in place of a recordset?

Regards,

Duane

From: MS_Access_Professionals@yahoogroups.com <MS_Access_Professionals@yahoogroups.com> on behalf of toukey1 <no_reply@yahoogroups.com>
Sent: Friday, June 21, 2019 7:49 AM
To: MS_Access_Professionals@yahoogroups.com
Subject: [MS_AccessPros] Update table using a recordset
?

Hi,

The intent of the code below is to update 3 values in the table "tblAllPols" based on the values found in the variable strsql1.? I am getting the correct rows when I run through the code in the "For inti........" statement but it's just not updating the table with the InvoiceNo, Date and Currency.

Set dbs = CurrentDb

strsql1 = "SELECT tblAllPols.SeqNo FROM tblAllPols WHERE [tblAllPols].[Issuedate] = # " & vard & " #" & vno & add2 & add3 & add4 & add8

strSQL = "SELECT * from [tblAllPols]"

Set rst = dbs.OpenRecordset(strSQL)

Set rst1 = dbs.OpenRecordset(strsql1)

rst1.MoveLast

rst1.MoveFirst

noorecords = rst1.RecordCount

For inti = 0 To (noorecords - 1)

? ? ? ? burgarray(0, inti) = rst1!SeqNo

? ? ? ? rst1.MoveNext

? ? Next

saverec = MsgBox("Do you want to save this record?", vbYesNo)

? ? DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70

If saverec = vbYes Then

For inti = 0 To (noorecords - 1)

? ? With rst

? ? ? ? .Edit

? ? ? ? !InvNo = Me!InvoiceNo

? ? ? ? !InvDate = Me!Date

? ? ? ? !Currenc = Me!Currency

? ? ? ? .Update

? ? End With

Next

?rst.Close

?rst1.Close

? ??

? ??

End If

Any assistance would be greatly appreciated

Regards

Toukey