VBA Update query for multi fields

Question

I have been trying to fix this code, but have no idea why I keep having too few parameters :1 error

strSQL = "UPDATE tblProduct "
strSQL = strSQL & "SET [Verified_By] = " & Me.txtCurrentUser & "" & " , [Verified_Date] = #" & Me.txtAuto_Date & "#" & ", [Status] = ""Verified"""
strSQL = strSQL & " WHERE [Status] = ""Not Verified""" & " AND [Verify] = -1"

I am still having troubles with the syntax for attaching variables to queries in VBA. The query works in my UPDATE statement in Access.

This is what I use in Access query:

UPDATE tblProduct SET Verified_By = forms!frmVerificationProduct!txtcurrentuser, Verified_date = forms!frmVerificationProduct!txtAuto_date, Status = "Verified"
WHERE verify = -1 AND Status = "Not Verified";

HansUp · Accepted Answer · 2016-01-29 22:19:13Z

If the tblProduct.Verified_By field is text datatype, the problem is that the UPDATE is supplying an unquoted text value. And when Access sees the unquoted text value, it assumes that must be a parameter for which you have not supplied a value.

In that case, you could revise the code to include the needed quotes or switch to a parameter query and not be bothered with quotes ...

Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim strSQL As String
strSQL = "UPDATE tblProduct" & vbCrLf & _
    "SET [Verified_By] = [pCurrentUser], [Verified_Date] = [pAuto_Date], [Status] = 'Verified'" & vbCrLf & _
    "WHERE [Status] = 'Not Verified' AND [Verify] = -1"
Debug.Print strSQL ' <- view in Immediate window; Ctrl+g will take you there
Set db = CurrentDb
Set qdf = db.CreateQueryDef(vbNullString, strSQL)
qdf.Parameters("pCurrentUser").Value = Me!txtCurrentUser.Value
qdf.Parameters("pAuto_Date").Value = Me!txtAuto_Date.Value
qdf.Execute dbFailOnError

Gustav · Accepted Answer · 2016-01-29 22:19:47Z

1

This should do:

strSQL = "UPDATE tblProduct "
strSQL = strSQL & "SET [Verified_By] = " & Me.txtCurrentUser & ", [Verified_Date] = #" & Format(Me.txtAuto_Date, "yyyy\/mm\/dd") & "#, [Status] = 'Verified' "
strSQL = strSQL & "WHERE [Status] = 'Not Verified' AND [Verify] = -1"

answered Jan 29, 2016 at 22:19

Gustav

56.3k7 gold badges33 silver badges65 bronze badges

Collectives™ on Stack Overflow

VBA Update query for multi fields

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related