using variable for variable name inside loop

Hello,
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

casitAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
Lee W, MVPConnect With a Mentor Technology and Business Process AdvisorCommented:
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.
0
 
Lee W, MVPTechnology and Business Process AdvisorCommented:
Sorry, I'm not getting your meaning... can you give an example?
0
 
casitAuthor Commented:
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

0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
casitAuthor Commented:
VB6/
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

0
 
Lee W, MVPTechnology and Business Process AdvisorCommented:
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
0
 
casitAuthor Commented:
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.
0
 
casitAuthor Commented:
Thanks I got it all firgure out.
0
 
Lee W, MVPTechnology and Business Process AdvisorCommented:
Like I said, it's been a while....

Glad you got it all figured out.
0
All Courses

From novice to tech pro — start learning today.