Solved

Looping through table fields

Posted on 2011-09-24
14
348 Views
Last Modified: 2012-05-12
I have a table such as

CustomerID
CustName
friend1
friend2
friend3
friend4
friend5
... plus 70 more fields

I want to create a loop that displays a msgbox showing the contents of about 20 of these 70 fields

e.g.
for loopy in friend1,friend4, friend7 , friend10
msgbox loopy.value   'This should show the value of the field e.g. Jon Smith
next

I simply want to display 20 values of the 70 of the current record.
Obviously my loop syntax is all wrong.
Can anybody give me a working piece of code for the above loop.
0
Comment
Question by:Patrick O'Dea
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
14 Comments
 
LVL 17

Assisted Solution

by:andrewssd3
andrewssd3 earned 100 total points
ID: 36592047
Here are a couple of examples of how to do this.  I'm not sure how you will be getting your data, but I've created a simple query using ADODB to get a recordset.  The first method allows you to define the names you want in an array, then it loops through this and displays each one.  The second method works if you want a contiguous range of the names, say nos. 3-13.

Sub ShowFriendsSome()

    Dim cn As 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("friend1", "friend3", "friend7")
    
    ' get the connection object for the current database
    Set cn = CurrentProject.Connection
    strSQL = "Select * from tblCustomer"
    
    'create a recordset object
    Set rs = cn.Execute(strSQL)
    
    rs.MoveFirst
    If Not rs.EOF Then
    
        For i = LBound(aNames) To UBound(aNames)
            strMessage = strMessage & aNames(i) & ": " & _
                rs.Fields(aNames(i)).Value & vbCrLf
        Next i
        
        MsgBox strMessage
        
    End If

End Sub


Sub ShowFriends()

    Dim cn As ADODB.Connection
    Dim strSQL As String
    Dim rs As ADODB.Recordset
    Dim strMessage As String
    
    Dim aNames As Variant
    
    Dim i As Long
    
    ' get the connection object for the current database
    Set cn = CurrentProject.Connection
    strSQL = "Select * from tblCustomer"
    
    'create a recordset object
    Set rs = cn.Execute(strSQL)
    
    rs.MoveFirst
    If Not rs.EOF Then
    
        For i = 3 To 6
            strMessage = strMessage & "friend" & CStr(i) & ": " & _
                rs.Fields("friend" & CStr(i)).Value & vbCrLf
        Next i
        
        MsgBox strMessage
        
    End If

End Sub

Open in new window

0
 
LVL 84
ID: 36592081
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.
0
 

Author Comment

by:Patrick O'Dea
ID: 36592183
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.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 120

Assisted Solution

by:Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1) earned 100 total points
ID: 36593037
Use this to open the rs.    strSQL = "Select * from tblCustomer where customerid =" & me.customerid
0
 

Author Comment

by:Patrick O'Dea
ID: 36593331
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!
0
 
LVL 61

Expert Comment

by:mbizup
ID: 36593352
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.

0
 
LVL 61

Expert Comment

by:mbizup
ID: 36593360
Also, the OldValue property is associated with Controls - not recordset fields.  That is why you are getting that error.
0
 

Author Comment

by:Patrick O'Dea
ID: 36593422
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

Open in new window

0
 
LVL 61

Expert Comment

by:mbizup
ID: 36593469
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.
0
 
LVL 61

Expert Comment

by:mbizup
ID: 36593499
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.
     
0
 

Author Comment

by:Patrick O'Dea
ID: 36593565
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
0
 
LVL 61

Accepted Solution

by:
mbizup earned 300 total points
ID: 36593688
Okay that makes sense (and your pun does too :) )

You are checking actual fields against the oldValue of the controls.  The record has not been committed at the time of the BeforeUpdate event, so you are effectively comparing oldValue to oldValue.

Try revising that to check the current value of the controls against the oldValue of the controls.  You also need to change the Name property of your add1 textbox to "add1" (it is currently named Text77).

Private Sub Form_BeforeUpdate(Cancel As Integer)
    Dim strMessage As String    
    Dim aNames As Variant
    Dim aControlNames As Variant
    Dim i As Long
    
' **** Add the fields you want here - as many as you want
    aNames = Array("customername", "add1", "datereported")
    aControlNames = Array("customername", "add1", "datereported")
    
        For i = LBound(aControlNames) To UBound(aControlNames)
            MsgBox "loop no. " & i
            MsgBox Me.Controls(aControlNames(i)).Value & "             old-> " & Me.Controls(aControlNames(i)).OldValue
        Next i

End Sub

Open in new window

0
 
LVL 61

Expert Comment

by:mbizup
ID: 36593694
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"...)
0
 

Author Closing Comment

by:Patrick O'Dea
ID: 36593699
Ok !  Got it now !

Thanks to all for your patience and expertise especially mbizup.
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question