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

Toukey,

You really need to understand how to troubleshoot. I think many of us have suggested many times to add a debug.print line or set a break point so you can step through your code. Without knowing how to do this, you will continually need help.

Please add the debug statement like:

strSQL = "UPDATE tblAllPols SET InvNo = " & x & _

", InvDate = #" & varx & "#, Currenc = " & _

xxx & " WHERE IssueDate = # " & vard & " #" & vno & add2 & add3 & add4 & add8

Debug.Print strSQL

Then look in the debug window (Press Ctrl+G) to see what is presented. If you can't find the error, then reply back with the results in the window.

Regards,

Duane

From: MS_Access_Professionals@yahoogroups.com <MS_Access_Professionals@yahoogroups.com> on behalf of toukey1 <no_reply@yahoogroups.com>
Sent: Tuesday, June 25, 2019 10:31 AM
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] Re: Update table using a recordset

Hi Duane,

I'm getting a "Run-time error '3061': Too few parameters. Expected 1" error after I amended your code with my variables. Was trying to figure out the cause. Seems to work without Currenc.

Dim x As Long

Dim xxx As String

Dim varx As Date

Dim vard As Date

Dim vno As Variant

Dim add2 As String

Dim add3 As String

Dim add4 As String

Dim add8 As String

x = Me!InvoiceNo

xx = Me!ClientNoCombo.Column(0)

xxx = Me!Currency

varx = Me!Date

vard = Me!IssueDate

vary = Me!PolicyNo

varz = Me!Premium

varp = Me!Tax

add2 = " And [tblAllPols].[PolicyNo] = '" & vary & " ' "

add3 = " And [tblAllPols].[Premium] = " & varz

add4 = " And [tblAllPols].[ClientNo] = " & xx

add8 = " And [tblAllPols].[Tax] = " & varp

vno = " And [tblAllPols].[InvNo] = 0"

strSQL = "UPDATE tblAllPols SET InvNo = " & x & _

", InvDate = #" & varx & "#, Currenc = " & _

xxx & " WHERE IssueDate = # " & vard & " #" & vno & add2 & add3 & add4 & add8

Even tried it without using the variables e.g. Me!InvoiceNo etc. but getting the same error.

Regards

Toukey

---In MS_Access_Professionals@yahoogroups.com, <duanehookom@...> wrote :

Hi Toukey,

Any feedback if this worked?

Regards,

Duane

From: MS_Access_Professionals@yahoogroups.com <MS_Access_Professionals@yahoogroups.com> on behalf of toukey1 <no_reply@yahoogroups.com>
Sent: Monday, June 24, 2019 9:51 AM
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] Re: Update table using a recordset

Thanks for your response Duane.

Your assumption is correct. I will try the code you provided and let you know how things go.

Thanks again.

Toukey.

---In MS_Access_Professionals@yahoogroups.com, <duanehookom@...> wrote :

Toukey,

I assume you want to update the three fields in the tblAllPols with values from the form controls where the IssueDate on the form is equal to the IssueDate in tblAllPols.

Dim dbs as DAO.Database

Dim strSQL As String

Set dbs = CurrentDb

' assuming InvNum and Currency are numeric and InvDate is a Date

strSQL = "UPDATE tblAllPols SET InvNo = " & Me.InvNum & _

", InvDate = #" & Me.[Date] & "#, Curenc = " & _

Me.[Currency] & " WHERE IssueDate = #" & Me.IssueDate & "#"

dbs.Execute strSQL, dbFailOnError

Set dbs = Nothing

Duane

From: MS_Access_Professionals@yahoogroups.com <MS_Access_Professionals@yahoogroups.com> on behalf of toukey1 <no_reply@yahoogroups.com>
Sent: Sunday, June 23, 2019 8:56 AM
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] Re: Update table using a recordset

Hi Crystal,

Below is a sample of the tblAllPols table I'm trying to update. So I would like the code on the "Save" button on the form to update the InvNo, InvDate and Currenc fields of the tblAllPols table from the info entered on the form. You will notice that the SeqNo is unique.

SeqNo	ClientNo	FirstName	LastName	Premium	InvNo	InvDate	Currenc	Issuedate
109830	0002571	M	X	$4,712.50	0			25-Jun-2019
109831	0002571	M	X	$4,712.50	0			25-Jun-2019
109832	0002571	M	X	$4,712.50	0			25-Jun-2019
109833	0002571	M	X	$4,712.50	0			25-Jun-2019

This is the code I was attempting to use....

Set dbs = CurrentDb

strsql1 = "SELECT tblAllPols.SeqNo FROM tblAllPols WHERE [tblAllPols].[Issuedate] = Me!IssueDate

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

For inti = 0 To (noorecords - 1)

With rst

.Edit

!InvNo = Me!InvoiceNo

!InvDate = Me!Date

!Currenc = Me!Currency

.Update

End With

rst.Close

rst1.Close

End If

Hopefully the above examples will make things a little clearer.

Regards

Toukey

---In MS_Access_Professionals@yahoogroups.com, <strive4peace2008@...> wrote :

hi Toukey,

please post the whole procedure you are using, with the correction for DoMenuItem, and example information for each of your controls

and please answer:
if SeqNo is supposed to be unique, then why are multiple values allowed?

thank you

~crystal

On 6/22/2019 9:25 PM, toukey1 wrote:

Hi Crystal

The fields are actually called InvDate and Currenc so I should be fine.

Any idea why my recordset is not updating the table or how best I can accomplish the task at hand?

Regards

Toukey

---In MS_Access_Professionals@yahoogroups.com, <strive4peace> wrote :

hi Toukey,

just noticed your fieldnames. Date and Currency are reserved words and shouldn't be used as names.
http://allenbrowne.com/AppIssueBadWord.html

if SeqNo is supposed to be unique, then why is it not?

have an awesome day,
crystal

On 6/22/2019 9:07 AM, toukey1 wrote:

Hi Crystal,

Sorry about the history.?? Let me see how best I can explain what I am trying to accomplish.

I have the original "Dlookup" code on the 'Save' button on an Invoice form.?? The code did what it was supposed to do which was to get the SeqNo (unique key) in the table tblAllPols based on the criteria i.e. vard, vno, add2 etc.?? Once the row has been found, the row would be updated with the InvNo, InvDate and currenc from the current invoice form.

Originally, there would be only one (1) row to update as the criteria was unique but now, due to other changes in the application, more than one (1) row can have the same criteria and I need to update all the rows found with the same criteria with the??InvNo, InvDate and currenc from the current form.?? That's when I thought I would create a recordset which would find the SeqNo for all rows with the criteria and update it.?? Using the recordset I created I was able to identify the SeqNos but the rows in the table are not being updated.

How best can I accomplish this?

Regards Toukey

---In MS_Access_Professionals@yahoogroups.com, <strive4peace> wrote :

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