Need form to show recordset data?

[TruthAndBeauty]

How do I get a form's to show data from a recordset?

I'm working with Access 2000, ADO, connections and recordsets(rst). I can populate and navigate a rst several different ways. I can append rst data to a table for use by queries, forms, reports, etc.; however, I read that a form, unlike a report, can use my rst without me having to transfer the data to a table. I don't understand how fields on the form are related/bound to the fields in my rst.

In the Form ON LOAD event, I make the db connection, populate MyRST, and then ( according to the book I can Set Me.recordset = MyRST. I don't get any errors. The book doesn't go any further than this. (MYRST is adCmdTable/Static).
(FYI - tried Me.recordsource=MyRST and Me.recordsetclone=MyRST but got an error)

The form opens but since the fields aren't bound, they are blank. I know MyRST is populated with over 200 records. I see on the form (continuous) that the record navigator shows over 200 records though no data is shown in the body of the form. How do I get the form's fields to show the MyRST (Me.Recordset) data?

I would appreciate any suggestions.

Robert

 

[jebry]

Hi Robert!

Assuming you know the names of the fields in the recordset you can just say Me!YourTextBox.ControlSource = YourField. You may be able to loop through the fields collection of the recordset like this:

Dim fld As Field
Dim strText As String
Dim intCounter As Integer

intCounter = 1
For Each fld In rst.Fields
strText = "Text" & Format(intCounter, "00&quot
Me.Controls(strText).ControlSource = fld.Name
intCounter = intCounter + 1
Next fld

Of course this assumes that you have named your text boxes appropriately.

BTW, I haven't tested this loop.

hth Jeff Bridgham
bridgham@purdue.edu

 

[Sameal]

Just remember you have EXPLICITLY set every control on your form with data.

If your form shows only one record at a time but allows you to move between records then you need create a recordset out of a query and then as the user clicks to move to next you need to move the recordset and repopulate your controls on the form. It may seem like a lot of typing and sometimes it IS. Try to group things together like Populate() 'which could create and return your recordset object. MoveNext() 'which could move one record forward in your recordset and then re-populate your controls with the new record data.

I would give coding examples but I don't use ADO, could give DAO examples though.

 

[TruthAndBeauty]

thanks for the advice. It works now.

Hey!, I also found that I can populate a rst (e.g.; rst1 populated for a stroed procedure) and then Set the form.recordset=rst1. The continuous form displays the records.