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

You haven't quoted NewVal properly, thus:

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

"PJFry" <(E-Mail Removed)> wrote in message
news:C3291AF4-703B-4D0D-915E-(E-Mail Removed)...
> 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
>

That did it! Thanks!

"Baz" wrote:

> You haven't quoted NewVal properly, thus:
>
> strSQL = "UPDATE tAuditLogTxt SET txtNewVal=""" & NewVal & _
> """ WHERE anID=(SELECT Max(anID) FROM tAuditLogTxt WHERE txtNTName='" &
> fOSUserName() & "')"
>
> "PJFry" <(E-Mail Removed)> wrote in message
> news:C3291AF4-703B-4D0D-915E-(E-Mail Removed)...
> > 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
> >
>
>
>