using variable for variable name inside loop

Posted on 2008-11-06
Last Modified: 2012-05-05
I got a loop that works great.  However I need for all the variable names that have a number in them to work with the i variable insead.
Dim i As Integer

    For i = 1 To QueryTable_addy.RecordCount

    'Debug.Print i

    'Debug.Print i - 1

        frmVerifyAddress.txtCandidate1_name.Text = QueryTable_addy.Records(i - 1).Fields(REC_CONTACT).Value

        frmVerifyAddress.txtCandidate1_code.Text = QueryTable_addy.Records(i - 1).Fields(REC_CODE).Value

        frmVerifyAddress.txtCandidate1_addr.Text = QueryTable_addy.Records(i - 1).Fields(REC_ADDR1).Value

        frmVerifyAddress.txtCandidate1_city.Text = QueryTable_addy.Records(i - 1).Fields(REC_CITY).Value

        frmVerifyAddress.txtCandidate1_state.Text = QueryTable_addy.Records(i - 1).Fields(REC_STATE).Value

        frmVerifyAddress.g_strCandidate1_addr2 = QueryTable_addy.Records(i - 1).Fields(REC_ADDR2).Value

        frmVerifyAddress.g_strCandidate1_comp = QueryTable_addy.Records(i - 1).Fields(REC_COMPANY).Value

        frmVerifyAddress.g_strCandidate1_country = QueryTable_addy.Records(i - 1).Fields(REC_COUNTRY).Value

        frmVerifyAddress.g_strCandidate1_zip = QueryTable_addy.Records(i - 1).Fields(REC_ZIP).Value

        frmVerifyAddress.g_strCandidate1_phone = QueryTable_addy.Records(i - 1).Fields(REC_PHONE).Value

        frmVerifyAddress.g_strCandidate1_email = QueryTable_addy.Records(i - 1).Fields("E-mail").Value

    Next i

Open in new window

Question by:casit
    LVL 95

    Expert Comment

    by:Lee W, MVP
    Sorry, I'm not getting your meaning... can you give an example?

    Author Comment

    Well this is what I want however it doesn't work.
    Basically have the loop fill in the correct variable name for me.  Based on the current count.  I got it working for the recordset.  However I'm having problems with the variable that I want to assign to.
    frmVerifyAddress.txtCandidate(i)_name.Text = QueryTable_addy.Records(i - 1).Fields(REC_CONTACT).Value

    Open in new window

    LVL 95

    Accepted Solution

    Ok, I think I see what you're saying... you needed to make those controls control arrays.  What are you doing this in?  Access?  VB6?

    With a control array, you would do exactly that... well almost, it would look like:
    frmVerifyAddress.txtCandidate_name(i - 1).Text = QueryTable_addy.Records(i - 1).Fields(REC_CONTACT).Value

    However, you can drop the i - 1 everywhere if you start your loop with 0.

    Dim i As Integer
        For i = 0 To QueryTable_addy.RecordCount
            frmVerifyAddress.txtCandidate_name(i).Text = QueryTable_addy.Records(i).Fields(REC_CONTACT).Value
            frmVerifyAddress.txtCandidate_code(i).Text = QueryTable_addy.Records(i).Fields(REC_CODE).Value
            frmVerifyAddress.txtCandidate_addr(i).Text = QueryTable_addy.Records(i).Fields(REC_ADDR1).Value
            frmVerifyAddress.txtCandidate_city(i).Text = QueryTable_addy.Records(i).Fields(REC_CITY).Value
            frmVerifyAddress.txtCandidate_state(i).Text = QueryTable_addy.Records(i).Fields(REC_STATE).Value
            frmVerifyAddress.g_strCandidate_addr2(i) = QueryTable_addy.Records(i).Fields(REC_ADDR2).Value
            frmVerifyAddress.g_strCandidate_comp(i) = QueryTable_addy.Records(i).Fields(REC_COMPANY).Value
            frmVerifyAddress.g_strCandidate_country(i) = QueryTable_addy.Records(i).Fields(REC_COUNTRY).Value
            frmVerifyAddress.g_strCandidate_zip(i) = QueryTable_addy.Records(i).Fields(REC_ZIP).Value
            frmVerifyAddress.g_strCandidate_phone(i) = QueryTable_addy.Records(i).Fields(REC_PHONE).Value
            frmVerifyAddress.g_strCandidate_email(i) = QueryTable_addy.Records(i).Fields("E-mail").Value
        Next i

    It's been a while since I worked heavily in VB6/VBA, but if I remember correctly, if you create a new control, then copy it, then paste it, you should be prompted if you would like to create a control array - say yes.

    Again... if I'm understanding things correctly.

    Author Comment

    When I do the following code its telling me its the same variable.

    Public g_strCandidate_addr2(1) As String
    Public g_strCandidate_addr2(2) As String

    Open in new window

    LVL 95

    Expert Comment

    by:Lee W, MVP
    Oh, sorry, that's a variable and not a control... try (keep in mind, I said it's been a while):
    Public g_strCandidate_addr2 AS Array

    Author Comment

    no such thing as array.
    And when I did
    Public g_strCandidate_addr2(5) As String
    It tells me
    Constants, fixed-length strings, arrays, user defined types  and declare statements  not allowed as public members of objects modules.

    Author Comment

    Thanks I got it all firgure out.
    LVL 95

    Expert Comment

    by:Lee W, MVP
    Like I said, it's been a while....

    Glad you got it all figured out.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Suggested Solutions

    Title # Comments Views Activity
    Window placement 17 55
    Use Multiple Forms 4 30
    Write data from SQL to CSV file - position 1 34
    VB 6.0 printer how to align 6 34
    If you have ever used Microsoft Word then you know that it has a good spell checker and it may have occurred to you that the ability to check spelling might be a nice piece of functionality to add to certain applications of yours. Well the code that…
    Most everyone who has done any programming in VB6 knows that you can do something in code like Debug.Print MyVar and that when the program runs from the IDE, the value of MyVar will be displayed in the Immediate Window. Less well known is Debug.Asse…
    Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
    Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

    779 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

    12 Experts available now in Live!

    Get 1:1 Help Now