Am trying to make an access report which will capture how a user after entering their username and password use system forms in a database. the report should show all the activities that took place after the user logged in. It should also show the time.
How do i go about that in access 2007?
Thanks
mose Mbugua,
The short of it is that you will have to add code to every event procedure fired by every form, and every AfterUpdate event on every control on every Form. This is possible, but the type of thing you are asking for is incredibly extensive, as you are essentially asking the DB to log every mouse click and every data change.
You would also want a Table that could capture all those activities, so you could report it. There is no automatic activity log in MS Access.
8 1111
mose Mbugua,
The short of it is that you will have to add code to every event procedure fired by every form, and every AfterUpdate event on every control on every Form. This is possible, but the type of thing you are asking for is incredibly extensive, as you are essentially asking the DB to log every mouse click and every data change.
You would also want a Table that could capture all those activities, so you could report it. There is no automatic activity log in MS Access.
twinnyfo
what if we reduce that so that the report can only capture only forms and reports which were opened by a user? Do i have to use code or is there another way?
You must still use code. One way might be to use the OnOpen Event of each form, saving the Form Name, User Name and Time Accessed to your table.
The simplest way to do that is create a Public Function in a separate Module that saves that information to a Table--then you just call that Function any time the Form is opened.
NeoPa 32,556
Expert Mod 16PB Mose:
What if we reduce that so that the report can only capture only forms and reports which were opened by a user?
The original question was no different from this. Hence the answer is the same. Anything that is to be logged must be logged by your project by design. There is no automatic logging in Access.
Mose,
Here is my five-minute solution--there may be other/better ways to do this, but this will work...
Create a Table named tblLogActivity with the following fields: - Field Data Type
-
=======================
-
LogKey Long, AutoNumber
-
FormName Text
-
UserName Text
-
TimeAccessed Date/Time, Default Value = Now()
In the module behind each of your forms: - Option Compare Database
-
Option Explicit
-
-
Private Sub Form_Open(Cancel As Integer)
-
On Error GoTo EH
-
LogActivity Me.Form.Name, Environ("USERNAME")
-
Exit Sub
-
EH:
-
MsgBox "There was an error opening the Form! " & _
-
"Please contact your Database Administrator.", vbOKOnly, "WARNING!"
-
Exit Sub
-
End Sub
And then, in a separate Module, include the following function: - Option Compare Database
-
Option Explicit
-
-
Public Function LogActivity(FormName As String, UserName As String)
-
On Error GoTo EH
-
Dim db As Database
-
Dim strSQL As String
-
Dim rst As Recordset
-
Set db = CurrentDb()
-
strSQL = "SELECT * FROM tblLogActivity;"
-
Set rst = db.OpenRecordset(strSQL, dbOpenDynaset)
-
If Not rst.RecordCount = 0 Then
-
With rst
-
.AddNew
-
!FormName = FormName
-
!UserName = UserName
-
.Update
-
End With
-
End If
-
rst.Close
-
db.Close
-
Set rst = Nothing
-
Set db = Nothing
-
Exit Function
-
EH:
-
MsgBox "There was an error logging the activity! " & _
-
"Please contact your Database Administrator.", vbOKOnly, "WARNING!"
-
Exit Function
-
End Function
Not the fanciest code every created, but should serve as an introduction to the process. Of course, the process could be expanded to include other values also.
Am new to code guys. Guide me to doing that.
Do your forms only use macros? If so, I would encourage you to learn more about VBA and convert your macros to VBA. It is a much more powerful means of making your DB useful and flexible.
This is some Code I recently started using for logging Errors and other Events of interest. Hopefully it's not to long to post or overkill for this Question.
Also, there are some Methods in there that are not included, like GetWindowUser() and dbLocal(), if anyone wants them, I can wrangle them together.
As for Logging, the logEvent() function creates a record in SQL Server. Like Twinnyfo's example, to log an event on Form Open (Load): - Private Sub Form_Load()
-
Call logEvent("FormOpen",0, "", "FormName", 0, "")
-
End Sub
The customErrorHandler() does pretty much the same, but also Logs it to a text file as well as displays the message to the user. So then, I put the following in error handlers throughout the Database: - Call customErrorHandler(mProcedureName, True, Err, Erl, "")
Here is the Module: -
Private Const mProcedureName = "ErrorHandler"
-
Public gErrorMessage As String
-
-
Public Function customErrorHandler(ByRef sActiveObject As String, ByRef bShowError As Boolean, ByRef oErr As ErrObject, ByRef iErrorLine As Integer, ByRef sErrorMessage As String) As Boolean
-
-
Dim sError As String
-
Dim sSQL As String
-
Dim iErrorFileHandle As Integer
-
Dim sLogFile As String
-
Dim iErrorNumber As Integer
-
Dim sErrorDescription As String
-
Dim sAppDir As String
-
-
' On Error GoTo Statments reset the error object
-
iErrorNumber = oErr.Number
-
sErrorDescription = oErr.Description
-
-
On Error GoTo GotoError
-
-
DoCmd.Hourglass False
-
DoCmd.SetWarnings True
-
-
sAppDir = getAppDir()
-
iErrorFileHandle = FreeFile
-
sLogFile = sAppDir & gShortAppName & ".log"
-
-
' Build Error Message
-
sError = sError & "" & Now() & vbCrLf
-
sError = sError & " Code Library/Line: " & sActiveObject & "/" & iErrorLine & vbCrLf
-
sError = sError & " User/Computer: " & GetWindowsUser() & "/" & GetWindowsComputerName() & vbCrLf
-
sError = sError & " Error Message: " & sErrorMessage & vbCrLf
-
sError = sError & " Error Number: " & iErrorNumber & vbCrLf
-
sError = sError & " Error Description: " & sErrorDescription & vbCrLf
-
-
' Write Error message to file
-
If Len(gShortAppName) > 0 And Len(sAppDir) > 0 Then
-
Open sLogFile For Append Access Write As #iErrorFileHandle
-
Print #iErrorFileHandle, "~" & sError
-
Close #iErrorFileHandle
-
End If
-
-
' Display Error
-
If bShowError Then msgBoxError ("An Error has occured in " & gShortAppName & ":" & vbCrLf & sError)
-
-
' Insert Error into SQL
-
customErrorHandler = logEvent("RuntimeError", iErrorNumber, sErrorDescription, sActiveObject, iErrorLine, sErrorMessage)
-
-
GotoExit:
-
Exit Function
-
-
GotoError:
-
MsgBox "Error in ErrorHandler.customErrorHandler: " & Err.Description
-
Resume GotoExit
-
-
End Function
-
-
Public Function logEvent(ByRef sEventType As String, ByRef iErrorNumber As Integer, ByRef sErrorDescription As String, ByRef sActiveObject As String, ByRef iErrorLine As Integer, ByRef sErrorMessage As String) As Boolean
-
-
Dim sSQL As String
-
-
logEvent = False
-
-
' Insert Error into SQL
-
sSQL = ""
-
sSQL = sSQL & " INSERT INTO EventLog ("
-
sSQL = sSQL & " EventType "
-
sSQL = sSQL & ", ErrorNumber "
-
sSQL = sSQL & ", Description "
-
sSQL = sSQL & ", Object "
-
sSQL = sSQL & ", Line "
-
sSQL = sSQL & ", Message "
-
sSQL = sSQL & ", UserID "
-
sSQL = sSQL & ", Computer "
-
sSQL = sSQL & ", Application "
-
sSQL = sSQL & ", ApplicationVersion "
-
sSQL = sSQL & ") VALUES ( "
-
sSQL = sSQL & " '" & sEventType & "'"
-
sSQL = sSQL & ", " & iErrorNumber & ""
-
sSQL = sSQL & ", '" & trimForSQL(sErrorDescription) & "'"
-
sSQL = sSQL & ", '" & sActiveObject & "'"
-
sSQL = sSQL & ", '" & iErrorLine & "'"
-
sSQL = sSQL & ", '" & trimForSQL(sErrorMessage) & "'"
-
sSQL = sSQL & ", '" & GetWindowsUser() & "'"
-
sSQL = sSQL & ", '" & GetWindowsComputerName() & "'"
-
sSQL = sSQL & ", '" & gShortAppName & "'"
-
sSQL = sSQL & ", '" & mVersion & "'"
-
sSQL = sSQL & ")"
-
-
dbLocal.Execute sSQL
-
-
logEvent = True
-
GotoExit:
-
Exit Function
-
-
GotoError:
-
MsgBox "Error in ErrorHandler.logEvent: " & Err.Description
-
Resume GotoExit
-
-
End Function
Sign in to post your reply or Sign up for a free account.
Similar topics
by: FJ |
last post by:
Hi all,
I hope someone can help me with the following:
I want my database to make a logfile in which it puts the date and time
a code is used. The code is triggered by a timer. Every x mins it...
|
by: Tarscher |
last post by:
Hi all,
I have a user control and a form. I want the user control to be visible when
the form is shown. I haven't found a good webtutorial on this so maybee
someone can help me out?
regards...
|
by: Stephen Adam |
last post by:
Hi there,
I am working on a project were a user can update a SQL Server DB via a web
form. I've included a number of text boxes for a user to enter in strings.
The text from these boxes is then...
|
by: wish |
last post by:
Dear all,
in my application, after user login with their password then some user just can view certain page and some page is confidential. What is the command that i need to add in?
|
by: Milan Mehta |
last post by:
Dear All,
I am new to Access and trying my hand on Access 2007.
When I try to create a from using Form Wizard and then selects all fields, all text box remain of same width. If I alter width of...
|
by: SpaceMarine |
last post by:
hello,
i am having a problem creating a .ACCDE file from an Access
2007 .ACCDB file.
its somewhat large application, about 80 megs. it was originally an
Access 2000 app that somebody else...
|
by: GypsyEyes1480 |
last post by:
I am creating a database for my boss and I am a new user to Access, so pretty much I am bumbling through slowly. I created two tables with similar info for two sep semesters. I created a query for...
|
by: rawilken |
last post by:
Does anyone know of a Calendar developed for Access that mimics the functions of the traditional Outlook calendar?
|
by: jmborron |
last post by:
I have a report in Access 2007 that is populated by a parameter query. My boss wants this report ran for all 250 optoins once a month, saved as a pdf and emailed to the contacts located in a table...
|
by: afizam |
last post by:
Dear All,
I am trying to make a simple user form in Excel 2007 to be used in my workshop. This is part of the code I've made :
If Me.txtFirstName.Value = "" Then
MsgBox "Please enter a First...
|
by: DolphinDB |
last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation.
Take...
|
by: DolphinDB |
last post by:
Tired of spending countless mintues downsampling your data? Look no further!
In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
|
by: isladogs |
last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM).
In this month's session, we are pleased to welcome back...
|
by: Vimpel783 |
last post by:
Hello!
Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
|
by: jfyes |
last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
|
by: PapaRatzi |
last post by:
Hello,
I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
|
by: Defcon1945 |
last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
|
by: Shællîpôpï 09 |
last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
|
by: Faith0G |
last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
| |