Populating a Form with Records using DAO

[Gman063 via AccessMonster.com]

I am new to VBA DAO and I am having a problem populating a form with records
using DAO. I have the following code in the Forms OnLoad event:

Dim dbs As Database
Dim rsFCE As Recordset

Set dbs = CurrentDb

Set rsFCE = dbs.OpenRecordset("tblAll_FCE_List")

FCEID = rsFCE!FCEID
FCENumber = rsFCE!FCENumber
FCELevel1 = rsFCE!FCELevel1
FCELevel2 = rsFCE!FCELevel2
FCELevel3 = rsFCE!FCELevel3
FCELabel = rsFCE!FCELabel
rsFCE.Close


Code runs but only returns one record. There are 214 records in the table..
What am I doing worng?

Thanks

 

[Douglas J. Steele]

Just specify tblAll_FCE_List as the Recordsource of the form. Using a bound
form will eliminate the problem.

In general, you need to loop through the recordset, looking at each row:

Set rsFCE = dbs.OpenRecordset("tblAll_FCE_List")

Do Until rsFCE.EOF
FCEID = rsFCE!FCEID
FCENumber = rsFCE!FCENumber
FCELevel1 = rsFCE!FCELevel1
FCELevel2 = rsFCE!FCELevel2
FCELevel3 = rsFCE!FCELevel3
FCELabel = rsFCE!FCELabel
rsFCE.MoveNext
Loop
rsFCE.Close

However, that won't work, because you don't have the ability to address
controls in the individual rows on a form.

 

[Gman063 via AccessMonster.com]

Thanks for the reply;

I understand that the easiest way to do this is to use a bound form. However
I am trying to increase my knowledge of VBA and DAO by understanding how to
do this without bounding the forms recordsouce. The below will cycle through
all the records; however the form still only shows one record, the last one.
What I am trying to do is basically propagate the form using VBA and the DAO
process. Not sure if what I am trying to say is clear to you. All I have been
able to do with DAO is return one record at a time in the form. The
navigation button on the form only shows one record to move through. My
original code above displayed the first record on the table; using Mr. Steele
mod only displays the last record on the table.

Any help from someone to clear this up for me will be very much appreciated.

Just specify tblAll_FCE_List as the Recordsource of the form. Using a bound
form will eliminate the problem.

In general, you need to loop through the recordset, looking at each row:

Set rsFCE = dbs.OpenRecordset("tblAll_FCE_List")

Do Until rsFCE.EOF
FCEID = rsFCE!FCEID
FCENumber = rsFCE!FCENumber
FCELevel1 = rsFCE!FCELevel1
FCELevel2 = rsFCE!FCELevel2
FCELevel3 = rsFCE!FCELevel3
FCELabel = rsFCE!FCELabel
rsFCE.MoveNext
Loop
rsFCE.Close

However, that won't work, because you don't have the ability to address
controls in the individual rows on a form.

I am new to VBA DAO and I am having a problem populating a form with
records

[quoted text clipped - 20 lines]

Thanks

 

[Douglas J. Steele]

As I said, it's not possible: you don't have the ability to address controls
in the individual rows on a form.

You could look at using a Grid control, but Access doesn't come with one:
you'd have to get it elsewhere.


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Thanks for the reply;

I understand that the easiest way to do this is to use a bound form.
However
I am trying to increase my knowledge of VBA and DAO by understanding how
to
do this without bounding the forms recordsouce. The below will cycle
through
all the records; however the form still only shows one record, the last
one.
What I am trying to do is basically propagate the form using VBA and the
DAO
process. Not sure if what I am trying to say is clear to you. All I have
been
able to do with DAO is return one record at a time in the form. The
navigation button on the form only shows one record to move through. My
original code above displayed the first record on the table; using Mr.
Steele
mod only displays the last record on the table.

Any help from someone to clear this up for me will be very much
appreciated.

Just specify tblAll_FCE_List as the Recordsource of the form. Using a
bound
form will eliminate the problem.

In general, you need to loop through the recordset, looking at each row:

Set rsFCE = dbs.OpenRecordset("tblAll_FCE_List")

Do Until rsFCE.EOF
FCEID = rsFCE!FCEID
FCENumber = rsFCE!FCENumber
FCELevel1 = rsFCE!FCELevel1
FCELevel2 = rsFCE!FCELevel2
FCELevel3 = rsFCE!FCELevel3
FCELabel = rsFCE!FCELabel
rsFCE.MoveNext
Loop
rsFCE.Close

However, that won't work, because you don't have the ability to address
controls in the individual rows on a form.

I am new to VBA DAO and I am having a problem populating a form with
records

[quoted text clipped - 20 lines]

Thanks