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

Sabtu, 22 Juni 2019

Re: [MS_AccessPros] Update table using a recordset

hi Tourkey,

please be sure to keep history when you post. Here is what you wrote:
~~~

Thank you for your responses.

Initially I had a DLookup code that was working fine based on the criteria e.g.:

sno = DLookup("[SeqNo]", "tblAllPols", "[tblAllPols].[Issuedate] = # " & vard & " #" & vno & add2 & add3 & add4 & add8)

uquery = "update tblAllPols set InvDate = # " & varx & " # where [tblAllPols].[SeqNo] = " & sno

uquery1 = "update tblAllPols set InvNo = " & x & " where [tblAllPols].[SeqNo] = " & sno

uquery2 = "update tblAllPols set Currenc = '" & xxx & "' where [tblAllPols].[SeqNo] = " & sno

However the above code only updates one row in the table.? I want to be able to update more that one row so I thought a loop would be better.

I am not getting any errors in the code I posted initially but the problem is that it's not updating the table.

What is the best way to handle what I want to do?

Regards

~~~

you're welcome

re: "I want to be able to update more that one row so I thought a loop would be better."

What is the logic you are using to know what to update? Could that same logic not be put into the WHERE clause for SQL too?

is SeqNo not on your form? why are you using a DLookup to get it?? and what is the grouping above the sequence number?

Where do the other parts like add2 and add3 come from?

Is this a main form with a subform(s)? What is the RecordSource? It is hard to help without knowing more about your structure and intention.

Could you explain a little bit about what your application does, and what this form is collecting?

thanks

have an awesome day,
crystal

On 6/21/2019 12:59 PM, crystal 8 strive4peace [MS_Access_Professionals] wrote:

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