By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
446,163 Members | 976 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 446,163 IT Pros & Developers. It's quick & easy.

Adding multiple records to a subform using VB

P: n/a
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
Nov 13 '05 #1
Share this Question
Share on Google+
6 Replies


P: n/a
"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

Nov 13 '05 #2

P: n/a
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

Nov 13 '05 #3

P: n/a
"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
Nov 13 '05 #4

P: n/a
"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
Nov 13 '05 #5

P: n/a
Correction .....
Set rst = Me.Form.MySubFormName.Recordset


should read:

Set rst = Me.MySubFormName.Form.Recordset
Wendy
Nov 13 '05 #6

P: n/a
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
Nov 13 '05 #7

This discussion thread is closed

Replies have been disabled for this discussion.