Error on writing to Recordset

12 Sep 2007 10:02 PM

Don Barton

I am trying to write a record to a table via VBA. I am doing this to
document user access. When a user logs in correctly, global variables
are assigned with different demographic data.    These are then made
into global variables and are defined in my General/Declarations
section of a module.

Below is a function I am trying to use to write the user infomation to
a table.

-------------------------------------------------
Public Function WriteAuditTrail()
    On Error GoTo Err_WATError

    Dim strSQL As String
    Dim TableName As String
    Dim db As DAO.Database
    Dim rst As DAO.Recordset

    Set db = CurrentDb()

    strSQL = "SELECT * FROM tblUserActivity WHERE [PWUserName] = " &
[gActiveUser] & ";"

    Set rst = db.OpenRecordset(strSQL, dbOpenDynaset)

    rst.AddNew
    rst!PWNames = gActiveUser
    rst!PWDateTime = Now
    rst!pwActivity = gUserActivity
    rst!pwSecStatus = gSecStatus
    rst.Update
    rst.Close
    etc......
-----------------------------------
My error routine is invoked when it hits "Set
rst=db.OpenRecordset(strSQL, dbOpenDynaset)" line. I either have a
syntax error for my gActiveUser variable, or I am missing some
properties of the rst object?

I've also noticed when I put the mouse over a rst!fieldname I see
"object variable or with block variable not set", but the global
variables have data.

Thanks,

Don

13 Sep 2007 4:17 AM

Tom van Stiphout

On Wed, 12 Sep 2007 22:02:13 -0000, Don Barton <DonaldB***@gmail.com>
wrote:

That's because you're doing a:
....where PWUserName = Tom;
while you should be doing:
....where PWUserName = 'Tom';

-Tom.

Show quote

>I am trying to write a record to a table via VBA. I am doing this to
>document user access. When a user logs in correctly, global variables
>are assigned with different demographic data.    These are then made
>into global variables and are defined in my General/Declarations
>section of a module.
>
>Below is a function I am trying to use to write the user infomation to
>a table.
>
>-------------------------------------------------
>Public Function WriteAuditTrail()
>    On Error GoTo Err_WATError
>
>    Dim strSQL As String
>    Dim TableName As String
>    Dim db As DAO.Database
>    Dim rst As DAO.Recordset
>
>    Set db = CurrentDb()
>
>    strSQL = "SELECT * FROM tblUserActivity WHERE [PWUserName] = " &
>[gActiveUser] & ";"
>
>    Set rst = db.OpenRecordset(strSQL, dbOpenDynaset)
>
>    rst.AddNew
>    rst!PWNames = gActiveUser
>    rst!PWDateTime = Now
>    rst!pwActivity = gUserActivity
>    rst!pwSecStatus = gSecStatus
>    rst.Update
>    rst.Close
>    etc......
>-----------------------------------
>My error routine is invoked when it hits "Set
>rst=db.OpenRecordset(strSQL, dbOpenDynaset)" line. I either have a
>syntax error for my gActiveUser variable, or I am missing some
>properties of the rst object?
>
>I've also noticed when I put the mouse over a rst!fieldname I see
>"object variable or with block variable not set", but the global
>variables have data.
>
>Thanks,
>
>Don