Writen username to table onOpen and onClose in a form (1 Viewer)

[selvsagt]

Writing username to table onOpen and onClose in a form

Hi.

I am trying to make a log of all the different forms in my db. It is for statistical usage only, to se which forms that are often used, and which forms who are never used.

Allen Brown has a great audit trail, but I cannot implement because I have a tekstfield as a primary key.

I need a module to write "open" or "close", username, formname and Now() to a table, and I need help on how to do this.

The tablename is: tblFormLog with the fields "LogID" primary key autonumber , "Type" text, ,"User" text, "logTime" date/time and "formName" text).

So far I have taken some bits form Allen Browns Audit code, but havent managed to make it work.

To get the username I use the code from Brown:

Function NetworkUserName() As String
    'Purpose:    Returns the network login name
    Dim lngLen As Long
    Dim lngX As Long
    Dim strUserName As String

    NetworkUserName = "Unknown"

    User = String$(254, 0)
    lngLen = 255&
    lngX = apiGetUserName(strUsername, lngLen)
    If (lngX > 0&) Then
        NetworkUserName = Left$(strUserName, lngLen - 1&)
    End If

End Function

Now I need a function to write to the table on the forms OnOpen and OnClose.
How do I form the Sql from here, and how do I implement to "capture" the forms name?

Can anyone help on this one?

 

[Guus2005]

I would use the Open and Unload events.
In the Open event

Log Me.Name, "Opening form"

In the Unload event

Log Me.Name, "Closing form"

Public Sub Log(strFormname As String, strRemark As String)

    Dim strSql As String
    Dim strUsername As String
    Dim strLogname  As String
    
    On Error GoTo Err_Log
    
    strUsername = GetName()
    strLogname = GetCurrentUserName()
    
    strSql = "INSERT INTO Log ( Username, Logname, Formname, [Timestamp], Remark ) VALUES (" & _
       "'" & strUsername & "', '" & strLogname & "', '" & strFormname & "', #" & Now() & "#, '" & Left$(strRemark, 255) & "')"

    CurrentDb.Execute strSql

Exit_Log:
    Exit Sub
Err_Log: 
    Resume Exit_Log
End Sub

Enjoy!

 

[selvsagt]

Hi.

I get a Run-time error '3075'
Syntax error in date in query expression '"23.10.2007 09:05:59#'

The tablefield for timestamp is date/time (long date with the same format as the message above).

It higlights the CurrentDB.execute strSql

The code:

strSql = "INSERT INTO tblFormLog ( Username, Logname, Formname, [Timestamp], Remark ) VALUES (" & _
       "'" & strUsername & "', '" & strLogname & "', '" & strFormname & "', #" & Now() & "#, '" & Left$(strRemark, 255) & "')"

    CurrentDb.Execute strSql

 

[Guus2005]

Seems that system regional settings and database settings are mixed up again.
Seems that your now() statement produces 23.10.2007 09:05:59, while Access expects probably 23-10-2007 09:05:59.
Store your dates as Long and timestamps as Double:

CLng(date) 
or
CDbl(now)

and convert them back:

CDate("39378") 
or
CDate("39378,4192592593")

Converting date and time to long and double circumvents the problems with dates, times and their notation in all countries.

 

[Guus2005]

Hi.

I get a Run-time error '3075'
Syntax error in date in query expression '"23.10.2007 09:05:59#'

Or you just missed the hash at the beginning of your time notation:
'#23.10.2007 09:05:59#'


HTH

 

[selvsagt]

I havent had any troubles with date/time before, and I have just plenty of modules and functions from "other" languages in the same db without any trouble.

Could it be a problem with the ' or a " or ¤ % "#¤%!#"¤!"!% or something like that

    strSql = "INSERT INTO tblFormLog ( Username, Logname, Formname, [Timestamp], Remark ) VALUES (" & _
       "'" & strUsername & "', '" & strLogname & "', '" & strFormname & "', '#" & Now() & "#, '" & Left$(strRemark, 255) & "')"

    CurrentDb.Execute strSql

 

[selvsagt]

Thanx a bunch. It was missing a '

You rock!