User tracking

Question

Hi,

I had some help a while ago on developing some user tracking for my databases, the current setup tells me what has happened and the date and time but I want to add a who section to the table as well, my current database displays the username in a text box that is linked to the hidden login form, this text box is called Text32 on most pages, I have placed the link to the discussion that happened when I first set up this function so that you can see what I did as well as my attempt to add the third column and link it to the tracking function.

Link to the original discussion https://social.msdn.microsoft.com/Forums/office/en-US/7ea472dd-6b48-4ddd-a0eb-dc934cde3775/unexpected-end-of-statement-access-2013?forum=accessdev#7ea472dd-6b48-4ddd-a0eb-dc934cde3775

My attempt at changing the module

Option Explicit

Public LogEvent As String

Public Function LogEvt()
    Dim SQL As String
    SQL = "INSERT INTO UsysLog (Event, EvTime, User) VALUES ('" & LogEvent & _
        "', #" & Format(Now, "mm/dd/yyyy hh:nn AM/PM") & _
        "', #" &  User & "#)"
    CurrentDb.Execute SQL, dbFailOnError
End Function

My attempt at the code for the page

Private Sub Form_Load()
    LogEvent = "Form opened: " & Me.Name
    User = Me.Text32
    LogEvt

End Sub

When I attempt to run the code it brings up the module code with the phrase "Variable not defined" and the word User highlighted.

I hope that it all makes sense and that I am not too far off as I would like to think that I am starting to understand some basic coding.

Kind Regards

Richard

Answer 1

You need to declare User as a public variable, just like LogEvent.

And string variables must be enclosed in quotes, not in # characters.

In the module:

Option Explicit

Public LogEvent As String
Public User As String

Public Function LogEvt()
    Dim SQL As String
    SQL = "INSERT INTO UsysLog (Event, EvTime, User) VALUES ('" & LogEvent & _
        "', #" & Format(Now, "mm/dd/yyyy hh:nn AM/PM") & _
        "', '" &  User & "')"
     CurrentDb.Execute SQL, dbFailOnError
 End Function

---

Regards, Hans Vogelaar (http://www.eileenslounge.com)

Answer 2

Hi Hans,

It keeps glitching, I have put 2 screen grabs below to show exactly how I have laid it out.

Answer 3

Just for the fun of it try:

DoCmd.RunSQL SQL

Answer 4

Sorry, my bad. I replaced one # too many.

Public Function LogEvt()
    Dim SQL As String
    SQL = "INSERT INTO UsysLog (Event, EvTime, User) VALUES ('" & LogEvent & _
        "', #" & Format(Now, "mm/dd/yyyy hh:nn AM/PM") & _
        "#, '" &  User & "')"
     CurrentDb.Execute SQL, dbFailOnError
 End Function

---

Regards, Hans Vogelaar (http://www.eileenslounge.com)

Answer 5

Hi Hans,

It now put up this error.

Am I missing Something?

Answer 6

Just for the fun of it try:

DoCmd.RunSQL SQL

Where would I put this?

Answer 7

Please ignore me, for some reason when I coppied it over I lost a line of code

Answer 8

You forgot the declaration of User:

Public User As String

---

Regards, Hans Vogelaar (http://www.eileenslounge.com)