Loop Problem with Recordset and Form Controls 1

[jpl458]

Have an open record set with 40 rows of data in two columns. The column (0) Is Location ID and is Text. The second (1) is Sumof Balls. Using the following code to populate a form with 40 Labels and 40 textboxes:

'Send query to SQL Server
rsdbpen GetBalls

'Loop through Controls
rsdbo.MoveFirst
For Each ctl In Me.Controls
If (ctl.ControlType = acLabel) Then
ctl.Caption = rsdbo.Fields(0)
End If
If (ctl.ControlType = acTextBox) Then
ctl.Value = rsdbo.Fields(1)
rsdbo.MoveNext
End If
Next ctl

The problem is the first and second times through the loop the code doesn't recognize the first "If (ctl.ControlType = acLabel) Then"
Then it does but puts the label in the first position not the third. All the Textbox data is ok.

Have no clue.

Thanks

jpl

 

[MajP]

Are you sure it is not recognizing it? I would test this to see what is really happening.

For Each ctl In Me.Controls
debug.print Debug.Print ctl.Name & " " & ctl.ControlType
If (ctl.ControlType = acLabel) Then
debug.print "Is label " & ctl.name
ctl.Caption = rsdbo.Fields(0)
End If

 

[MajP]

should be
Debug.Print ctl.Name & " " & ctl.ControlType

 

[jpl458]

MajP, ran your code with some modifications:

rsdbo.MoveFirst
For Each ctol In Me.Controls
Debug.Print rsdbo.Fields(1)
Debug.Print rsdbo.Fields(0)
Debug.Print ctol.Name & " " & ctol.ControlType
If (ctol.ControlType = acLabel) Then
Debug.Print "Is label " & ctol.Name
ctol.Caption = rsdbo.Fields(0)
End If
rsdbo.MoveNext
Next ctol

Here is what first of what showed up in the immediate window:
585 This the Value in recorset
DW01 This the location ID
01 109 This the result of "Debug.Print ctol.Name & " " & ctol.ControlType
AND 01 is name of first TextBox. Is there an order by which boxes are examined? Like First textBox then Label, etc?

785 From here it looks ok
DW02
Label5 100
Is label Label5

During work on the program I accidentally deleted the First two label boxes but replaced them with new ones
There is no tabstop for labels. Looked up type on web and 109 is dao complex text. The first two labels are being seen as labels.

Thanks for the quick response

jpl

 

[MajP]

Although it would take a little time to do the easiest solution would be to use a constant naming convention
Lets say for the lables (L1, L2, ...L40), and for the textboxes (T1,T2,...T40).

Then you code becomes something like.

dim x as integer
Do while not rsdbo.eof
x = x + 1
me.controls("L" & x) = rsdbo.fields(0)
me.controls("T" & x) = rsdbo.fields(1)
rsdbo.movenext
loop

Guarantees the correct control gets the correct value.

 

[jpl458]

MajP Changed all the control names to L1...L40 and T1...T40, but got Error
Run_time error '438'
Object does not support this property or method on statment
Me.Controls("L" & x) = rsdbo.Fields(0)

I then made a change to your code adding .value to two statements:
rsdbo.MoveFirst
Dim x As Integer
Do While Not rsdbo.EOF
x = x + 1
Me.Controls("L" & x) = rsdbo.Fields(0).Value
Me.Controls("T" & x) = rsdbo.Fields(1).Value
rsdbo.MoveNext
Loop
But got the same error
The proper data is in the recordset, the problem is in the Me.Controls("L" & x)= part, I think.
Entered ? Me.Controls("L" & x) into the immediate window and got the same error.

Thanks for your time

jpl
Using ACCESS 2010 and Windows 7
I can't see any reason this would not work

 

[jpl458]

Got it to work with

rsdbo.MoveFirst
Dim x As Integer
Do While Not rsdbo.EOF
x = x + 1
Me.Controls("L" & x).Caption = rsdbo.Fields(0).Value
Me.Controls("T" & x).Value = rsdbo.Fields(1).Value
rsdbo.MoveNext
Loop
Just added the .caption and .value and it ran as it shouls

Thanks for the direction

jpl

 

[PHV]

Me.Controls("L" & x)[!].Caption[/!] = rsdbo.Fields(0).Value
Me.Controls("T" & x).Value = rsdbo.Fields(1).Value

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[MajP]

For most objects Microsoft defines a defaulat property. When you look in the object browser you can see the default property because along with the property icon will be a little blue dot. If the object has a default property then you do not have to write it out. The default property of a textbox and dao recordset field is the "value" property. So

rsdbo.fields(0) is exactly the same as rsdbo.fields(0).value. Nothing wrong with adding ".value", but MS allows you to be lazy.
Similarly a collection class has the "item" property as a default.
Forms("form1") is the same as forms.item("form1").

SomeTextbox.value is the same as writing simply sometextbox.
But as pointed out a label does not have a default property, so I should have added the caption property and that was the problem. The ".value" is fine to add, but is not needed to fix this.

Default properties and methods make things a little easier, but can cause some confusion. Also MS provides two types of notation when dealing with collections (Bang and Dot). All of the following are the same

'dot and its defaults
Forms.Item("form1").Controls.Item("txtOne").Value
Forms.Item("form1").Controls.Item("txtOne")
Forms("form1").Controls("txtOne")
Forms("form1")("txtOne")
'bang and its defaults
Forms!form1.Controls!txtOne
Forms!form1!txtOne

And you can mix and match the above coming up with lots of variations.