Attach a recordset to a subForm?

[Ian Ornstein]

in posting
http://groups.google.com/groups?hl=e...1.cgocable.net
Lyle showed us that an ADODB.Recordset can be created and attached to
a continuous (and datasheet) form.

I needed it for a data entry subform and I haven't been able to get it
to work.
Is there any rational for why it doesn't work with a subForm?

Any suggestions for a datasheet data entry subform with two columns:
a column of labels (not updateable) and a column of data entry fields.

I am interested in knowing...
Thanks,
IanO

[Steve Jorgensen]

On 16 Feb 2004 17:48:42 -0800, ia**@infoave.net (Ian Ornstein) wrote:

in posting
http://groups.google.com/groups?hl=e...1.cgocable.net
Lyle showed us that an ADODB.Recordset can be created and attached to
a continuous (and datasheet) form.

I needed it for a data entry subform and I haven't been able to get it
to work.
Is there any rational for why it doesn't work with a subForm?

Any suggestions for a datasheet data entry subform with two columns:
a column of labels (not updateable) and a column of data entry fields.

I am interested in knowing...
Thanks,
IanO

How about, instead of making your subform a data entry subform, you make your
recordset initially empty by basing it on a query that returns no rows?

[Allen Browne]

Does your subform control have anything in its
LinkMasterFields/LinkChildFields properties? If so, I would not expect the
Recordset to work, as everytime you move records in the main form the
contents of the subform are changed.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Ian Ornstein" <ia**@infoave.net> wrote in message
news:f6**************************@posting.google.c om...

in posting
http://groups.google.com/groups?hl=e...1.cgocable.net Lyle showed us that an ADODB.Recordset can be created and attached to
a continuous (and datasheet) form.

I needed it for a data entry subform and I haven't been able to get it
to work.
Is there any rational for why it doesn't work with a subForm?

Any suggestions for a datasheet data entry subform with two columns:
a column of labels (not updateable) and a column of data entry fields.

I am interested in knowing...
Thanks,
IanO

[Mike Storr]

On 16 Feb 2004 17:48:42 -0800, Ian Ornstein wrote:

in posting
http://groups.google.com/groups?hl=e...1.cgocable.net
Lyle showed us that an ADODB.Recordset can be created and attached to
a continuous (and datasheet) form.

I needed it for a data entry subform and I haven't been able to get it
to work.
Is there any rational for why it doesn't work with a subForm?

Any suggestions for a datasheet data entry subform with two columns:
a column of labels (not updateable) and a column of data entry fields.

I am interested in knowing...
Thanks,
IanO

It may depend on how you are trying to set the recordsource. Are you
referencing the subform properly - ie.
Forms!MainForm!SubFormControl.Form.Recordsource ?

--
Mike Storr
www.veraccess.com

[Lyle Fairfield]

ia**@infoave.net (Ian Ornstein) wrote in
news:f6**************************@posting.google.c om:

in posting
http://groups.google.com/groups?hl=e...w=1&selm=bmEK9
.43452%24lj.1060600%40read1.cgocable.net Lyle showed us that an
ADODB.Recordset can be created and attached to a continuous (and
datasheet) form.

I needed it for a data entry subform and I haven't been able to get it
to work.
Is there any rational for why it doesn't work with a subForm?

Any suggestions for a datasheet data entry subform with two columns:
a column of labels (not updateable) and a column of data entry fields.

I am interested in knowing...
Thanks,
IanO

Hmmmm ... I would probably do this from the main form with this code to
filter the pseudo sub form "Form1"

Option Compare Database
Dim r As ADODB.Recordset

Private Sub Form_Current()
With r
.Filter = adFilterNone
.Filter = "fldVirtual = '" & Me.fldVirtual & "'"
End With
With Form_Form1
Set .Recordset = r
End With
End Sub

Private Sub Form_Open(Cancel As Integer)
Set r = New ADODB.Recordset
With r
.CursorLocation = adUseClient
.CursorType = adOpenKeyset
.LockType = adLockOptimistic
.Fields.Append "fldVirtual", adChar, 255
.Open
.AddNew
.Collect(0) = "Malcolm"
.Update
.AddNew
.Collect(0) = "Keith"
.Update
.AddNew
.Collect(0) = "Lyle"
.Update
.MoveFirst
End With
End Sub
--
Lyle
(for e-mail refer to http://ffdba.com/contacts.htm)

[Ian Ornstein]

Thanks for you reply, Steve.

How about, instead of making your subform a data entry subform, you make your
recordset initially empty by basing it on a query that returns no rows?

It cannot be empty because the first field acts as a label identifing
the data item to be keyed into the second field. There are about two
dozen data items that may (or may not) be entered. Due to this number,
the data sheet format which emulates a grid seems appropriate.

Back to the original question:
It works as a main form but not as a subform, why?

Thanks again,
IanO

[Ian Ornstein]

Thanks for you reply,Allen.

Does your subform control have anything in its
LinkMasterFields/LinkChildFields properties?

No, its not that kind of application.
It is just a data entry grid.

I use the comboboxes and text fields on the main form later
in the process when I post the data entered into the subform

I appreciate your help, Allen.
IanO

[Ian Ornstein]

HiMike,
If you take a quick look at Lyle's clever posting you will see that he used
the RecordSet not the RecordSource.

Doesn't matter, neither worked for the subform.
I needed it for a data entry subform and I haven't been able to get it
to work. Is there any rational for why it doesn't work with a subForm?

Thanks again,
IanO

It may depend on how you are trying to set the recordsource. Are you
referencing the subform properly - ie.
Forms!MainForm!SubFormControl.Form.Recordsource ?

[Steve Jorgensen]

On 17 Feb 2004 16:42:49 -0800, ia**@infoave.net (Ian Ornstein) wrote:

Thanks for you reply, Steve.

How about, instead of making your subform a data entry subform, you make your
recordset initially empty by basing it on a query that returns no rows?

It cannot be empty because the first field acts as a label identifing
the data item to be keyed into the second field. There are about two
dozen data items that may (or may not) be entered. Due to this number,
the data sheet format which emulates a grid seems appropriate.

Back to the original question:
It works as a main form but not as a subform, why?

Thanks again,
IanO

I don't understand. You said you wanted it to be in data entry mode,
therefore it would be empty until you added a record. I was suggesting
another way to achieve that by using a where clause instead of the form's data
entry mode.

Regarding differences between master and subforms, i think someone else asked
if you were specifying Master/Child link properties for the subofrm control.
If you're setting the recordset property of a subform at run-time, you want to
-not- do that. Access will act wierd if you do.

[Ian Ornstein]

Lyle,
The Main form does not have a recordset to filter.
Sure I use some queries to populate comboboxes, but the main form
is essentially unbound. Also your code does not attach the recordset
you created to the subform...which was the essence of my original question.

The original posting works just fine for a mainform but doesn't work
for a subform. Why? Is there something I am missing?

I am still puzzled about this.
Any other ideas?

Thanks again,
IanO

in posting
http://groups.google.com/groups?hl=e...w=1&selm=bmEK9
.43452%24lj.1060600%40read1.cgocable.net Lyle showed us that an
ADODB.Recordset can be created and attached to a continuous (and
datasheet) form.

Hmmmm ... I would probably do this from the main form with this code to
filter the pseudo sub form "Form1"

Option Compare Database
Dim r As ADODB.Recordset

Private Sub Form_Current()
With r
.Filter = adFilterNone
.Filter = "fldVirtual = '" & Me.fldVirtual & "'"
End With
With Form_Form1
Set .Recordset = r
End With
End Sub

Private Sub Form_Open(Cancel As Integer)
Set r = New ADODB.Recordset
With r
.CursorLocation = adUseClient
.CursorType = adOpenKeyset
.LockType = adLockOptimistic
.Fields.Append "fldVirtual", adChar, 255
.Open
.AddNew
.Collect(0) = "Malcolm"
.Update
.AddNew
.Collect(0) = "Keith"
.Update
.AddNew
.Collect(0) = "Lyle"
.Update
.MoveFirst
End With
End Sub

[Lyle Fairfield]

ia**@infoave.net (Ian Ornstein) wrote in
news:f6**************************@posting.google.c om:

Lyle,
The Main form does not have a recordset to filter.
Sure I use some queries to populate comboboxes, but the main form
is essentially unbound. Also your code does not attach the recordset
you created to the subform...which was the essence of my original
question.

My code attached and filtered the subform's recordset from the main form's
module.

--
Lyle
(for e-mail refer to http://ffdba.com/contacts.htm)

[Ian Ornstein]

Steve,
I am sorry to have confused you.
Yes the subform is for data entry. However the user has to know what
field that s/he is entering the data value. There are over two dozen
values
to enter. So I display a grid with two colums. The first column
contains
the caption that would normally be in a label for a textbox. The
second field
is the textbox into which the data is entered.

Consider that this ADODB.RecordSet is disconnected and created in the
VB code.
I also populate the labels column and put a zero in the data field.
Perhaps 8 pairs are displayed but as the user enters the data the grid
(aka datasheet) scrolls to reveal more fields. When it is complete, I
read the data in this disconnected RecordSet and use append queries to
post the data to the real tables.

It is a workable solution... but the solution that Kyle wrote and I
quoted in the intial posting works only for the Main form. My original
question was
how to make it work for a subform.

I hope this clarifies the issue.
Thanks, Steve.

IanO

I don't understand. You said you wanted it to be in data entry mode,
therefore it would be empty until you added a record. I was suggesting
another way to achieve that by using a where clause instead of the form's data
entry mode.