Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2014
    Posts
    37

    Post Unanswered: Use combobox.oldvalue to find "old" combobox.text

    Ok, this is my first post, I hope I am doing this right, but forgive me if I miss step on the etiquette, and oh BTW... I am stuck!

    THE SITUATION: I am working on an audit table in Access 2003 (ancient). As a starting point I am using Martin Green's AuditTrail Tutorial and code and modifying it to meet my needs. (Access Tips: Add an Audit Trail to your Access Database) .

    THE GOAL: For ease of use, I want to store the TEXT value for any combobox in the audit table as opposed to the bound column value, for both old and new values after an "EDIT".

    This is pretty easy for the NewValues just by storing ctl.Text in new value field, where ctl is any combobox control.

    For the old value, I would like to take the combobox's old value (ctl.OldValue) and do a lookup to find what the "old text" was. I was attempting to do this through DLookup(), using the current bound column of the control in the search criteria. Sounds easy right?

    Here is the full code I am working with:

    Code:
    'Proceedure to record audit trail additions, edits and deletes in the Audit Trail Table  (tblAuditTrail)
    'source: http://www.fontstuff.com/access/acctut21.htm
    
    'Use with...
    
    'Prepare the Form and Add its Code:
    'The Tag property of each control to be audited must be set to Audit as described in the previous example.
    'As this example requires more actions to be audited the code that calls the AuditChanges routine is a little more complex.
    'Again, the BeforeUpdate event is used to call the AuditChanges macro when a record is saved, which happens when a new record is added or an existing record is edited.
    
    'A Procedure to call the AuditChanges routine (new and existing records):
    
    'Private Sub Form_BeforeUpdate(Cancel As Integer)
    '    If Me.NewRecord Then
    '        Call AuditChanges("EmployeeID", "NEW")
    '    Else
    '        Call AuditChanges("EmployeeID", "EDIT")
    '    End If
    'End Sub
    
    'A Procedure to call the AuditChanges routine (deleted records):
    
    'Private Sub Form_AfterDelConfirm(Status As Integer)
    '    If Status = acDeleteOK Then Call AuditChanges("EmployeeID", "DELETE")
    'End Sub
    
    
    Sub AuditChanges(IDField As String, UserAction As String, Optional SubFormName As String)
        On Error GoTo AuditChanges_Err
        Dim cnn As ADODB.Connection
        Dim rst As ADODB.Recordset
        Dim ctl As Control
        Dim datTimeCheck As Date
        Dim strUserID As String
        Dim frmCurrentForm As Form
        
        Set cnn = CurrentProject.Connection
        Set rst = New ADODB.Recordset
        rst.Open "SELECT * FROM tblAuditTrail", cnn, adOpenDynamic, adLockOptimistic
        datTimeCheck = Now()
        strUserID = Environ("USERNAME")
        
        
        If SubFormName = "" Then
            Set frmCurrentForm = Forms(Screen.ActiveForm.Name)
        Else
            Set frmCurrentForm = Forms(Screen.ActiveForm.Name).Controls(SubFormName).Form 'Screen.ActiveForm.ActiveControl.Form.ActiveControl(SubFormName)
        End If
        
        Select Case UserAction
            Case "EDIT"
                For Each ctl In frmCurrentForm.Controls
                    If ctl.ControlType = acSubform Then
                        'If the control is a subform, recursively call the fuction again to perform audit changes on subform
                        '"Forms!" & frmCurrentForm.Name & "!" & ctl.SourceObject & ".Form"
                        Call AuditChanges(IDField, UserAction, ctl.SourceObject)
                    
                    ElseIf ctl.Tag = "Audit" Then
                        If Nz(ctl.Value) <> Nz(ctl.OldValue) Then
                            
                            With rst
                                .AddNew
                                ![DateTime] = datTimeCheck
                                ![UserName] = strUserID
                                ![FormName] = frmCurrentForm.Name
                                ![Action] = UserAction
                                ![PK-FieldName] = IDField
                                ![PK-Value] = frmCurrentForm.Controls(IDField).Value
                                ![FieldName] = ctl.ControlSource
                                If ctl.ControlType = acComboBox Then
                                    ![OldValue] = DLookup("[TheTextValue]", ctl.Recordset.Name, ctl.Recordset.Fields([BoundColumnNumber]).Name & "=" & ctl.OldValue)
                                    ![NewValue] = ctl.Text 
                                Else
                                    ![OldValue] = ctl.OldValue
                                    ![NewValue] = ctl.Value
                                End If
                                .Update
                            End With
                        End If
                    End If
                Next ctl
            Case Else
                With rst
                    .AddNew
                    ![DateTime] = datTimeCheck
                    ![UserName] = strUserID
                    ![FormName] = frmCurrentForm.Name
                    ![Action] = UserAction
                    ![PK-FieldName] = IDField
                    ![PK-Value] = frmCurrentForm.Controls(IDField).Value
                    'MsgBox frmCurrentForm.Controls(IDField).Value, vbOKOnly
                    .Update
                End With
        End Select
    AuditChanges_Exit:
        On Error Resume Next
        rst.Close
        cnn.Close
        Set rst = Nothing
        Set cnn = Nothing
        Exit Sub
    AuditChanges_Err:
        MsgBox Err.Description, vbCritical, "ERROR!"
        Resume AuditChanges_Exit
    End Sub
    Specifically this is the area of code I am having trouble:



    Code:
    If ctl.ControlType = acComboBox Then
         'This works for a specific combobox.
         ![OldValue] = DLookup("[Scientific Name]", "TaxonListQry", "TSN=" & ctl.OldValue) 
                                    
         'I want a generalize for any combobox
         '![OldValue] = DLookup("[Scientific Name]", ctl.Recordset.Name, ctl.Recordset.Fields(3).Name & "=" & ctl.OldValue) 
    
         ![NewValue] = ctl.Text 
    Else
         ![OldValue] = ctl.OldValue
         ![NewValue] = ctl.Value
    End If
    This is the SQL that feeds the forms combobox:

    SELECT TSN, [Scientific Name], Family, DefaultExclude, Valid, ValidTSN, RankID, CustomTSN
    FROM TaxonComplete
    ORDER BY [Scientific Name];
    In this specific example, the bound column (TSN) is 1, but in the table used in the query (TaxonComplete), it is field number three (of 20 total table fields) such that:

    ctl.BoundColumn = 1
    ctl.Recordset.Fields(3).Name = "TSN"
    ctl.Recordset.Fields.count = 20
    ctl.Recordset.RecordCount = 24917

    I would like to take this working statement:

    ![OldValue] = DLookup("[Scientific Name]", "TaxonListQry", "TSN=" & ctl.OldValue)

    and generalize it for ANY combobox so that it reads like this:

    ![OldValue] = DLookup("TheFieldContainingReadableText", "TheQueryUsedForTheCombobox", "BoundColumnNAME=" & ctl.OldValue)

    This is what I have so far:
    ![OldValue] = DLookup("[Scientific Name]", ctl.Recordset.Name, ctl.Recordset.Fields(3).Name & "=" & ctl.OldValue)

    And I am stuck.

    ========== QUESTION TIME!! =============

    1) How do I get the ctl.boundcolumn to work within the Recordset.Fields(x) number?

    2) It seems to me it would be way easier to be able to return the NAME of the Ctl.BoundColumn but I can't find anything that works.

    Something like ctl.BoundColumn.name would be nice... along with ctl.Text.Column.Name. for question #3. Any Ideas?

    3) How do I return the ColumnName for the combobox "ReadableTextColumn" given that it will be the only non-zero value in the ctl.ColumnWidths string?

    ctl.ColumnWidths = "0;3600;0;0;0;0;0;0"



    ==================================

    Am I missing something? Is there an easier way to do this? Any help would be greatly appreciated.

    THANKS!

  2. #2
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    495
    Provided Answers: 24
    I think you have WAY overcomplicated this. I dont see any need for all this code. The combobox should be bound to a query.
    The query should have the data you want to pick and the value you want to use. No code.

    Then execute something (macro or sub) to use this value.

  3. #3
    Join Date
    Sep 2014
    Posts
    37

    clarification...

    Thanks for the reply ranman256!

    It does seem overly complicated! I surmise from your reply that I may not have been clear enough, so let me provide a little more background and information. First off, let me assure you that on all forms, every combobox is bound to a query. Generally speaking, each combobox shows descriptive information, but the bound column contains the PrimaryKey (PK) for that record.

    The Audit Trail: An Audit Trail allows someone to look back at what changes have been made to data. The audit trail table (tblAuditTrail) stores information about changes to record fields on a variety of "audited" forms and tables. An audited form is any form that contains controls where we want to track changes to the underlying record data (Edited, Added, Deleted). This includes fields where we need to know what change was made, when, and by whom. The AuditChanges() procedure is triggered by any "audited form" on the BeforeUpdate() or AfterDelConfirm() events. The change data is then stored in the AuditTable.

    How it works: When triggered, AuditChanges() scans the form for controls which are tagged to be audited and the old and new values along with the other audit information is stored in the audit table. In the case of comboboxes, the old and new values are only the bound columns (the PK values), when what I really want is the Descriptive Information associated with those PK values.

    The Difficulty: It's easy to find the cboControl.OldValue (PK in BoundColumn) and store that. But, what I need to do is lookup the Descriptive Field for that .OldValue (PK) in the combobox's underlying query and store that information. This is easy to do on a specific case with Dlookup() where I know, the "FieldName" (the descriptive info column name) and the "Criteria" (the ComboBox's BoundColumn name), but becomes difficult to generalize for use on any combobox on any form, because I need to find info for the dlookup() FieldName and Criteria fieldname, which is not stored in the properties of the combobox.

    Does this make more sense?

    What I really need...

    1) a way to find the Column Name (fieldname) for the BoundColumn of any combobox, given the bound column number (cboControl.BoundColumn = x).

    2) a way to find the Column Name (Fieldname) for the first VisibleColumn used in any combobox.

    Does this help clarify my question?

  4. #4
    Join Date
    Sep 2014
    Posts
    37

    Solved: This post provides the answer

    Here's a link to the post that solved this issue for me. THANKS!

    http://www.dbforums.com/microsoft-ac...lumn-name.html

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •