Solved

Looping through table fields

Posted on 2011-09-24
14
313 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
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
 
LVL 119

Assisted Solution

by:Rey Obrero
Rey Obrero 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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

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

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Access Running Very Slowly on Windows 7 PC 27 61
How calculate median 5 38
add more styles to my code 2 19
SQL Query 3 0
This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
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.

743 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now