Linking userID to FullName

[PC User]

Linking userID to FullName

<<BACKGROUND>>

I've made Audit Trail fields to track the actual changes to the data. As a routine, I add 4 fields to my main data tables:

CreatedBy, CreatedWhen, EditedBy, EditedWhen

On my forms, I add controls for all four and set the Visible property of them to False. The CreatedWhen control is set to default to Now(). The CreatedBy control defaults to fOSUsername() (which is a function found at http://www.mvps.org/access/api/api0008.htm to get the network logon ID of the user). In the Before Update event of the form, I add the following code:

Me!EditedBy = fOSUserName()
Me!EditedWhen = Now()

This results in a date and time stamp of when the record was created and Edited and Eho created and/or edited it.

<<PROBLEM>>

In our company's Outlook Contact List the fields include the userID, the first name and the last name. I've linked a table to this Outlook Contact List and made a query with one of the fields to concatenate the first and last names.

FullName: [Global Address List]![First] & " " & [Global Address List]![Last]

In the second column of the query I have the userID which has the field name [Account]. My question is that in the Audit Trail fields mentioned above, I want to programically have the user's full name be inserted into the fields CreatedBy and EditedBy instead of the userID number from the network. How do I link the [FullName] from the query to these fields so that the user's full name is placed into the main table.

I've tried to use DLookup to do the matching, but I get an error. Can someone help me? In the table there's a field that has the full name (last, first) and I wanted to try that field to test my programming before trying using the query's [FullName]; but I get an error after making a change in the data while in form view.

Code:
===============================================================
Private Sub Form_BeforeUpdate(Cancel As Integer)

On Error GoTo PROC_ERR

Dim frmCurrentForm As Form
'Dim strUser As String

Set frmCurrentForm = Screen.ActiveForm
frmCurrentForm![LastModified] = NOW

strUser = fOSUserName()

'Me!EditedBy = fOSUserName()
Me!EditedWhen = NOW()

Me!EditedBy = DLookup("[Display name]", "Global Address List", "[Account] =" & strUser)

Exit Sub

PROC_ERR:
MsgBox "The following error occured: " & Error$
Resume Next
End Sub
===============================================================

Thanks,
PC

 

[KenHigg]

Me!EditedBy = DLookup("[Display name]", "Global Address List", "[Account] ='" & strUser & "'")

???
kh

 

[PC User]

Thank you very much. That's the answer.

PC

 

[PC User]

That's a good point. Thanks for mentioning it. Sounds like you mean that the userID and FullName be linked through a relationship in a query. When I need to make a printed report of activities in the database, I can use a report based on that query. Do I understand you correctly?

PC