Looping through table fields

I have to ask: Why are you storing data in this manner? Anytime you see fields named "xxx1", "xxx2", "xxx3", I have to assume the data isn't being stored correctly, which is the reason you're having to jump through hoops like this.

Of course, this could be coming from an external source over which you have no control, but if this is something that you built, then I'd consider revisiting that design.

Thanks Andrew,
I am sure that your solution is more or less what I require.
However, the "select * from tblCustomers" suggests that it will display ALL records.
I just want to display for the ONE record currently in frmCustomers.
Typically, I will use this code when the user clicks on a button.

How would I tweak it just for the current record in the form?


Thanks too to LSM for your comments.
I appreciate the comments and it may appear that my design is flawed. However, I have my reasons which I won't bore you with!  Thanks all the same.

Thanks all for your help so far.
I am almost there but the following line is causing error : Member or data member not found

MsgBox rs.Fields(aNames(i)).Value & "             old-> " & rs.Fields(aNames(i)).OldValue

The problem is the ".OldValue" at the end.
Why does this fail when the ".Value" works fine?

Thanks!

OldValue is only usable before a record is committed to the table.  Here's some more info on it.

http://msdn.microsoft.com/en-us/library/aa172955%28v=office.11%29.aspx

When you're editing a record, the OldValues are still known until you actually save the record.  This allows you restore unedited data if needed while a user is editing a record.

Once the record is saved, the OldValue and the Current value are the same.

Also, the OldValue property is associated with Controls - not recordset fields.  That is why you are getting that error.

Okay , thanks for the help so far.

See my code attached.
I may need to restructure as the line preceded by *** uses  .OldValue  which apparently is not valid in this context.

My requirement is to be able to see BOTH the new and old value.

Ultimately, I am writing an audit of changes.
Any suggestions how I do this?

Private Sub Form_BeforeUpdate(Cancel As Integer)
    Dim cn As New ADODB.Connection
    Dim strSQL As String
    Dim rs As ADODB.Recordset
    Dim strMessage As String
    
    Dim aNames As Variant
    Dim i As Long
    
' **** Add the fields you want here - as many as you want
    aNames = Array("locked", "customername", "add1", "datereported")
    
    Set cn = CurrentProject.Connection
    strSQL = "Select * from tblCustomers where customerid =" & Me.CustomerID
    
    Set rs = cn.Execute(strSQL)
    
    rs.MoveFirst
    If Not rs.EOF Then
    
        For i = LBound(aNames) To UBound(aNames)
        
        '*****MsgBox rs.Fields(aNames(i)).Value & "             old-> " & rs.Fields(aNames(i)).OldValue
        Next i
    End If

End Sub

Select allOpen in new window

Give this a try -

Define a second array called "aControlNames"  to hold the relevant names of the controls on your form - in the same order as your fields array.

Then revise your messagebox like this

MsgBox rs.Fields(aNames(i)).Value & "             old-> " & Me.Controls(aControlNames(i)).OldValue


The OldValue property should work in this context (BeforeUpdate), since the record has not actually been committed to the table.

Btw, if you are using ALL of the fields in your table, you could have defined the aNames array by looping through the field names. Just an FYI - but it works the way you have it coded as well.

You may even be able to do something like this for populating those arrays:

dim ctl as control
I = 0
For Each ctl in me.controls
    If ctl.ControlType = acTextbox or ctl.ControlType = acCombobox  then    ' etc
          aControlNames(i) = ctl.name
          aNames(i) = ctl.ControlSource
          i = i + 1
    end if
Next

That assumes a perfect environment in which you need all editable controls, they are all bound to the relevant fields in your table, and that there are no unbound controls of the types listed in the If statement.
     

Okay , just when I though I was getting somewhere... confusion reigns!
See attached.
Click frmCustomers
Now make a change (any change) to the 2 yellow fields.
Then click "Exit" at the bottom of the screen.

The idea is that the before and after value of the name and address1 should display (But they dont).

(BYW Mbizup, I have tried the loop in posting 36593499 before but I find it lacks sufficient control (excuse the pun!))
KeyService3.accdb

It's also a good idea to prefix your object and variable names meaningfully to distinguish them from fields and from each other like this:

intMyInteger
strMyString
txtMyTextbox
cboMyCombo
lstMylistbox

(Google "Reddick VBA naming conventions"...)

Ok !  Got it now !

Thanks to all for your patience and expertise especially mbizup.