Dynamically updating table based on current Form

[cohortq]

Hey Guys!!
I am trying to update an access table using the RunSql function in access. I want to be able to update a single column each time with the ID# on the current form. Here is my code:
Dim NEWID As Integer
NEWID = Me.UniqueKey.Value (also tried without .value)
DoCmd.RunSQL "Update ID set Key = NEWID where UID = 1"
DoCmd.RunMacro "Export"

This is to printout the current form to a file, but the thing is that when I run it, Access wants to knowthe value for NEWID, and I thought I already set it. I think it's because Variables aren't applicable in an SQL statement, but I was wondering if anyone has suggestions on how I can do this. THANX GUYS!!!!

 

[marcNZ]

I realised not long ago that the sql statement cannot have the variable name in it within the quotes.
Your statement might work rewritten like that:

"Update ID set Key = " & NEWID & " where UID = 1"

If you had a string to pass instead of a number,

"Update ID set Key = NEWID where myString = '" & _
strData & "'"

As you can see, the string variable needs the single quote, which are part of the main sql statement, one before closing the sql, then the string variable, finally the ending single quote within the double quotes as it is part of the main sql statement.

Hope this helps.

 

[cohortq]

THANX A LOT MARKY MARK!!!