setting text box control sources in form on fly

[confused]

I have a form that I want to set the record source on the fly based on the
selection that the user makes on another form. Once the form opens, than I
want to set the control sources of text boxes based on the record source of
the form. Both of my loops are working. Below is the code that I have on
the form's on load event.

RecSource = Me.RecordSource

' Set database variable to current database.
Set dbsCurrentDB = CurrentDb

' Open Recordset object.
Set rstForm = dbsCurrentDB.OpenRecordset(RecSource)

' Set a variable to hold number of columns in crosstab query.
intColumnCount = rstForm.Fields.Count

rstForm.MoveFirst

Do
I = 1

For I = 1 To intColumnCount
Me("txt" + Format(I)) = rstForm(I - 1)
Next I

rstForm.MoveNext

Loop Until rstForm.EOF

rstForm.Close

But when the form opens, the data that is showing is the data of the last
record.

What am I missing in my code?

Many thanks!

 

[Dirk Goldgar]

I have a form that I want to set the record source on the fly based on the
selection that the user makes on another form. Once the form opens, than
I
want to set the control sources of text boxes based on the record source
of
the form. Both of my loops are working. Below is the code that I have on
the form's on load event.

RecSource = Me.RecordSource

' Set database variable to current database.
Set dbsCurrentDB = CurrentDb

' Open Recordset object.
Set rstForm = dbsCurrentDB.OpenRecordset(RecSource)

' Set a variable to hold number of columns in crosstab query.
intColumnCount = rstForm.Fields.Count

rstForm.MoveFirst

Do
I = 1

For I = 1 To intColumnCount
Me("txt" + Format(I)) = rstForm(I - 1)
Next I

rstForm.MoveNext

Loop Until rstForm.EOF

rstForm.Close

But when the form opens, the data that is showing is the data of the last
record.

What am I missing in my code?

Your code is not setting controlsources, it's setting values. And since it
is looping through all the records in rstForm, the recordset you opened, it
is setting those values to the field values in the last record. There
should be no need to loop through the recordset, anyway, nor to open a
separate recordset (since the form has one of its own). Try this and see if
it works:

'----- start of (untested) code -----
Private Sub Form_Load()

Dim I As Integer

With Me.Recordset

For I = 1 To .Fields.Count
Me.Controls("txt" & I).ControlSource = .Fields(I - 1).Name
Next I

End With

End Sub
'----- end of code -----

 

[confused]

Thanks for the quick reply! I modified my code with what you provided and it
works.