Adding multiple records to a subform using VB

[Wendy Powley]

I have a subform which represents a 1:N relationship with the main
form. I would like to be able to read values from an external file,
fill the subform with the values read & allow the user to view/edit
the values via the subform.

I thought this would be accomplished using a simple loop; read values,
assign the various fields of the subform & repeat for each row of the
subform. Seems this is not possible, or at least I have been unable
to figure it out!

If you have any suggestions, I would greatly appreciate hearing them.

Thanks!

Wendy Powley
School of Computing
Queen's University
Kingston, ON

[John Winterbottom]

"Wendy Powley" <we***@cs.queensu.ca> wrote in message
news:85**************************@posting.google.c om...

I have a subform which represents a 1:N relationship with the main
form. I would like to be able to read values from an external file,
fill the subform with the values read & allow the user to view/edit
the values via the subform.

I thought this would be accomplished using a simple loop; read values,
assign the various fields of the subform & repeat for each row of the
subform. Seems this is not possible, or at least I have been unable
to figure it out!

If you have any suggestions, I would greatly appreciate hearing them.

From Access 2000 onwards you can programmatically access the forms'
recordset with code like this: (Make sure you set a reference to the DAO
object library)

Dim rst As DAO.Recordset
Dim i As Long

Set rst = Me.Recordset
For i = 1 To 10
rst.AddNew
rst("Field1").Value = "Value1"
rst("Field2").Value = "Value2"
rst.Update
Next i
Set rst = Nothing

[Larry Daugherty]

Hi Wendy,

In most cases you can get done what you want without any VBA code.

In a backup copy of your application, click the Autoform icon in the
Database|Forms window and create a new form based on your 1 side table.
Clean it up a bit and then extend the bottom of the form enough to hold your
subform when it's ready. Open the datasource for the form and design your
query.

Back at the Database|Forms window, create a new form in a similar fashion.
Do not put a header or footer on this form unless you have them on your
existing subform. Open the datasource for the (sub)form and design its
query. When you are happy with it, close the subform with an appropriate
name.

Open your main form in design mode and reduce its size so that you can find
your new subform in the Database|Forms window. Drag your new subform onto
your new form to a point near the left side of the form and below all
existing controls. Release the mouse and Access will relate a subform
control on your main form and place your subform within it.

Before changing for Form view, look at the properties for the subform
control. You should see the appropriate field name(s) in Link Master Field
and Link Child Field. If those names are missing you have some
troubleshooting to do in your data sources.

HTH

--
-Larry-
--

"Wendy Powley" <we***@cs.queensu.ca> wrote in message
news:85**************************@posting.google.c om...

I have a subform which represents a 1:N relationship with the main
form. I would like to be able to read values from an external file,
fill the subform with the values read & allow the user to view/edit
the values via the subform.

I thought this would be accomplished using a simple loop; read values,
assign the various fields of the subform & repeat for each row of the
subform. Seems this is not possible, or at least I have been unable
to figure it out!

If you have any suggestions, I would greatly appreciate hearing them.

Thanks!

Wendy Powley
School of Computing
Queen's University
Kingston, ON

[Wendy Powley]

"John Winterbottom" <as******@hotmail.com> wrote in message news:<2h************@uni-berlin.de>...

From Access 2000 onwards you can programmatically access the forms'
recordset with code like this: (Make sure you set a reference to the DAO
object library)
<snip code>
Hi John,

Thanks for the response. It looks like exactly what I want. However,
I tried it out (my code looks like the following):
Dim rst As DAO.Recordset
Dim i As Long

Set rst = Me.Form.MySubFormName.Recordset
For i = 1 To 10
rst.AddNew
rst("NameOfTheTextFieldToFill").Value = "Value1"

rst.Update
Next i
Set rst = Nothing

and .. nothing happens. "Value1" does not appear in my text box nor
does the database get updated.

Wendy

[John Winterbottom]

"Wendy Powley" <we***@cs.queensu.ca> wrote in message
news:85**************************@posting.google.c om...

Hi John,

Thanks for the response. It looks like exactly what I want. However,
I tried it out (my code looks like the following):
and .. nothing happens. "Value1" does not appear in my text box nor
does the database get updated.

Are you sure the code is running? Set a breakpoint and step through it
verifying that it runs through the loop the requisite number of times. Also
this line:

Set rst = Me.Form.MySubFormName.Recordset

should read

Set rst = Me.MySubFormName.Form.Recordset

[Wendy Powley]

Correction .....

Set rst = Me.Form.MySubFormName.Recordset

should read:

Set rst = Me.MySubFormName.Form.Recordset
Wendy

[Wendy Powley]

Just a follow-up to my own post in case it helps someone else.

The following works:

rst.AddNew
rst("NAME_OF_FIELD_IN_DB").Value = "somevalue"
....
rst.Update
What I was doing wrong was using the name of the control (the name of
the text box)instead of the name of the field to be updated.

Thanks for the help!

Wendy