Writing values to a table via VB

[PJFry]

I want to create an audit log for a process database at my office.
Basically, everytime a change is made to certain parts of the form I want to
log the change and who did it. For example, when someone checks a box that
indicates an issue as been resolved, the persons network ID, the change made
and date and time the change was made will be recorded. Here is what I have
so far:

***********************************************************
Private Sub Resolution_Click()
Me.dtmDateResolved = Date
Me.dtmTimeResolved = Time

Dim strSQL As String
Dim UserID As String

UserID = fOSUserName()

strSQL = "INSERT INTO tChangeLog(txtUserID) VALUES ( " & UserID & ");"

DoCmd.RunSQL strSQL

End Sub
***********************************************************
where UserID = fOSUserName() is
**********************************************************
Private Declare Function apiGetUserName Lib "advapi32.dll" Alias _
"GetUserNameA" (ByVal lpBuffer As String, nSize As Long) As Long

Function fOSUserName() As String
' Returns the network login name
Dim lngLen As Long, lngX As Long
Dim strUserName As String
strUserName = String$(254, 0)
lngLen = 255
lngX = apiGetUserName(strUserName, lngLen)
If (lngX > 0) Then
fOSUserName = Left$(strUserName, lngLen - 1)
Else
fOSUserName = vbNullString
End If
End Function
************************************************************

As you can see, I want to use an INSERT INTO statement to write a variable
to the table. I can do it if I define the value in the statement:

strSQL = "INSERT INTO tChangeLog(txtUserID) VALUES ('pjfry');"
but I don't know how to use variables in the statement.

Thoughts?
Thanks in advance
PJ

 

[Douglas J. Steele]

Since txtUserID is a text field, you need quotes around the value you're
trying to save:

strSQL = "INSERT INTO tChangeLog(txtUserID) VALUES ('" & UserID & "')"

Exagerated for clarity, that's

strSQL = "INSERT INTO tChangeLog(txtUserID) VALUES ( ' " & UserID & " ' )"

Note: the only reason this will work is because UserID cannot have an
apostrophe in it. If you were dealing with a name that included an
apostrophe, such as O'Reilly, you'd need to use one of the following:


strSQL = "INSERT INTO tChangeLog(txtUserID) VALUES ('" & Replace(UserID,"'",
"''" & "')"
strSQL = "INSERT INTO tChangeLog(txtUserID) VALUES (""" & UserID & """)"
strSQL = "INSERT INTO tChangeLog(txtUserID) VALUES (" & Chr$(34) & UserID &
Chr$(34) & ")"

(In case you're wondering, there's no real need to include the semi-colon at
the end of the SQL statement)

 

[PJFry]

Perfect. I see what the issue was.

Thanks!
PJ

Since txtUserID is a text field, you need quotes around the value you're
trying to save:

strSQL = "INSERT INTO tChangeLog(txtUserID) VALUES ('" & UserID & "')"

Exagerated for clarity, that's

strSQL = "INSERT INTO tChangeLog(txtUserID) VALUES ( ' " & UserID & " ' )"

Note: the only reason this will work is because UserID cannot have an
apostrophe in it. If you were dealing with a name that included an
apostrophe, such as O'Reilly, you'd need to use one of the following:


strSQL = "INSERT INTO tChangeLog(txtUserID) VALUES ('" & Replace(UserID,"'",
"''" & "')"
strSQL = "INSERT INTO tChangeLog(txtUserID) VALUES (""" & UserID & """)"
strSQL = "INSERT INTO tChangeLog(txtUserID) VALUES (" & Chr$(34) & UserID &
Chr$(34) & ")"

(In case you're wondering, there's no real need to include the semi-colon at
the end of the SQL statement)