Requesting Parameter Value on Update Query

[Guest]

I have an audit log that records the values of a control before and after it
is changed. The first part of the process runs on an OnDirty event to record
the value before the change and the second part runs on an AfterUpdate event
(code below).

The OnDirty part works fine, but when I try to execute the code below, I am
prompted for a parameter value. There is identical code that runs on 15
other controls with no problem. The other updates are on dates and numbers.

One interesting thing to note is that if I enter a numeric charater instead
of a text character, the code runs fine.

Private Sub txtCompanyScreened_AfterUpdate()

Dim NewVal As String

NewVal = Me.txtCompanyScreened

strSQL = "UPDATE tAuditLogTxt SET txtNewVal=" & NewVal & _
" WHERE anID=(SELECT Max(anID) FROM tAuditLogTxt WHERE txtNTName='" &
fOSUserName() & "')"

DoCmd.RunSQL strSQL

End Sub

I am running 2003 on XP.

Thoughts?

PJ

 

[Baz]

You haven't quoted NewVal properly, thus:

strSQL = "UPDATE tAuditLogTxt SET txtNewVal=""" & NewVal & _
""" WHERE anID=(SELECT Max(anID) FROM tAuditLogTxt WHERE txtNTName='" &
fOSUserName() & "')"

 

[Guest]

That did it! Thanks!

You haven't quoted NewVal properly, thus:

strSQL = "UPDATE tAuditLogTxt SET txtNewVal=""" & NewVal & _
""" WHERE anID=(SELECT Max(anID) FROM tAuditLogTxt WHERE txtNTName='" &
fOSUserName() & "')"