I have a form that allows the user to transfer a file from one directory to another. The information about the transfer (date, time, etc) is displayed on another form. How can I collect that information in a table and also include the name of the person that transferred the document to in the same table? Similiar to using a table to collect user logon information.
Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Collect User Information in Table
- Thread starter LadyDev
- Start date
Do you have the user logging on? If so, you can create a table that contains all the fields for the information and then write vb to update whenever anychanges are made to the data. You would do this through events.
I am not sure how much info you need. You will have to be more specific about your level of understanding.
I am not sure how much info you need. You will have to be more specific about your level of understanding.
- Thread starter
- #3
Beginner - Wannabe Intermmediate. Yes, I have the user logging on (into the database), but how can I capture when they transfer a file from one place to the other in a table. I need UsrName, date/time file transferred, Name of file transferred.
Well, they have to click something to make that happen. You put code there to capture when that something is clicked, like a button; you can put code in the button to capture the file they are transfering (They have to enter a file name somewhere or click on a filename somewhere. It gets quite involved at this point. So, unless someone gets back to you first, it will take me sometime to do so. I may not be able to get back until this aternoon at somepoint.
- Thread starter
- #5
I have the form and the tables setup. I am trying to (also) capture the name of the person that is transferring the file from one directory to the other. I have not been able to do that successfully. Can someone help in this regard? This is my table setup:
tblFileHistory
FileName
FileNameID
Timestamp
Message
UserName
How do I get the name of the User in the table --OR-- the name of the machine that the user is accessing the file from. Whichever is easiest. I don't know how to do either. Thanks!
tblFileHistory
FileName
FileNameID
Timestamp
Message
UserName
How do I get the name of the User in the table --OR-- the name of the machine that the user is accessing the file from. Whichever is easiest. I don't know how to do either. Thanks!
Ok. Well, here goes. Copy the following code into a module and rename it to something like mdlLogging.
Option Compare Database 'Use database order for string comparisons
Option Explicit
Const MB_ICON_STOP = 16
Const ACT_ADD = 1
Const ACT_UPDATE = 2
Const ACT_DELETE = 3
Function ahtLog(strTableName As String, varPK1 As Variant, varPK2 As Variant, varPK3 As Variant, varPK4 As Variant, varPK5 As Variant, intAction As Integer) As Integer
' Log a user action in the log table
On Error GoTo ahtLog_Err
Dim dbCurrent As Database
Dim rstLog As Recordset
Set dbCurrent = DBEngine.Workspaces(0).Databases(0)
Set rstLog = dbCurrent.OpenRecordset("tblLog", DB_OPEN_DYNASET, DB_APPENDONLY)
rstLog.AddNew
rstLog![UserName] = CurrentUser()
rstLog![TableName] = strTableName
rstLog![Market] = varPK1 'Change the next 5 lines to your own field names that you want to track.
rstLog![IPANO] = varPK2 'You can delete any of these 5 that you don't need or if you need more
rstLog![Product] = varPK3 'you can add them. For me, I needed these 5.
rstLog![Quarter] = varPK4
rstLog![YearID] = varPK5
rstLog![ActionDate] = Now
rstLog![Action] = intAction
rstLog.Update
rstLog.Close
ahtLog = True
ahtLog_Exit:
On Error GoTo 0
Exit Function
ahtLog_Err:
MsgBox "Error " & Err & ": " & Error$, MB_ICON_STOP, "ahtLog()"
ahtLog = False
Resume ahtLog_Exit
End Function
Function ahtLogAdd(strTableName As String, varPK1 As Variant, varPK2 As Variant, varPK3 As Variant, varPK4 As Variant, varPK5 As Variant) As Integer
' Record addition of a new record in the
' log table
On Error GoTo ahtLogAdd_Err
ahtLogAdd = ahtLog(strTableName, varPK1, varPK2, varPK3, varPK4, varPK5, ACT_ADD)
ahtLogAdd_Exit:
On Error GoTo 0
Exit Function
ahtLogAdd_Err:
MsgBox "Error " & Err & ": " & Error$, MB_ICON_STOP, "ahtLogAdd()"
Resume ahtLogAdd_Exit
End Function
Function ahtLogDelete(strTableName As String, varPK1 As Variant, varPK2 As Variant, varPK3 As Variant, varPK4 As Variant, varPK5 As Variant) As Integer
' Record deletion of a record in the
' log table
On Error GoTo ahtLogDelete_Err
ahtLogDelete = ahtLog(strTableName, varPK1, varPK2, varPK3, varPK4, varPK5, ACT_DELETE)
ahtLogDelete_Exit:
On Error GoTo 0
Exit Function
ahtLogDelete_Err:
MsgBox "Error " & Err & ": " & Error$, MB_ICON_STOP, "ahtLogDelete()"
Resume ahtLogDelete_Exit
End Function
Function ahtLogUpdate(strTableName As String, varPK1 As Variant, varPK2 As Variant, varPK3 As Variant, varPK4 As Variant, varPK5 As Variant) As Integer
' Record updating of a record in the
' log table
On Error GoTo ahtLogUpdate_Err
ahtLogUpdate = ahtLog(strTableName, varPK1, varPK2, varPK3, varPK4, varPK4, ACT_UPDATE)
ahtLogUpdate_Exit:
On Error GoTo 0
Exit Function
ahtLogUpdate_Err:
MsgBox "Error " & Err & ": " & Error$, MB_ICON_STOP, "ahtLogUpdate()"
Resume ahtLogUpdate_Exit
End Function
Look at the comments in the code and make only the change in the ahtLog function where stated in comments.
Next, there are three events you need to be concerned with, After Insert, After Update, and On Delete. These are in the property dialog of the form. On each of these, you will put the following:
After Insert you will put:
=ahtLogAdd("TableName",[Field1],[Feild2],[Field3],[Fild4],[Field5])
After Update you will put:
=ahtLogUpdate("TableName",[Field1],[Field2],[Field3],[Field4],[Field5])
On Delete you will put:
=ahtLogDelete("TableName",[Field1],[Field2],[Field3],[Field4],[Field5])
You will need to change the table name and field name according to what your table and fields names are.
You will then need to create a table called tblLog with the following fields:
UserName Text (50)
ActionDate Short Date
TableName Text (50)
Field1 Text (50)
Field2 Text (50)
Field3 Text (50)
Field4 Text (50)
Field5 Text (50)
Max Text (50)
Action BYTE
Action's value will be either 1,2, or 3 for Add, Update, or Delete.
Now this will only be for getting the name of the user and what fields were changed. As for getting the file name, you must determine where that is happening in the database and try to capture that information from there. I don't have that code because Inever had to do that.
Good luck on your project. You can do another post here for the parts you don't understand and someone can help you further. This is a lot to get in one sitting.
By the way, this code was taken from a book called Microsoft Access 2.0 How-To CD. It was originally for Access 2.0, but still works for the later versions.
Option Compare Database 'Use database order for string comparisons
Option Explicit
Const MB_ICON_STOP = 16
Const ACT_ADD = 1
Const ACT_UPDATE = 2
Const ACT_DELETE = 3
Function ahtLog(strTableName As String, varPK1 As Variant, varPK2 As Variant, varPK3 As Variant, varPK4 As Variant, varPK5 As Variant, intAction As Integer) As Integer
' Log a user action in the log table
On Error GoTo ahtLog_Err
Dim dbCurrent As Database
Dim rstLog As Recordset
Set dbCurrent = DBEngine.Workspaces(0).Databases(0)
Set rstLog = dbCurrent.OpenRecordset("tblLog", DB_OPEN_DYNASET, DB_APPENDONLY)
rstLog.AddNew
rstLog![UserName] = CurrentUser()
rstLog![TableName] = strTableName
rstLog![Market] = varPK1 'Change the next 5 lines to your own field names that you want to track.
rstLog![IPANO] = varPK2 'You can delete any of these 5 that you don't need or if you need more
rstLog![Product] = varPK3 'you can add them. For me, I needed these 5.
rstLog![Quarter] = varPK4
rstLog![YearID] = varPK5
rstLog![ActionDate] = Now
rstLog![Action] = intAction
rstLog.Update
rstLog.Close
ahtLog = True
ahtLog_Exit:
On Error GoTo 0
Exit Function
ahtLog_Err:
MsgBox "Error " & Err & ": " & Error$, MB_ICON_STOP, "ahtLog()"
ahtLog = False
Resume ahtLog_Exit
End Function
Function ahtLogAdd(strTableName As String, varPK1 As Variant, varPK2 As Variant, varPK3 As Variant, varPK4 As Variant, varPK5 As Variant) As Integer
' Record addition of a new record in the
' log table
On Error GoTo ahtLogAdd_Err
ahtLogAdd = ahtLog(strTableName, varPK1, varPK2, varPK3, varPK4, varPK5, ACT_ADD)
ahtLogAdd_Exit:
On Error GoTo 0
Exit Function
ahtLogAdd_Err:
MsgBox "Error " & Err & ": " & Error$, MB_ICON_STOP, "ahtLogAdd()"
Resume ahtLogAdd_Exit
End Function
Function ahtLogDelete(strTableName As String, varPK1 As Variant, varPK2 As Variant, varPK3 As Variant, varPK4 As Variant, varPK5 As Variant) As Integer
' Record deletion of a record in the
' log table
On Error GoTo ahtLogDelete_Err
ahtLogDelete = ahtLog(strTableName, varPK1, varPK2, varPK3, varPK4, varPK5, ACT_DELETE)
ahtLogDelete_Exit:
On Error GoTo 0
Exit Function
ahtLogDelete_Err:
MsgBox "Error " & Err & ": " & Error$, MB_ICON_STOP, "ahtLogDelete()"
Resume ahtLogDelete_Exit
End Function
Function ahtLogUpdate(strTableName As String, varPK1 As Variant, varPK2 As Variant, varPK3 As Variant, varPK4 As Variant, varPK5 As Variant) As Integer
' Record updating of a record in the
' log table
On Error GoTo ahtLogUpdate_Err
ahtLogUpdate = ahtLog(strTableName, varPK1, varPK2, varPK3, varPK4, varPK4, ACT_UPDATE)
ahtLogUpdate_Exit:
On Error GoTo 0
Exit Function
ahtLogUpdate_Err:
MsgBox "Error " & Err & ": " & Error$, MB_ICON_STOP, "ahtLogUpdate()"
Resume ahtLogUpdate_Exit
End Function
Look at the comments in the code and make only the change in the ahtLog function where stated in comments.
Next, there are three events you need to be concerned with, After Insert, After Update, and On Delete. These are in the property dialog of the form. On each of these, you will put the following:
After Insert you will put:
=ahtLogAdd("TableName",[Field1],[Feild2],[Field3],[Fild4],[Field5])
After Update you will put:
=ahtLogUpdate("TableName",[Field1],[Field2],[Field3],[Field4],[Field5])
On Delete you will put:
=ahtLogDelete("TableName",[Field1],[Field2],[Field3],[Field4],[Field5])
You will need to change the table name and field name according to what your table and fields names are.
You will then need to create a table called tblLog with the following fields:
UserName Text (50)
ActionDate Short Date
TableName Text (50)
Field1 Text (50)
Field2 Text (50)
Field3 Text (50)
Field4 Text (50)
Field5 Text (50)
Max Text (50)
Action BYTE
Action's value will be either 1,2, or 3 for Add, Update, or Delete.
Now this will only be for getting the name of the user and what fields were changed. As for getting the file name, you must determine where that is happening in the database and try to capture that information from there. I don't have that code because Inever had to do that.
Good luck on your project. You can do another post here for the parts you don't understand and someone can help you further. This is a lot to get in one sitting.
By the way, this code was taken from a book called Microsoft Access 2.0 How-To CD. It was originally for Access 2.0, but still works for the later versions.
- Status
Similar threads
