OldValue of a ComboBox non-bound field

[dungstar]

I have a bound form, Frm, bound to tblMain.
I have a combo box, Cbo, bound by column(0), a foreign key, to tblMain.
The Cbo's RowSource comes from table tblCbo,
and populates 2 columns on the form: tblCbo.ID, tblCbo.Desc.
First column, tblCbo.ID is hidden.

What I'm trying to do is output the old value that was displayed in the combo box, i.e. column(1)'s old value (Desc), not column(0)'s (ID).

Something to the effect of:
MsgBox "The old 'Desc' was " & Cbo.Column(0).OldValue

As possible solution, I could go through the mess of extracting the writing an individual query for the combo box, open up a recordset and assign the "tblCbo.Desc" to a variable. However, unlike this theoretical scenario, I have many combo boxes.

I'm hoping there's a much easier way... Please please help!

 

[Argonak]

Yeah if I get your meaning.

For a real easy way to do this I'd make a txt box on my form somewhere. Then I'd make it invisible. Now, in your combo box event for when it gets a new value, stick the new value in the text box, and take the text box's old value and put it in your message box. Set the text box's default to whatever your combo box's default was.

sure, its a bit silly to have an invisble txt box floating around behind the scenes, but its really easy and quick to do. Just keep it locked when you're not using it.

 

[dungstar]

I was hoping there's some flexibility in the OldValue property that I could take advantage of, so I could use a universal function for all Combo Boxes using For Each acComboBox rather than write an idividual code for each one.

If I did this however, instead of combo box event, I may have to use the OnCurrent event, since an combo box event would likely change the value of text box everytime something is selected even though the record hasn't been updated and the actual OldValue didn't actually change.

Thanks for the suggestion.

 

[Mile-O]

What I'm trying to do is output the old value that was displayed in the combo box, i.e. column(1)'s old value (ID), not column(0)'s (Desc).

When? While editing the record or when reloading the record into a form?

 

[dungstar]

SJ -
Sorry, I made an error. I had Desc and ID switched around. Should read:

What I'm trying to do is output the old value that was displayed in the combo box, i.e. column(1)'s old value (Desc), not column(0)'s (ID).

To answer your question of when: After the record is updated.

Example
tblMain
ID Desc
1 Apples
2 Oranges
3 Bananas

Cbo changed from 1 - Apples to 2 - Oranges (and record is updated)
CbldValue = 1

I want to output something like
MsgBox("Your old value was " & <CbldValue_of_Column(1) = Apples>)

 

[Mile-O]

To answer your question of when: After the record is updated.

The OldValue property works while the control's value has been changed but the record hasn't actually been updated/saved.

 

[dungstar]

Right now, I am calling the function on the Form_BeforeUpdate event, actually. Sorry about the confusion. The OldValue property works. But I'm trying to get the OldValue of a column of the combo box that is not the bound column.

Cbo.Value has the same result as Cbo.Column(0) because Column(0) is bound. If I wanted to know the second column, I can simply do Cbo.Column(1).
However, if I want the OldValue, it returns the old value of the bound Column(0). I need the OldValue of Column(1), and there's no way that I know of to call Cbo.Column(1).OldValue.

 

[Pat Hartman]

Why would you care? If you know the OldValue of the bound field, you can always obtain the associated values by running a query or using DLookup()s (inferior). The reson for wanting the OldValue is usually to make log records. All you need to log is the OldValue of the bound field. The other fields aren't stored in this table anyway.

 

[dungstar]

My objective is to output an audit trail to a text box that records the old and new values (of all changes) that is comprehensible to the user. On my form, I have text boxes as well as combo boxes (i.e., the text boxes are fields from the main table and the combo boxes are foreign key fields from the main table). So the information in the text box would contain a mix of the two.

The code to log this would differentiate the acTextBox from acComboBox in appending to the audit text box (which is a memo field in the main table).

 

[Mile-O]

Have a look for ghudson's Audit Trail sampled database in the Sample Databases forum.

 

[dungstar]

Yes, I did. It's a spectacular piece of work. I'm trying to model after that, which is what I've ended up here. (Major kudos to Ghudson for that!!!)

 

[WillEllis]

Audit rail combobox-"OldText", not OldValue needed.

Yes, I did. It's a spectacular piece of work. I'm trying to model after that, which is what I've ended up here. (Major kudos to Ghudson for that!!!)

Did you ever find a solution to this? I am dealing with the same issue.

 

[RCurtin]

I have been working on this issue also. I created an array to hold the original text from Column(1) of the combo boxes and I've passed this to the AuditTrail function:

This is the code from my form.

Dim OriginalComboValues(9, 1) As Variant

Private Sub Form_Current()
    Dim intI As Integer
    Dim ctl As Control
    
    Me.txtAuditComment.Value = ""
        'keep record of original text in combo-boxes for the audit trail
    intI = 0
    For Each ctl In Me.controls  'loop through controls on form
        If ctl.ControlType = acComboBox Then 'Only check comboboxes
            OriginalComboValues(intI, 0) = ctl.Name
            OriginalComboValues(intI, 1) = ctl.Column(1)
            intI = intI + 1
        End If
    Next ctl

End Sub

Private Sub Form_BeforeUpdate(Cancel As Integer)
On Error GoTo Form_BeforeUpdate_Err
        'create audit trail of changes
    If Me.NewRecord = True Then
        Me.txtAuditComment.Visible = False
    Else
        Call fAuditTrail(Me, "PayRateID", OriginalComboValues)
    End If
          
Form_BeforeUpdate_Exit:
    Exit Sub
    
Form_BeforeUpdate_Err:
    MsgBox Err.Number & " - " & Err.Description
    Resume Form_BeforeUpdate_Exit
End Sub

Here is the part of the audit trail function that I modified:

Public Function fAuditTrail(frmPassForm As Form, strPassIDCtl As String, [B]OriginalComboValues As Variant[/B])
On Error GoTo Err_ErrorHandler

    Dim strFrmName, comment As String
    strFrmName = frmPassForm.Name
    
    If IsNull(frmPassForm.controls("txtAuditComment").Value) = False Then
        comment = frmPassForm.controls("txtAuditComment").Value
    End If
    Dim intRef As Integer
    intRef = frmPassForm.controls(strPassIDCtl).Value
    
       'If new record, record it in audit trail and exit function.
        If frmPassForm.NewRecord = True Then
          Call fAudit(strPassIDCtl, intRef, strFrmName, strPassIDCtl, "Null", CStr(intRef), comment)
            Exit Function
        End If
   

    Dim ctl As Control
    For Each ctl In frmPassForm.controls
                    Dim strCtl As String
                    Dim strOld As String
                    Dim strNew As String
        
        'Only check data entry type controls.
        Select Case ctl.ControlType
            Case acTextBox, acListBox, acOptionGroup, acCheckBox
                    'If new and old value do not equal
                    If ctl.Value <> ctl.OldValue Then
                        strCtl = ctl.Name
                        strOld = ctl.OldValue
                        strNew = ctl.Value
                        Call fAudit(strPassIDCtl, intRef, strFrmName, strCtl, strOld, strNew, comment)
                    'If old value is Null and new value is not Null
                    ElseIf IsNull(ctl.OldValue) And Len(ctl.Value) > 0 Or ctl.OldValue = "" And Len(ctl.Value) > 0 Then
                       strCtl = ctl.Name
                        strOld = "Null"
                        strNew = ctl.Value
                        Call fAudit(strPassIDCtl, intRef, strFrmName, strCtl, strOld, strNew, comment)
                    'If new value is Null and old value is not Null
                    ElseIf IsNull(ctl.Value) And Len(ctl.OldValue) > 0 Or ctl.Value = "" And Len(ctl.OldValue) > 0 Then
                        strCtl = ctl.Name
                        strOld = ctl.OldValue
                        strNew = "Null"
                        Call fAudit(strPassIDCtl, intRef, strFrmName, strCtl, strOld, strNew, comment)
                    End If
            [B]Case acComboBox[/B]
                    'If new and old value do not equal
                    If ctl.Value <> ctl.OldValue Then
                        strCtl = ctl.Name
                       [B] strOld = FindComboText(OriginalComboValues, ctl.Name)[/B]
                        [B]strNew = ctl.Column(1)[/B]
                        Call fAudit(strPassIDCtl, intRef, strFrmName, strCtl, strOld, strNew, comment)
                    'If old value is Null and new value is not Null
                    ElseIf IsNull(ctl.OldValue) And Len(ctl.Value) > 0 Or ctl.OldValue = "" And Len(ctl.Value) > 0 Then
                        strCtl = ctl.Name
                        strOld = "Null"
                        strNew = ctl.Column(1)
                        Call fAudit(strPassIDCtl, intRef, strFrmName, strCtl, strOld, strNew, comment)
                    'If new value is Null and old value is not Null
                    ElseIf IsNull(ctl.Value) And Len(ctl.OldValue) > 0 Or ctl.Value = "" And Len(ctl.OldValue) > 0 Then
                        strCtl = ctl.Name
                        strOld = FindComboText(OriginalComboValues, ctl.Name)
                        strNew = "Null"
                        Call fAudit(strPassIDCtl, intRef, strFrmName, strCtl, strOld, strNew, comment)
                    End If
        End Select
    Next ctl

Exit_ErrorHandler:
    Set ctl = Nothing
    Exit Function
    
Err_ErrorHandler:
        Select Case Err
            Case 64535 'Operation is not supported for this type of object.
                MsgBox "Operation is not supported for this type of object! Error From --- " & _
                "fAuditTrail() --- Error Number >>>  " & Err.Number & "  Error Desc >>  " & Err.Description, , conAppName
            Case Else
                MsgBox "Error From --- fAuditTrail() --- Error Number >>>  " & Err.Number _
                & "  <<< Error Description >>  " & Err.Description, , conAppName
        End Select
    Resume Exit_ErrorHandler
End Function

Function FindComboText(OriginalComboValues As Variant, comboName As String)
    
    Dim intI As Integer
    For intI = 0 To 9       'searches for the same control name in the array
        If OriginalComboValues(intI, 0) = comboName Then
            FindComboText = OriginalComboValues(intI, 1)    'finds corresponding value
        End If
    Next intI
    
End Function

There might be an easier way of doing this but I couldn't think of one!

Hope this helps someone.