Too few parameters on vba sql

[tagteam]

I am trying to run query in vba but keep getting this error:


Run time error 3061
Too few paramters. Expecte 1

Below is the code. I have used similar in other areas and it works, not sure why this doesnt. When i do a debug.print it shows the correct value for the variable myid

Dim myid As String
myid = Me.BillingInvoiceID
Dim strSQL As String




strSQL = "Update tmpRRInvoices Set tmpRRInvoices.Archived = Yes WHERE (((tmpRRInvoices.BillingInvoiceID)= " & myid & "))"


CurrentDb.Execute strSQL, dbFailOnError

[Welshgasman]

Yes probably needs to be 'Yes'?

[tagteam]

I already tried that. If I use 'Yes' then i get the same message. If i use "Yes" then I get Compile error: Syntax Error

[Welshgasman]

Show the result of a debug.print of strsql
You do not need any of those brackets either?

[Micron]

If you are absolutely certain that there is no spelling error in table/field names then debug.print sql, step through, stop on the Execute line. If it looks good, copy/paste sql to new query in sql view and attempt to switch to datasheet view. If it presents a datasheet without balking (likely will be blank but that's OK) then the syntax and names are good. If not, it should highlight the offending part.

[tagteam]

I commented out some of the code after that and now it does not give an error but still does not update the field.

Here is the full code you can see the part i commented out at the bottom. I may have been getting the too few parameters from the second sql string but when the vba window opened it was highlighting the first string.
Dim FAvar As String
FAvar = Me.BillingInvoiceID
MsgBox "the Billing Invoice ID is " + FAvar
'DoCmd.OpenReport "r_RRIndividualCoverSheet", acViewReport, , "[BillingInvoiceID] = '" & Me.BillingInvoiceID & "'"


Dim myid As String
myid = Me.BillingInvoiceID
Dim strSQL As String

strSQL = "Update tmpRRInvoices Set tmpRRInvoices.Archived = Yes WHERE (((tmpRRInvoices.BillingInvoiceID)= " & myid & "))"

'CurrentDb.Execute strSQL, dbFailOnError

'Dim ArchiveUpdateID As String
'ArchiveUpdateID = Me.BillingInvoiceID
'Dim BatchNumber As String
'BatchNumber = Me.TrinityAPBatchTxt

'Dim strSQL2 As String

'strSQL2 = "Update tbl_ImportedRepairs_Archive" _
' & "Set tbl_ImportedRepairs_Archive.TrinityAPBatch = " & BatchNumber & "" _
' & "WHERE (((tmpRRInvoice.BillingInvoiceID)= " & ArchiveUpdateID & "))"

'CurrentDb.Execute strSQL2, dbFailOnError

[CJ_London]

Is the field Archived a yes/no field? if so, instead of Yes, try True or -1

[CJ_London]

there is your problem


from your report criteria BillingInvoiceID is a string

So you need to use the single quotes per your report criteria

[tagteam]

ok, so first part is working with the tip from Ajax. I needed (((tmpRRInvoices.BillingInvoiceID)= '" & myid & "'))" instead of (((tmpRRInvoices.BillingInvoiceID)= " & myid & "))"


Now that that works it is giving me an error on the second part.

This line:

strSQL2 = "Update tbl_ImportedRepairs_Archive Set tbl_ImportedRepairs_Archive.TrinityAPBatch = '" & BatchNumber & "' WHERE (((tmpRRInvoice.BillingInvoiceID)= '" & ArchiveUpdateID & "'))"

is now giving me the too few paramters error. That is weird because it is just like the previous one and it does give me the correct variable values.

I have tried
& BatchNumber &
" & BatchNumber & "
'" & BatchNumber & "'

I have also tried changing the BatchNumber from a string to variable

[Gicu]

Did you un-comment these two lines?

'Dim BatchNumber As String
'BatchNumber = Me.TrinityAPBatchTxt

Cheers,

[CJ_London]

there is no need to assign values to another variable before including in your sql, and certainly not three times as you have done for BillingInvoiceID. It just makes your code more confusing, difficult to debug and difficult to follow.

strSQL2 = "Update tbl_ImportedRepairs_Archive Set tbl_ImportedRepairs_Archive.TrinityAPBatch = '" & Me.TrinityAPBatchTxt & "' WHERE (((tmpRRInvoice.BillingInvoiceID)= '" & Me.BillingInvoiceID & "'))"

but if you are going to continue with that method I suggest you use names which are relevant to the dataype - 'batchnumber' implies a numeric value, batchStr would be more relevant

And include Option Explicit at the top of every module - then when you compile the code, it would have told you that BatchNumber was undeclared (as is ArchiveUpdateID). You can set it for new modules by going to Tools>Options>Editor tab form the VBA ribbon and ticking the Require Variable Declaration option

[tagteam]

Ok, I have cleaned up the code to this

Dim myid As String
myid = Me.BillingInvoiceID
Dim strSQL As String
strSQL = "Update tmpRRInvoices Set tmpRRInvoices.Archived = Yes WHERE (((tmpRRInvoices.BillingInvoiceID)= '" & myid & "'))"


CurrentDb.Execute strSQL, dbFailOnError


Dim BatchNumber As Integer
BatchNumber = Me.TrinityAPBatchTxt




strSQL = "Update tbl_ImportedRepairs_Archive Set tbl_ImportedRepairs_Archive.TrinityAPBatch = " & BatchNumber & " WHERE (((tbl_ImporteRepairs_Archive.BillingInvoiceID)= '" & myid & "'))"

CurrentDb.Execute strSQL, dbFailOnError

and i got it working. In cleaning up the code I noticed that I had the wrong table in the where clause.

Thanks for the help in getting this sorted.

[CJ_London]

unless you are using BatchNumber elsewhere in your sub or function, you don't need

Dim BatchNumber As Integer
BatchNumber = Me.TrinityAPBatchTxt

glad you got it working