Using contol.OldValue with a combo box

[ALSav]

I have been working on an archiving feature for a database. When I update the value in a combo box, I would like the actual text showing in the combo box before and after the change to be stored in the archive rather than the integer value of the bound column (the primary key) from the table which is used to populate the box. Any ideas how I can do this??
Here's my code

For Each ctrl In Me.Controls
If (basActiveCtrl(ctrl)) Then
If ((ctrl.Value <> ctrl.OldValue) _
Or (IsNull(ctrl) And Not IsNull(ctrl.OldValue)) _
Or (Not IsNull(ctrl) And IsNull(ctrl.OldValue))) Then
If (Not IsNull(ctrl)) Then
strNewValue = ctrl.Value
Else: strNewValue = &quot;deleted&quot;
End If
If (Not IsNull(ctrl.OldValue)) Then
strOldValue = ctrl.OldValue
Else: strOldValue = &quot;blank&quot;
End If

If ctrl.ControlType = acComboBox Then
strNewValue = ctrl.Text
**************************
This is where I have problems, I want the old value of the combo box to show the text rather than the bound column integer value which
strOldValue.ctrl.OldValue returns
********************************************
Else
strField = ctrl.ControlSource
End If
key = Me!GP_SAN
fromDate = Now()

AddUpdate rstHistory, key, strField, strOldValue, strNewValue, fromDate 'Add the info to the log table

End If
End If
Next ctrl

End Sub

Function AddUpdate(rstHistory As Recordset, _
key As Integer, strField As String, strOldValue As String, strNewValue As String, fromDate As Date)


' Adds a new record to a Recordset using the data passed
' by the calling procedure. The new record is then made
' the current record.
With rstHistory
.AddNew
!GP_SAN = key 'Fields of the table
!Field = strField
!Previous_Value = strOldValue
!New_Value = strNewValue
!Date_Updated = fromDate
.UPDATE
.Bookmark = .LastModified
End With

End Function

Public Function basActiveCtrl(ctrl As Control) As Boolean

Select Case ctrl.ControlType
Case Is = acLabel
Case Is = acRectangle
Case Is = acLine
Case Is = acImage
Case Is = acCommandButton
Case Is = acOptionButton
Case Is = acCheckBox
basActiveCtrl = True
Case Is = acOptionGroup
Case Is = acBoundObjectFrame
Case Is = acTextBox
basActiveCtrl = True
Case Is = acListBox
basActiveCtrl = True
Case Is = acComboBox
basActiveCtrl = True
Case Is = acSubform
Case Is = acObjectFrame
Case Is = acPageBreak
Case Is = acPage
Case Is = acCustomControl
Case Is = acToggleButton
Case Is = acTabCtl
End Select

End Function

 

[Gzep]

Hi.

the ctl.columns(index) will give you the current text value

use the ctl.oldvalue in a DLookup to get the text string
OR
(idea only: store current value, change value back, use .columns() to get text, change value back, use .colums() to get current text.)

that's the way I'd approach the problem.

Gzep.