Populating a Form with Records using DAO

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Gman063 - 06 Dec 2006 17:29 GMT

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

Reply to this Message

Douglas J. Steele - 06 Dec 2006 17:39 GMT

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.

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

>I am new to VBA DAO and I am having a problem populating a form with
>records
[quoted text clipped - 20 lines]
>
> Thanks

Reply to this Message

Gman063 - 07 Dec 2006 16:16 GMT

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.
[quoted text clipped - 22 lines]
>>
>> Thanks

Reply to this Message

Douglas J. Steele - 07 Dec 2006 16:24 GMT

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.

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

> Thanks for the reply;
>
[quoted text clipped - 46 lines]
>>>
>>> Thanks

Reply to this Message