Loading Text Boxes on a Form

[George1111]

I am currently loading textboxes on a form using

Text1 = rstRecordset.Fields(1)
        Text2 = rstRecordset.Fields(2)
        Text3 = rstRecordset.Fields(3)
        Text4 = rstRecordset.Fields(4)
        Text5 = rstRecordset.Fields(5)
        Text6 = rstRecordset.Fields(6)
        Text7 = rstRecordset.Fields(7)
        Text8 = rstRecordset.Fields(8)
        Text9 = rstRecordset.Fields(9)
        Text10 = rstRecordset.Fields(10)
        Text11 = rstRecordset.Fields(11)
        Text12 = rstRecordset.Fields(12)
        Text13 = rstRecordset.Fields(13)
        Text14 = rstRecordset.Fields(14)
        Text15 = rstRecordset.Fields(15)
        Text16 = rstRecordset.Fields(16)
        Text17 = rstRecordset.Fields(17)
        Text18 = rstRecordset.Fields(18)
        Text19 = rstRecordset.Fields(19)
        Text20 = rstRecordset.Fields(20)

Is it possible to simplify the code by somehow doing the same in a loop, something like ...

       Dim MyControl as Control
        For I = 1 to 20
              Set MyControl.Name = "Text" & Trim(str(I))
              Set MyControl.Text = rstRecordset.Fields(I)
       Next I

This doesn't work, but I was hoping to do something along these lines

Thanks

[hensa22]

might create textbox controls array

for i=0 to 19
	txt(i).text=rstRecordset.Fields(i+1)
next

[bushmobile]

For I = 1 to 20
    Me.Controls("Text" & I).Text = rstRecordset.Fields(I)
Next I

[JeffB]

I suggest using an array of textbox. To create such an array, first, create a new textbox on your form and give it a name. Then select it, copy and paste on your form. You will be asked if you want to create a "control group", say yes. Now, if you select one of the textbox, you can see they have a value in the property "index". You can access any one of them by using this index, so you can use a loop now like in hensa22 post

JeffB

[George1111]

Thanks guys

I think the indexed text box will have to be the way to go (pity)

I was trying to create a generic form which allowed me to put the Table Name and Field Name into the Tag Property, then populate the form automatically based on the Tag Values

I was hoping to be able to use text boxes with a proper name (as against being called Text1(3) for example.

Never mind, I will just have to settle for the indexed approach

Thanks Again

[GremlinSA]

To use the Controls array you could use the following loop structure..

Dim My_TextBox As Control
Dim loop_1 As Integer
For Each My_TextBox In Form1.Controls
    For loop_1 = 1 To 20
        If UCase(My_TextBox.Name) = "TEXT" & Trim(Str(loop_1)) Then
            'My_TextBox.Text = Str(loop_1)
            My_TextBox.Text = rstRecordset.Fields(loop_1)
        End If
    Next loop_1
Next

But using a Array of Textbox controls is the better bet..

Gremmy...

[George1111]

Thanks Gremmy

Just what I wanted !

[GremlinSA]

NP George..

Just one thing to Note.. The execution time on this will be much longer than if you used a array of controls.. (Expecially if there are many other controls on the form)..

The array of controls could bring the execution time down to about 5% of the time that the above will run..


Just a Note ...

Gremmy...

[bushmobile]

@George1111: did you see post #3? it does what you want (the same as gremmy's code) in three lines...

[George1111]

Hi Bushmobile - yes, I tried it but it doesn't work


Gives "Compile Error"

"Function or Interface Marked as restricted, or the function uses an Automation Type not supported by Visual Basic"

Like you, I thought how simple can this be ! But unfortunately VB doesn't like it

Many Thanks for your encouragement

[GremlinSA]

@George1111: did you see post #3? it does what you want (the same as gremmy's code) in three lines...

Bushmobile.. I did a bit of research into this... Seems the Me.controls is a protected Collection class and is effectivly read only, althow you can make changes using the included methods

Properties and Methods of the Collection Object
Each Collection object comes with properties and methods you can use to insert, delete, and retrieve the items in the collection.
Property or method Description
Add method Add items to the collection.
Count property Return the number of items in the collection. Read-only.
Item method Return an item, by index or by key.(Default Method)
Remove method Delete an item from the collection, by index or by key.

Although on MSDN they do not explicitly state this fact, they do however imply it on this page (Control Object) as such....

Each Control object is denoted by a particular intrinsic constant. For example, the intrinsic constant acTextBox is associated with a text box control, and acCommandButton is associated with a command button. The constants for the various Microsoft Access controls are set forth in the control's ControlType property

Something else to note, every example of using Me.Controls uses a third reference (ControlType) to make changes to any control..
Adding controls at runtime
ControlType Property

What is missleading in this case is on the control Object page (same link as above) they show example of referencing controls implicitly or explicitly

' Implicitly refer to NewData control in Controls
' collection.
Me!NewData
' Use if control name contains space.
Me![New Data]
' Performance slightly slower.
Me("NewData")
' Refer to a control by its index in the controls
' collection.
Me(0)
' Refer to a NewData control by using the subform
' Controls collection.
Me.ctlSubForm.Controls!NewData
' Explicitly refer to the NewData control in the
' Controls collection.
Me.Controls!NewData
Me.Controls("NewData")
Me.Controls(0)

But note that they are only showing method to reference the controls, not how to modify values on the controls...

Hope this info is helpfull...

Gremmy....

[Shuja Ali]

BushMobile's Code is the best way of doing it.

The error that is being generated may not be because of the code that BM has posted. There can be multiple other reasons for you getting this error.

[GremlinSA]

Ahh found the problem...

For I = 1 to 20
Me.Controls("Text" & trim(ctr(I))).Text = rstRecordset.Fields(I)
Next I

should work..

with out the Trim it's trying to reference "Text 1" and not "Text1"

Gremmy...

[bushmobile]

The code i posted should (and does) work - is the error definitely occuring on that line? What version of VB are you using?

Regarding using Trim - using implicit coercion as I did ("Text" & 1) is the equivalent of using CStr() which doesn't add a leading space, unlike Str() which does. Str() should only really be used if you're trying to ensure that +ve and -ve numbers in a list line up (the leading space is not present if the number is negative)