Update Query too few parameters (1 Viewer)

[Mackbear]

Hi, Good day! It's me again, I have come across another problem I have been staring at for the past few days and I can't seem to figure out the problem. This is an update query. I have attached an image to with snapshots.

I was trying to update a certain field in a table where the entryid is on the selected list. It is having an error too few parameters. But I used the same parameter as the other query (the insert query one) and it works. I don't know why it is not working on the update query.

 

[June7]

You should post code and compiled SQL statement, not an image on a Word doc that is hard to read. Not to mention making us go through effort to open zip file just to read text that could have been included in post.

Suggest you try concatenating reference to form control instead of embedding between quote marks.

 

[theDBguy]

Hi. Just in case it helps, take a look at this as well. Cheers!

 

[Mackbear]

You should post code and compiled SQL statement, not an image on a Word doc that is hard to read. Not to mention making us go through effort to open zip file just to read text that could have been included in post.

Suggest you try concatenating reference to form control instead of embedding between quote marks.

Hello, I apologize here is the code:

Dim sqlhistnote As String
sqlhistnote = "UPDATE tbl_pbhistory SET tbl_pbhistory.Notes = [Forms]![frm_pbtreasreachout]![nots] WHERE tbl_pbhistory.[entryid_hist] IN (" & Replace(SelectedList, "|", ",") & ")"
Debug.Print sqlhistnote
CurrentDb.Execute sqlhistnote

I just posted the image in case you may want to see the immediate window as I have seen something weird in there...

It works fine in the insert query which is this:
Dim sqlhistory As String
sqlhistory = "INSERT INTO tbl_pbhistory (entryid_hist, AP, APFullName, ResolutionStatus, PrimaryRC, UserRC, DateWorked, DateTimeWorked, Week, Mmonth ) SELECT tbl_PBreso.entryid_pbreso, tbl_PBreso.AP, tbl_PBreso.APFullName, tbl_PBreso.ResolutionStatus, tbl_PBreso.PrimaryRC, tbl_PBreso.UserRC, tbl_PBreso.DateWorked, tbl_PBreso.DateTimeWorked, tbl_PBreso.Week, tbl_PBreso.Mmonth FROM tbl_PBreso WHERE tbl_PBreso.[entryid_pbreso] IN (" & Replace(SelectedList, "|", ",") & ")"
Debug.Print sqlhistory
CurrentDb.Execute sqlhistory

 

[June7]

The INSERT does not reference form control.

BTW, is [nots] correct spelling or should that be [note]?

Perhaps just need to correct spelling and embedded reference will work.

 

[Mackbear]

The INSERT does not reference form control.

BTW, is [nots] correct spelling or should that be [note]?

Perhaps just need to correct spelling and embedded reference will work.

Yes that is the correct spelling.

I got another update query as well that references form control and it works fine.

Dim sqltreas As String
sqltreas = "UPDATE qry_pbworkflow SET qry_pbworkflow.[FollowUpDate] = #" & ffupdate & "#, qry_pbworkflow.[ResolutionStatus]='In Process' WHERE qry_pbworkflow.[entryid_pbreso] IN (" & Replace(SelectedList, "|", ",") & ")"
Debug.Print sqltreas
CurrentDb.Execute sqltreas

 

[June7]

ffupdate is control name? You are concatenating. Query with issue does not concatenate reference to [nots], it is embedded between quotes.

 

[Mackbear]

ffupdate is control name? You are concatenating. Query with issue does not concatenate reference to [nots], it is embedded between quotes.

Yes it is a control name, I apologize I am not sure what the concatenate thing is, can you enlighten me?

 

[June7]

Concatenation: the action of linking things together in a series.

In code, concatenation is linking literal text with a dynamic input. That's what & character accomplishes.

= #" & ffupdate & "#,

That is concatenating.

SET tbl_pbhistory.Notes = [Forms]![frm_pbtreasreachout]![nots] WHERE

That is not concatenating. The form control reference is literal string of characters between quote marks. The value of [nots] is not passed into query structure.

sqlhistnote = "UPDATE tbl_pbhistory SET Notes = '" & [Forms]![frm_pbtreasreachout]![nots] & "' WHERE [entryid_hist] IN (" & Replace(SelectedList, "|", ",") & ")"

 

[theDBguy]

Yes it is a control name, I apologize I am not sure what the concatenate thing is, can you enlighten me?

Hi. If you're using VBA anyway, have you tried the code in the link I posted earlier?

 

[Mackbear]

Concatenation: the action of linking things together in a series.

In code, concatenation is linking literal text with a dynamic input. That's what & character accomplishes.

= #" & ffupdate & "#,

That is concatenating.

SET tbl_pbhistory.Notes = [Forms]![frm_pbtreasreachout]![nots] WHERE

That is not concatenating. The form control reference is literal string of characters between quote marks. The value of [nots] is not passed into query structure.

sqlhistnote = "UPDATE tbl_pbhistory SET Notes = '" & [Forms]![frm_pbtreasreachout]![nots] & "' WHERE [entryid_hist] IN (" & Replace(SelectedList, "|", ",") & ")"

Good day! Thanks for the explanation, I was thinking concatenate is the joining of two strings, I thought that what I am doing in this query is getting the values from the text box so I never thought of it as concatenating.

I actually tried that before and what I did is:
sqlhistnote = "UPDATE tbl_pbhistory SET Notes = '" & me.[nots] & "' WHERE [entryid_hist] IN (" & Replace(SelectedList, "|", ",") & ")"

And I am getting Data Type mismatch on query expression. I checked the field and it is set as text. So I tried other ways. I tried this "[Forms]![frm_pbtreasreachout]![nots] " in replacement of '" & me.[nots] & "'.

When I tried the one you provided:
sqlhistnote = "UPDATE tbl_pbhistory SET Notes = '" & [Forms]![frm_pbtreasreachout]![nots] & "' WHERE [entryid_hist] IN (" & Replace(SelectedList, "|", ",") & ")"

"The Microsoft Access database engine cannot find the input table or query "False". Make sure it exist and that its name is spelled correctly."

This is so weird. Not sure where this is coming from, I'm not sure if there is something I am not seeing. I cannot find that "False" anywhere on my code.

:banghead::banghead::banghead:

 

[Mackbear]

Hi. If you're using VBA anyway, have you tried the code in the link I posted earlier?

Hello, I checked that but have not tried it yet, it looks a little complicated for the task that I am trying to do, I apologize as I am unable to understand complex codes, it will take me some time to comprehend this... :banghead::banghead::banghead: but thanks for this, might be handy in the future if I have the time to study and understand it, right now not much time.

 

[arnelgp]

debug and step through the code.

debug.print sqlhistnote

 

[Mackbear]

Hi all, I have posted the sample DB I hope you can look into it, this is driving me nuts..... ='c

Click on any number link > Select entry by clicking > Click "Payment Inquiry" Button.

Upon clicking send:
Generates Email draft
Saves data to tbl_pbhistory

Need to update the field Note for the selected entryid_hist on the tbl_pbhistory using the value on the nots field - Does not work ='c

 

[Mackbear]

debug and step through the code.

debug.print sqlhistnote

Hello, it is printing this error: "The Microsoft Access database engine cannot find the input table or query "False". Make sure it exist and that its name is spelled correctly."

 

[arnelgp]

I add this, to make sure the textbox has value:

Private Sub save_Click()

Dim sqlpbtemp As String
sqlpbtemp = "INSERT INTO tbl_PBinfotemp ( VendorAccountNo, PriorBalance, CurrentCharges, InvoiceDate, InvoiceID, entryid_pbinfo, PMCName, SiteName, VendorName ) SELECT tbl_PBinfo.VendorAccountNo, tbl_PBinfo.PriorBalance, tbl_PBinfo.CurrentCharges, tbl_PBinfo.InvoiceDate, tbl_PBinfo.InvoiceID, tbl_PBinfo.entryid_pbinfo, tbl_PBinfo.PMCName, tbl_PBinfo.SiteName, tbl_PBinfo.VendorName FROM tbl_PBinfo WHERE tbl_PBinfo.[entryid_pbinfo] IN (" & Replace(SelectedList, "|", ",") & ")"
Debug.Print sqlpbtemp
CurrentDb.Execute sqlpbtemp

'[COLOR="Navy"]* arnelgp
'* make sure there is value on this textbox
If Trim(Me.nots & "") = "" Then _
    Call rtype_AfterUpdate
'* end arnelgp
[/COLOR]…
…

 

[Mackbear]

I add this, to make sure the textbox has value:

Private Sub save_Click()

Dim sqlpbtemp As String
sqlpbtemp = "INSERT INTO tbl_PBinfotemp ( VendorAccountNo, PriorBalance, CurrentCharges, InvoiceDate, InvoiceID, entryid_pbinfo, PMCName, SiteName, VendorName ) SELECT tbl_PBinfo.VendorAccountNo, tbl_PBinfo.PriorBalance, tbl_PBinfo.CurrentCharges, tbl_PBinfo.InvoiceDate, tbl_PBinfo.InvoiceID, tbl_PBinfo.entryid_pbinfo, tbl_PBinfo.PMCName, tbl_PBinfo.SiteName, tbl_PBinfo.VendorName FROM tbl_PBinfo WHERE tbl_PBinfo.[entryid_pbinfo] IN (" & Replace(SelectedList, "|", ",") & ")"
Debug.Print sqlpbtemp
CurrentDb.Execute sqlpbtemp

'[COLOR="Navy"]* arnelgp
'* make sure there is value on this textbox
If Trim(Me.nots & "") = "" Then _
    Call rtype_AfterUpdate
'* end arnelgp
[/COLOR]…
…

Still having the same error:

"The Microsoft Access database engine cannot find the input table or query "False". Make sure it exist and that its name is spelled correctly"

 

[theDBguy]

Hello, I checked that but have not tried it yet, it looks a little complicated for the task that I am trying to do, I apologize as I am unable to understand complex codes, it will take me some time to comprehend this... :banghead::banghead::banghead: but thanks for this, might be handy in the future if I have the time to study and understand it, right now not much time.

Hi. No worries. I'm sure you'll get there eventually. Good luck!

 

[June7]

Should use line continuation for more readable code. It's not really necessary to always prefix each fieldname with table or query name.

Your actual code has:

sqlhistnote = sqlhistnote = "UPDATE …"

Remove one of those sqlhistnote =

Step debugging exposed that error. Review link at bottom of my post. If I had simply gone straight to that SQL string in code, I would have avoided step debugging.

 

[Mackbear]

Should use line continuation for more readable code. It's not really necessary to always prefix each fieldname with table or query name.

Your actual code has:

sqlhistnote = sqlhistnote = "UPDATE …"

Remove one of those sqlhistnote =

Step debugging exposed that error. Review link at bottom of my post. If I had simply gone straight to that SQL string in code, I would have avoided step debugging.

Awww thanks for this, however now I am back to the initial error I was having which is the data type mismatch in criteria expression. ='c

I checked and the Note field is set a text. I am not sure what else could be the problem...