Re: Writing values to a table via VB

Tech-Archive recommends: Fix windows errors by optimizing your registry

Perfect. I see what the issue was.

Thanks!
PJ

"Douglas J. Steele" wrote:

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)
--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"PJFry" <PJFry@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:A36F28D3-513F-4F3F-A711-7A1C42F75A7D@xxxxxxxxxxxxxxxx
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



.

Relevant Pages

  • Re: Inability to Capture Username
    ... "GetUserNameA" (ByVal lpBuffer As String, ... Function fOSUserName() As String ... Dim lngLen As Long, lngX As Long ... Dim strUserName As String ...
    (microsoft.public.excel.programming)
  • Re: Getting NT User Login Names from Access 2000 or 2003
    ... > I have a function called fOSUserName, that I found, to get the login name ... > "GetUserNameA" (ByVal lpBuffer As String, ... > Public Function fOSUserName() As String ... > Dim lngLen As Long ...
    (microsoft.public.access.formscoding)
  • macro help
    ... "GetUserNameA" (ByVal lpBuffer As String, ... Function fOSUserName() As String ... Dim lngLen As Long, lngX As Long ...
    (microsoft.public.excel.misc)
  • Re: automation connection to Db2 tables
    ... To open the connection I do have to give a UserID and Password to get ... Dim sqlDb2, sqlCCTab, sqlCmd As String ...
    (microsoft.public.access.modulesdaovba)
  • automation connection to Db2 tables
    ... To open the connection I do have to give a UserID and Password to get through. ... Dim sqlDb2, sqlCCTab, sqlCmd As String ...
    (microsoft.public.access.modulesdaovba)