Selasa, 25 Juni 2019

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 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
    Next

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

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










__._,_.___

Posted by: Duane Hookom <duanehookom@hotmail.com>
Reply via web post Reply to sender Reply to group Start a New Topic Messages in this topic (15)

SPONSORED LINKS
.

__,_._,___

Tidak ada komentar:

Posting Komentar