Increment Textbox?

jjyxk845

Board Regular
Joined
Sep 8, 2006
Messages
89
I have the code below and what I want to do is increment the textbox to match I. so Query_age1 would go into textbox1, Query_age2 would go into textbox2, Query_age3 would go into textbox3 etc.

Can anybody point me at the right syntax?


Code:
For I = 1 To 120 Step 1

Query_Age = "Age" & I

Me.TextBox1.Value = rs.Fields(Query_Age)

Next I

[/code]
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.
At the moment, you are looking for a field in the recordset called "Age1", "Age2", "Age3" etc.

If, as you say, you want to access a field called Query_Age1, Query_Age2 etc, then you need to say:

Code:
Query_Age = "Query_Age" & I

I may be missing something, but that appears to be the most obvious solution to your problem

Patrick
 
Upvote 0
Hmm, Maybe I didn't explain myself properly I have a form which will have 120 textboxs on labeled textbox1 to textbox120 into each of these i want to put age1 to age120.

so I created a loop to read each field in the recordset (age1 age2 etc) and want to put it into the relivant textbox(textbox1 textbox2 etc)

What i was looking for was a way to increment the textbox so it would cut down i on code rather than haveing

Code:
me.textbox1.value=rs.fields(age1)
me.textbox2.value=rs.fields(age2)

me.textbox119.value=rs.fields(age119)
me.textbox120.value=rs.fields(age120)

I could have

Code:
For I = 1 To 120 Step 1 

Query_Age = "Age" & I 

Me.TextBoxI.Value = rs.Fields(Query_Age) 

Next I

Does this make more sense. Sorry IF it doesn't I've just started VBA after useing Clarion for many years. so please bear with me. :-)
 
Upvote 0
Ah I see what you mean

As I understand it, I don't believe you can do that... an alternate approach would be the possibility of the controls in a form being kept in a collection (e.g you can access all the sheets in a workbook with the following code:

Code:
sub showsheetnames()
dim s as object
for each s in sheets
msgbox(s.name)
next s
end sub

), though as Im not sure if such a collection exists.. maybe someone more experienced in vba could help on that one. Using the natural order of the controls in, say, the textboxes collection you could then assign the various outputs of the query.


But no, I would say you couldn't concatentate to form object names, as you are trying to do, primarily because you set these at design time, and vba would not budget for such going ons in its handling of the code.

If you're worried about ugly looking code you can write stuff on one line by separating each command with a colon, but really thats just to make it look prettier and doesn't make the code any less semantically ugly.
 
Upvote 0
Ok. thanks for the that. Its not the ugly code I' worryed about its just the time taken to write it and when a simple loop could do the same thing 120 times rather than 120 lines of code.


I think there must be a way of specifing which control you want to change by a variable.

maybe if i ask the question a different way?

What i want to do is specify a textbox to add a value to by a variable ie .

Code:
variable="textbox1"

Me.Variable.value = 999

does that help?
 
Upvote 0
I see what you mean, but no as far as I am aware, having done quite a lot with forms, you cannot do that

A simple test confirms if you actually use that code on a form event... I think the explanation why is that the value to that variable is only assigned at run time. at design time that variable is taken literally, as it is not a property/method of an object and as such you will get pre-run time syntax errors.

If you want to go down that route, like I say, you're only option would be to create an object and explore the 'textboxes' collection, but i really am unsure if one exists, like it does for say workbooks and sheets.

Patrick
 
Upvote 0
Hi jjyxk845

Try the following code. It assumes you only have 120 textboxes on your form.

Dim tx As Control
For Each tx In Me.Controls
If Left(tx.Name, 7) = "TextBox" Then
Query_Age= "Age" & Mid(tx.Name, 8)
tx.Text = rs.Fields(Query_Age)
End If
Next

Hope it helps

Richard
 
Upvote 0
Hi,

That makes sense. One question though if I changed it the code(see below) and made sure the textboxes were named txtage1 etc would that work.

Code:
Dim tx As Control 
For Each tx In Me.Controls 
If Left(tx.Name, 7) = "textage" Then 
Query_Age= "Age" & Mid(tx.Name, 8) 
tx.Text = rs.Fields(Query_Age) 
End If 
Next

If so it i dont think it would matter how many textboxs you had as long as you named them correcttly.
 
Upvote 0
Hi,

That makes sense. One question though if I changed it the code(see below) and made sure the textboxes were named txtage1 etc would that work.

Code:
Dim tx As Control 
For Each tx In Me.Controls 
If Left(tx.Name, 7) = "textage" Then 
Query_Age= "Age" & Mid(tx.Name, 8) 
tx.Text = rs.Fields(Query_Age) 
End If 
Next

If so it i dont think it would matter how many textboxs you had as long as you named them correcttly.
 
Upvote 0
Hi,

That should work fine. What I meant by the 120 text boxes thing was that if you had text boxes on your form for anything else (for example a textbox at the top of your form for a name or date), the code as it is will also try and change those (and then more than likely throw up an error).


Hope it helps

Richard
 
Upvote 0

Forum statistics

Threads
1,226,453
Messages
6,191,135
Members
453,642
Latest member
jefals

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top