Filling unbound form with for-next loop

[Dave G]

I am writing a function to fill in the data in an unbound form.

I have a table with field names of 1, 2, 3 etc up to approx 100. I have
an unbound form with fields called 1, 2 3 etc.

I gave everything names like this because I wanted to simplify the
function which fills the unbound form with data. So:

forms![myForm]![1] = rs![1]

works fine, similarly with 2 and so on. But there are 100 fields so I
want to wrap the whole thing in a loop, like

for i = 1 to 100
forms![myform]![i] = rs![i]
next i

but this doesn't work.

Similarly with the field labels, I have

forms![myform]![label1] = getlabel(1) where getlabel() is a little
function that returns a text string.

Can anyone help me with the syntax to get this right so that I can open
the unbound form, and fill in all 100 field labels and field data with
just a few lines of code

Thanks
Dave

[Tom van Stiphout]

On 14 Sep 2006 07:13:09 -0700, "Dave G @ K2"
<Da*************@gmail.comwrote:

for i = 1 to 100
forms![myform].Controls(i) = rs![i]
next i

-Tom.

>I am writing a function to fill in the data in an unbound form.

I have a table with field names of 1, 2, 3 etc up to approx 100. I have
an unbound form with fields called 1, 2 3 etc.

I gave everything names like this because I wanted to simplify the
function which fills the unbound form with data. So:

forms![myForm]![1] = rs![1]

works fine, similarly with 2 and so on. But there are 100 fields so I
want to wrap the whole thing in a loop, like

for i = 1 to 100
forms![myform]![i] = rs![i]
next i

but this doesn't work.

Similarly with the field labels, I have

forms![myform]![label1] = getlabel(1) where getlabel() is a little
function that returns a text string.

Can anyone help me with the syntax to get this right so that I can open
the unbound form, and fill in all 100 field labels and field data with
just a few lines of code

Thanks
Dave

[Dave G]

Thanks Tom

It didn't work for the field value, but curiously I got it to work for
the labels. Here's a snippet of the code that I'm testing for 2 fields

For i = 1 To 2
Forms![frmCADI].Controls("lbl" & CStr(i)).Caption =
GetCADIText(i)
Forms![frmCADI].Controls(i) = rs![i]
Next i

So the fields are called 1 and 2 and the associated field labels are
called lbl1 and lbl2. If I comment out the second line then it works
fine - I see 2 field labels as expected. But with the second line in it
errors with a 3265 - item not found in this collection.

If I use:
For i = 1 To 2
Forms![frmCADI].Controls("lbl" & CStr(i)).Caption =
GetCADIText(i)
Next i
Forms![frmCADI]![1] = rs![1]
Forms![frmCADI]![2] = rs![2]

then it works great !!

Any ideas
Dave

[Lauren Quantrell]

DaveG,
There's a very easy way to do this that doesn't require the user to
rename all table columns and form fields to numbers as you have done.
All you have to do is to make sure that each field name on the foem
matches the column name in the table.
lq
Dim cmd As ADODB.command
Dim prm As ADODB.Parameter
Dim RS As ADODB.Recordset
Dim ctl As Control
Dim myID As Integer
Dim myFrm as String

Set cmd = New ADODB.command
Set cmd.ActiveConnection = CurrentProject.Connection
cmd.CommandTimeout = 0
cmd.CommandText = "StoredProcedureName"
cmd.CommandType = adCmdStoredProc

myFrm = "myFormName"
myID = Forms(myFrm).UniqueRecordID

Set prm = cmd.CreateParameter("myParameterName", adInteger,
adParamInput, , myID)
cmd.Parameters.Append prm

Set RS = cmd.Execute()

If Not RS.EOF Then
For Each ctl In Forms(myFrm).Controls
If ctl.ControlType = acTextBox Then ctl.value =
RS(ctl.Name)
Next ctl
End If
RS.Close
Set RS = Nothing
Set prm = Nothing
Set cmd = Nothing
Set ctl = Nothing



Dave G @ K2 wrote:

Thanks Tom

It didn't work for the field value, but curiously I got it to work for
the labels. Here's a snippet of the code that I'm testing for 2 fields

For i = 1 To 2
Forms![frmCADI].Controls("lbl" & CStr(i)).Caption =
GetCADIText(i)
Forms![frmCADI].Controls(i) = rs![i]
Next i

So the fields are called 1 and 2 and the associated field labels are
called lbl1 and lbl2. If I comment out the second line then it works
fine - I see 2 field labels as expected. But with the second line in it
errors with a 3265 - item not found in this collection.

If I use:
For i = 1 To 2
Forms![frmCADI].Controls("lbl" & CStr(i)).Caption =
GetCADIText(i)
Next i
Forms![frmCADI]![1] = rs![1]
Forms![frmCADI]![2] = rs![2]

then it works great !!

Any ideas
Dave

[Rick Lentz]

Dave,

Did you ever get your code to solve the issue you had populating form
fields? I ask because I have what I would call a similar issue with a
project here.

What I would like to do is write a For .. Next loop to fetch field
values in a form, then have the ability to restore those values after a
subsequent clearing of the form. Almost like a COPY/PASTE. For small
numbers of fields I've done this with a subform of hidden fields that I
shuffle field data to with a macro. Then another macro shuffles the
field data back to the main form.

trying to set a variable to a form field works fine as you know... e.g.:

box(1)= [forms]![form1]![bx1]

But in a loop (I have 80 fields!), I don't know how to specify a
"replaceable parameter" for the form field name. The below code will not
store field data from [bx1] into box(1), it will try to store [b] in
box(1)and fail because there is no field named b of course. Basically
what I need to know is how to specify the interpreted value of b as the
form field name rather than the literal b as used in the assignment
statement.

For a = 1 To 80
b = "bx" & a
box(a) = [Forms]![form1]![b]
Next

Any advice apprectiated.

Rick

*** Sent via Developersdex http://www.developersdex.com ***

[Rick Lentz]

Dave,

Did you ever get your code to solve the issue you had populating form
fields? I ask because I have what I would call a similar issue with a
project here.

What I would like to do is write a For .. Next loop to fetch field
values in a form, then have the ability to restore those values after a
subsequent clearing of the form. Almost like a COPY/PASTE. For small
numbers of fields I've done this with a subform of hidden fields that I
shuffle field data to with a macro. Then another macro shuffles the
field data back to the main form.

trying to set a variable to a form field works fine as you know... e.g.:

box(1)= [forms]![form1]![bx1]

But in a loop (I have 80 fields!), I don't know how to specify a
"replaceable parameter" for the form field name. The below code will not
store field data from [bx1] into box(1), it will try to store [b] in
box(1)and fail because there is no field named b of course. Basically
what I need to know is how to specify the interpreted value of b as the
form field name rather than the literal b as used in the assignment
statement.

For a = 1 To 80
b = "bx" & a
box(a) = [Forms]![form1]![b]
Next

Any advice apprectiated.

Rick

*** Sent via Developersdex http://www.developersdex.com ***

[Rick Lentz]

I found the solution based on hints from related posts (thanks Tom). To
get and put the contents of the fields in a form, I used the code below.
GetFields will take the contents of fields named BX1, BX2, BX3, etc. and
store them to array box(). This data can subsequently be restored after
alteration to the contents of the form fields with the PutFields code.

Public Sub Form_Load()
ReDim box(80)
------------------------------------------------------
End Sub
Public Sub GetFields()
For a = 1 To 80
b = "bx" & a
box(a) = [Forms]![form1].Controls(b)
Next
End Sub
------------------------------------------------------
Public Sub PutFields()
For a = 1 To 80
b = "bx" & a
[Forms]![form1].Controls(b) = box(a)
Next
End Sub

Rick

*** Sent via Developersdex http://www.developersdex.com ***