Access 2000 Audit Trail - Combobox Question

Hey All

I have developed an audit trail using the sample code provided by Microsoft
as follows:

Public Function WriteAudit(frm As Form, lngPermitNumber) As Boolean
On Error GoTo err_WriteAudit
Dim varcboInstalltype As Variant

Dim ctlC As Control
Dim strSQL As String
Dim bOK As Boolean

bOK = False

DoCmd.SetWarnings False


' For each control.
For Each ctlC In frm.Controls
If TypeOf ctlC Is TextBox Or TypeOf ctlC Is CheckBox Or TypeOf ctlC Is
ComboBox Then

If ctlC.Value ctlC.OldValue Or IsNull(ctlC.OldValue) Then
If Not IsNull(ctlC.Value) Then
strSQL = "INSERT INTO tblAudit (lngPermitNumber, FieldChanged,
FieldChangedFrom, FieldChangedTo, User, DateofHit ) " & _
" SELECT " & lngPermitNumber & " , " & _
"'" & ctlC.Name & "', " & _
"'" & ctlC.OldValue & "', " & _
"'" & ctlC.Value & "', " & _
"'" & GetUserName_TSB & "', " & _
"'" & Now & "'"
'Debug.Print strSQL
DoCmd.RunSQL strSQL
End If
End If
End If
Next ctlC

WriteAudit = bOK

exit_WriteAudit:
DoCmd.SetWarnings True
Exit Function

err_WriteAudit:
If Err.Number = 2427 Or Err.Number = 3251 Then

Exit Function
End If
MsgBox Err.Number, Err.Description
Resume exit_WriteAudit

End Function

My Question is this:

If the control is a combobox, I do not want to store in the table tblAudit
the actual value of the combobox as it is usually an autonumber. I want to
store the value/text that is displayed to the user instead (usually the
second column).

For example, is there any way of capturing the old value of column 2 of
cboSize and the new value of column 2 of cboSize or will I have to do a
lookup on cboSize once I have stored as variables the actual old and new
values of the combobox?

I am working with a form that has 16 comboxes. Dlookup seems like a "clunky"
solution but I can't think of another one.

Any assistance, as always, is greatly appreciated.

Debra

I have found a very simple solution .. that is ... to create a query to
create the report to show appropriate values rather than the autonumbers.

Thanks for taking the time to read this post.

Debra


"Debra Farnham" wrote in message
...
Hey All

I have developed an audit trail using the sample code provided by
Microsoft
as follows:

Public Function WriteAudit(frm As Form, lngPermitNumber) As Boolean
On Error GoTo err_WriteAudit
Dim varcboInstalltype As Variant

Dim ctlC As Control
Dim strSQL As String
Dim bOK As Boolean

bOK = False

DoCmd.SetWarnings False


' For each control.
For Each ctlC In frm.Controls
If TypeOf ctlC Is TextBox Or TypeOf ctlC Is CheckBox Or TypeOf ctlC Is
ComboBox Then

If ctlC.Value ctlC.OldValue Or IsNull(ctlC.OldValue) Then
If Not IsNull(ctlC.Value) Then
strSQL = "INSERT INTO tblAudit (lngPermitNumber, FieldChanged,
FieldChangedFrom, FieldChangedTo, User, DateofHit ) " & _
" SELECT " & lngPermitNumber & " , " & _
"'" & ctlC.Name & "', " & _
"'" & ctlC.OldValue & "', " & _
"'" & ctlC.Value & "', " & _
"'" & GetUserName_TSB & "', " & _
"'" & Now & "'"
'Debug.Print strSQL
DoCmd.RunSQL strSQL
End If
End If
End If
Next ctlC

WriteAudit = bOK

exit_WriteAudit:
DoCmd.SetWarnings True
Exit Function

err_WriteAudit:
If Err.Number = 2427 Or Err.Number = 3251 Then

Exit Function
End If
MsgBox Err.Number, Err.Description
Resume exit_WriteAudit

End Function

My Question is this:

If the control is a combobox, I do not want to store in the table tblAudit
the actual value of the combobox as it is usually an autonumber. I want to
store the value/text that is displayed to the user instead (usually the
second column).

For example, is there any way of capturing the old value of column 2 of
cboSize and the new value of column 2 of cboSize or will I have to do a
lookup on cboSize once I have stored as variables the actual old and new
values of the combobox?

I am working with a form that has 16 comboxes. Dlookup seems like a
"clunky"
solution but I can't think of another one.

Any assistance, as always, is greatly appreciated.

Debra