Update Query: Enter Parameter Value

  • Thread starter Thread starter CC
  • Start date Start date
C

CC

I'm trying to set a field to a value that is stored in a variable using an
update query in VBA (Access 2003). The query prompts the user to enter
parameter value, and the value of the variable displays in the pop up above
the input text box of the pop up.

The SQL is:
DoCmd.RunSQL "UPDATE temptbl_ImportedPlateOrders SET
temptbl_ImportedPlateOrders.GenSetName =" & strGenSetName

strGenSetName is the varialbe and displays the correct value in when I mouse
over it: strGenSetName = "G9999V00.txt"

Can someone identify what I'm doing wrong. Thanks in advance. You always
provide great advice.
 
CC said:
I'm trying to set a field to a value that is stored in a variable
using an update query in VBA (Access 2003). The query prompts the
user to enter parameter value, and the value of the variable
displays in the pop up above the input text box of the pop up.

The SQL is:
DoCmd.RunSQL "UPDATE temptbl_ImportedPlateOrders SET
temptbl_ImportedPlateOrders.GenSetName =" & strGenSetName

strGenSetName is the varialbe and displays the correct value in when
I mouse over it: strGenSetName = "G9999V00.txt"

Can someone identify what I'm doing wrong. Thanks in advance. You
always provide great advice.
Click to expand...

It appears the GenSetName is a text field, then you need text
delimiters on the value

....GenSetName ='" & strGenSetName & "'"

or

....GenSetName =""" & strGenSetName & """"
 
CC said:
I'm trying to set a field to a value that is stored in a variable
using an update query in VBA (Access 2003). The query prompts the
user to enter parameter value, and the value of the variable displays
in the pop up above the input text box of the pop up.

The SQL is:
DoCmd.RunSQL "UPDATE temptbl_ImportedPlateOrders SET
temptbl_ImportedPlateOrders.GenSetName =" & strGenSetName

strGenSetName is the varialbe and displays the correct value in when
I mouse over it: strGenSetName = "G9999V00.txt"

Can someone identify what I'm doing wrong. Thanks in advance. You
always provide great advice.
Click to expand...

As an alternative to Roy's advice, you can avoid the delimiter issue
entirely by using a querydef's parameters collection. Like this:

dim qdf as querydef 'you might have to add a reference to DAO
dim sSQL as string
sSQL="UPDATE temptbl_ImportedPlateOrders SET " & _
"GenSetName =[p1]"
set qdf=currentdb.createquerydef("",sSQL)
qdf(0) = strGenSetName
qdf.execute
 
Back
Top