[ACC2012]

I've created a database that supervisors, managers, directors, and senior administration can use to log employee "Performance Events." Like praise or coaching. We have many sites and employees often "float" under different management. For this reason, all management can view all events. We want to leave it this way BUT I need to build a tracking system that monitors who's viewed what record. I've found the Allen Brown solution of LogDocOpen/LogDocClose which works great but only logs the name of the report and/or form. I need to log the specific record viewed. I can't seem to find a solution. Does anyone have suggestions?

[ScottGem]

Not hard to do but the question is how does a user select a record? That has to be controlled to be able to do this.

[ACC2012]

They would open a form and select an employee name from a combo box. I want to record which employee is chosen from the combo box.

[ScottGem]

Ok, do you want to continue to use Allen's utility? Or do you just want to record which employee was looked at, by whom and when?

If the latter you can use code like this in the After Update event of your selection combo:

Dim strSQL

strSQL = "INSERT INTO tblLog (EmployeeID, ManagerID, ReviewedWhen) " & _
"VALUES(" & Me.cboEmployee & ", '" & NetworkUserName() & "', #" & Now() &#);"
CurrentDB.Execute strSQL, dbFailOnError

You need to create a table like so:

tblLog
LogID (PK Autonumber)
EmployeeID
ManagerID
ReviewedWhen (DateTime)

The datatypes of EmployeeID and ManagerID will depend on how you have your db setup. I'm assuming that ManagerID will use the NetworkUserName() function in Allen's code. You need to use the actual name for the combo that you select the employee from.

[ACC2012]

Ok, I think I'm very close but I'm getting an error when I compile "Compile error: Sub or Function not defined." It highlights the name of the combo box in the code, specifically, the ".Employee" (my combo box name) portion. Is this because the combo box is unbound? I didn't bind this as this form is only used to run reports based on the performance event table. Below is the code I entered:

Private Sub Employee_AfterUpdate()

Dim strSQL

strSQL = "INSERT INTO tblLog (EmployeeID, ManagerID, ReviewedWhen) " & _ "VALUES(" & Me.Employee & ", '" & fOUserName() & "', #" & Now() & "#);" CurrentDb.Execute strSQL, dbFailOnError

End Sub

I appreciate all of your help!

[ScottGem]

First, the _ is a continuation character. It indicates that the code continues on the next line. I don't know whether what you copied and pasted is representative or not, but it needs to look like this:

strSQL = "INSERT INTO tblLog (EmployeeID, ManagerID, ReviewedWhen) " & _
"VALUES(" & Me.Employee & ", '" & fOUserName() & "', #" & Now() & "#);"
CurrentDb.Execute strSQL, dbFailOnError

Second, are you sure Employee is the name of the control? You can find the name by selectign the control in Design mode then looking at the Other Tab of Property Dialog.

[ACC2012]

Ok, yes, I have the _ because "VALUES starts on the next line.

Yes, design view property sheet on the combobox on other tab, name says Employee.

[ScottGem]

Hmm, then it should work. Can you attach a stripped down and zipped version to a response here?

[ACC2012]

I found that error. I needed "fOSUserName" not "fOUserName". (Ugh!) But now I'm getting a different error that says "Number of query values and destination fields are not the same." I'm thinking that this may be because my "Employee" combobox combines 3columns, employee name, ID, & Position. I did this so that when the name is chosen, below it automatically populates a text box with the ID using =[Employee].[Column](1). Same for the position. I tried changing the INSERT INTO code to Me.Employee.Column(0) but this obviously didn't work. Do I need to set some sort of parameters with VALUES?

[ScottGem]

No, it has nothing to do with that. The error means that the number of fields in your Fields clause (the list of fields on the first line) doesn't match the number or type of values in the Values clause. Since it does appear to be 3 of each. Then you need to look at the datatypes. What datatype is the EmployeeID field? Since that seems most suspect.

[ACC2012]

The datatype in the tblLog for EmployeeID is Text.

[ScottGem]

That's what I figured. Ok change the code to:

strSQL = "INSERT INTO tblLog (EmployeeID, ManagerID, ReviewedWhen) " & _
"VALUES('" & Me.Employee & "', '" & fOUserName() & "', #" & Now() & "#);"
CurrentDb.Execute strSQL, dbFailOnError

Since it's a text datatype, you have to put the single quotes around it, the same as I did for ManagerID.

[ACC2012]

I got it to work! I thought about what you said about the Employee ID field and you were right about the datatype. I needed to add ' to the beginning and end of " & Me.Employee & ". Below is the code that works. :) Thank you so much for all of your help!

Dim strSQL As String
strSQL = "INSERT INTO tblLog (EmployeeID, ManagerID, ReviewedWhen) " & _
"VALUES('"VALUES('"', '" & fOSUserName() & "', '" & Now() & "', #"
CurrentDb.Execute strSQL, dbFailOnError