Syntax problem

  • Thread starter Thread starter sh
  • Start date Start date
S

sh

Hi' there,

I've got a form with 60 different unbound textbox fields, that i want
to fill with data from a table containing 20 records each with 3
fields. This can be done by writing i.e.:

dim i,j,rst
set rst = currentdb.openrecordset("tblTable")
rst.movefirst
Me.F01.value=rst!Field1
Me.F02.value=rst!Field2
Me.F03.value=rst!Field3
rst.movenext
Me.F04.value=rst!Field1
Me.F05.value=rst!Field2
Me.F06.value=rst!Field3
rst.movenext
..
..
....And so on.

Is there a way to dynamicly reference the names of the textbox fields,
so the code could be something like:

dim i,j,rst
set rst = currentdb.openrecordset("tblTable")
rst.movefirst
j=1
for i=1 to 20
Me."F"+format(j,"##").value=rst!Field1
Me."F"+format(j+1,"##").value=rst!Field2
Me."F"+format(j+2,"##").value=rst!Field3
j=j+3
rst.movenext
next
..
..



Kind Regards

Soren
 
Here is a cleaned up version of what you want to do.
1. First, notice I defined the type of each variable. This makes you code
more readable, and processing more efficient.
2. You need to be sure your record set has data so it does not blow up on you.
3. You can do a For Next for j inside the i loop.
4. Even though it is not syntactically required, it is a good practice to
include the variable name of your loop with the Next.
5. Your format statement will not produce the results you want. Using the
## format will give you 1, 2, 3. The 00 returns 01, 02, 03.
6. You cant format the name of a control on the fly the way you did it;
however, using the Controls object, it is possible.
7. Notice there is only one line for moving values from the fields to the
text boxes. The only thing you need to consider here is whether Field1 is
actually field 1 in the table. Field numbers are zero based, so if what you
are calling Field1 is actually the 4th field in the table, the you would
address it as j + 3
8. The + is a math operator, the & is for concatenating strings.
Dim i as Interger
Dim j as Integer
Dim rst as RecordSet
set rst = currentdb.openrecordset("tblTable")
If rst.RecordCount = 0 then
MsgBox "No Data For This Form"
GoTo This_Procedure_Exit
Else
rst.MoveLast
rst.moveFirst
End If

for i=1 to 20
For j = 1 to 3
Me.Controls("F" & format(j,"00").value=rst.Fields(j)
Next j
rst.movenext
next i
 
If you have a table structure of 3 fields, and it currently holds 20 records,
then create a form for that table (using the New Form Wizard) and set it's
Default view property to Continuous Forms. That is, on the last dialog of
the wizard, select "Modify the form's design", then click the Finish button
to access the Default view property.
 
Thanks a lot for all the information and hints, - the answer to my
syntax problem was:

Me.Controls("F" & format(j,"00").value=rst.Field­s(j)

Btw - I think in your example a minor adjustment has to be made to make
the adressing of fields work correctly, - something like:

Me.Controls("F" & format((i - 1) * 3 + j,"00").value=rst.Field­s(j)

Thanks again

Sogge
 
Back
Top