Form does not show recordset's data

[Georgios Liakopoulos]

Hi everyone and thanks for reading

I am trying to set an access form's recordset on an ADODB.Recordset that
was fabricated from scratch. This means that I have appended the fields
I want and then I added some records. Finally, I am setting the form's
recordset property on this recordset but I get #Error in the form's
controls.

Offcourse, I confirm that the controlsource of each one of the controls
matches the corresponding field values of the recordset.

(If I set the form's recordset property on a recordset made from an
existing source (table or query) the form works fine)

Any ideas?
Thanks again

 

[roger]

why not use a temporary table instead of ADODB?

 

[George Nicholson]

AFAIK, to bind controls to a Recordset, the *form needs a Recordsource*. The
Recordsource can be SQL or the name of an existing table or query. If you
want to make changes, the Recordsource must be updateable (not all queries
are).

Since your ADO recordset was "built from scratch" and exists only in memory,
the question becomes: what is the form supposed to use for a Recordsource?
It's got nothing, so you get #error.

If you want a data-bound form, you have to make that recordset into a table,
query or SQL that can be used as a Recordsource.

What you are describing is an unbound form with unbound controls. If you
want to work with that recordset, you will have to provide navigation
controls and all the record/field handling code.
Form_Open: open recordset
Forward/Back: Save "previous" record (field-by-field), move within
recordset, populate form with new/current record (field-by-field)
Form_Current: is unusable if form isn't data bound (but you can provide
your own equivalent)
(etc.)

(And what are you going to do with the results when done?...If they get
saved somewhere, why not just use that as a Recordsource in the first
place...)

 

[Douglas J. Steele]

Actually, I believe that starting with Access 2002, it's possible to set a
form's Recordset to an existing recordset.

Assuming the instantiated recordset is named the imaginative rs, you'd use

Set Me.Recordset = rs

or

Set Forms!NameOfForm.Recordset = rs

 

[George Nicholson]

aye, but I can't see how that would work for a bound form unless the
recordset being assigned is based on something that already exists: existing
table name, exsiting query name or SQL manipulating either of those things.
As I understand it, the OP is trying to create a data-bound form that is
*not* bound to any existing table. What is it supposed to bind to? Isn't
that the very definition of an unbound form?

 

[Douglas J. Steele]

Unless I'm misinterpretting what he wants, it's not that big a deal. The
form wouldn't work until you've established its recordset, but you can set
it up as a bound form (so that the ControlSource is set for each bound
control), and then set the RecordSource property to nothing.

If you want to avoid errors, you can save it completely unbound, then bind
the controls when you set the recordset:

Set Me.Recordset = rs
Me.Text1.ControlSource = "Field1"
Me.Text2.ControlSource = "Field2"

and so on.

 

[Georgios Liakopoulos]

Unless I'm misinterpretting what he wants, it's not that big a deal. The
form wouldn't work until you've established its recordset, but you can set
it up as a bound form (so that the ControlSource is set for each bound
control), and then set the RecordSource property to nothing.

If you want to avoid errors, you can save it completely unbound, then bind
the controls when you set the recordset:

Set Me.Recordset = rs
Me.Text1.ControlSource = "Field1"
Me.Text2.ControlSource = "Field2"

and so on.

Thank you George and Douglas for your help
It worked eventually...The problem was that I did not set up the
recordset properties (see the asterisks in the code). Note that this
form was a GUI element and was not supposed to pass any data to a table
or query directly. Here is the code:

'CODE START
'Dims...

Set rs_from_scratch = New ADODB.Recordset
rs_from_scratch.Fields.Append "myField1", adInteger
rs_from_scratch.Fields.Append "myField2", adVarWChar, 100
rs_from_scratch.Fields.Append "myField3", adInteger
rs_from_scratch.Fields.Append "myField4", adBoolean

'*********************************************************
rs_from_scratch.CursorType = adOpenKeyset
rs_from_scratch.LockType = adLockOptimistic
rs_from_scratch.CursorLocation = adUseClient
rs_from_scratch.Open
'*********************************************************

'Loop to retrieve values for each field to add new records to the recordset
'Retrieving values...
'Inside the loop: adding records
rs_from_scratch.AddNew
rs_from_scratch("myField1") = "..."
rs_from_scratch("myField2") = "..."
rs_from_scratch("myField3") = "..."
rs_from_scratch("myField4") = "..."
rs_from_scratch.Update
'End of Loop

'Bound form and form controls
Set Forms!FormName!SubformName.Form.Recordset = rs_from_scratch
Forms!FormName!SubformName.Form.Control1.ControlSource = "myField1"
Forms!FormName!SubformName.Form.Control2.ControlSource = "myField2"
Forms!FormName!SubformName.Form.Control3.ControlSource = "myField3"
Forms!FormName!SubformName.Form.Control4.ControlSource = "myField4"

rs_from_scratch.Close
Set rs_from_scratch = Nothing
'CODE END