Adding multiple records at once..Is it possible?

[Guest]

My subform updates a table set up to track commodity increases. Each Vendor
has a commodityID that helps to group vendors that sell like commodities
together.

Currently my main form has three cascading boxes (2 combo, one list):
cboBaseComm, cboSecComm, and lstVendorID

The subform is linked to the main form by VendorID, VendorName, and
SecCommID and once the user selects a Vendor from the list box these fields
populate the subform of the new record. Then the user can enter the
estimated increase %,and date of increase, and reason.

So far all of this works as I intended. But, most of the time...the
increase %, date, and reason, for each vendor in the list box are the exact
same (because the list box only shows vendors with the same
CommodityID...ie:if steel goes up 3%, then all the vendors with the
CommodityID of steel will go up 3%, on the same date, for the same reason)
Currently the user has to select each vendor in the list box individually to
create the new records for each of them.

Is there a way to be able to tell the form that I want a new record created
for all vendors with X CommID and each new record have X % change, on X
date, for X reason? And, still have the option to select the vendors
individually if by chance the data is different on that specific CommID.

I realize I may have to do some backtracking to make this change...but I
think in the end it will be worth it.

Thanks
Diane

 

[Arvin Meyer [MVP]]

You can do it in 2 ways:

1. Create a recordset in code, and loop through it adding the new records
using the AddNew method.

2. Use an append query.

The second is fastest and can be run manually from a query, as well as from
code.

 

[Guest]

Thanks for the quick response. Unfortunately it is a little beyond my
experience....

Would you mind giving me a few more details. While I understand what and
how to use an append query on a basic level, I have never used one in a form.
I think I need to use the query builder on the forms recordsouce to make it
an append query but my problem is that the mainforms controls are
unbound...and the subform is bound to the table I want to update.

Do I need to change the controls on the mainform to bound controls of a new
query...or am I way off base?

 

[Arvin Meyer [MVP]]

From your original post, you mentioned that the subform is linked to the
main form by VendorID. That would not be possible with an unbound main form.
You could, however, fill a bound subform with data filtered by the VendorID
on a mainform.

The easiest way for an inexperienced person to use an append query in code
is to first build the append query that way you want. Then just open it in
code:

Sub cmdMyButton_Click()
DoCmd.OpenQuery "YourQueryNameHere"
End Sub

There are much more sophisticated ways, but I think that's the easiest.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

 

[David W. Fenton]

you mentioned that the subform is linked to the
main form by VendorID. That would not be possible with an unbound
main form.

Howso? If there's a control on the main form called VendorID then
you can use it as the link. Links are not limited to fields in a
parent form's recordsource -- links can be any valid expression.

 

[Guest]

Thanks for bearing with me. I could be getting the terminology mixed up.
Let me try to clarify.

The recordsource on my main form is blank (I assume that meant the main form
is unbound). The combo boxes row sources are my lookup tables (they are each
using a different table).

I linked the subform to the combo boxes manually using the using the Link
Child and Master Feilds in the properties of the subform.

I am going to mess with Arvins suggestion for a while and see what I can get
to work.

Thanks again

Diane

 

[Arvin Meyer [MVP]]

Howso? If there's a control on the main form called VendorID then
you can use it as the link. Links are not limited to fields in a
parent form's recordsource -- links can be any valid expression.

Linking in the sense of a 1 to many relationship (or even a 1 to 1
relationship) is not possible because there's nothing on the 1 side. If the
combo boxes and list box are used to filter the subform's recordsource with
a Where clause, that's not a link. In fact, I just tried to create the link
in the Access 97 test database I use and it wouldn't do it saying that you
cannot build the link. Symantics are not important though.

 

[David W. Fenton]

Linking in the sense of a 1 to many relationship (or even a 1 to 1
relationship) is not possible because there's nothing on the 1
side.

That it's unbound does *not* mean there's nothing to link to -- all
you need is a control that has a value.

If the
combo boxes and list box are used to filter the subform's
recordsource with a Where clause, that's not a link. In fact, I
just tried to create the link in the Access 97 test database I use
and it wouldn't do it saying that you cannot build the link.
Symantics are not important though.

My understanding was that the combo box was used as the parent
value, so that when you chose a value from the combo box, the
subform would display the matching values. This is a perfectly valid
approach and does not require a recordsource in the parent form, or
assigning the recordsource of the child form.

 

[Guest]

My understanding was that the combo box was used as the parent
value, so that when you chose a value from the combo box, the
subform would display the matching values. This is a perfectly valid
approach and does not require a recordsource in the parent form, or
assigning the recordsource of the child form.

David

The above is exactly how I have it set up. And it works fine if I only want
to add a single record at a time.

I am still struggling with making it add multiple record though. I know
that at some point I am going to need to use an append query, but I still
don't understand exactly how. The table I am adding records has an
autonumber PK that I need to be created, along with the other fields I
mentioned earlier. And If I already have records in the table from a
previous date under a specific vendor name and/or Commodity code, I do not
want to change those; I want to create a new record all together with a new
PK #.

Could one of you give me a little more detail on what needs to be done to
make this happen?

Thanks,
Diane

 

[David W. Fenton]

The above is exactly how I have it set up. And it works fine if I
only want to add a single record at a time.

I have no idea why it shouldn't work for multiple records.

I am still struggling with making it add multiple record though.
I know that at some point I am going to need to use an append
query,

I don't see why. With a parent link on the combo box, which would
provide the PK of the parent record, you should be able to add
records to the subform, and the foreign key value would be populated
automatically, without any need for an append query -- you'd just
have to make sure your subform allowed record additions.

but I still
don't understand exactly how. The table I am adding records has
an autonumber PK that I need to be created, along with the other
fields I mentioned earlier.

When you add a record, the Autonumber field of the child table will
be populated automatically. And the foreign key field (which is
*not* an Autonumber, but a long integer) will be populated
automatically when you add a record in the subform because you've
properly linked the parent and child form on the combo box that
provides the PK value of the parent record.

And If I already have records in the table from a
previous date under a specific vendor name and/or Commodity code,
I do not want to change those; I want to create a new record all
together with a new PK #.

Something is missing from your description of the situation. I see
no reason why you shouldn't simply be able to click the asterisk
button in the child form and start entering data in the new record.

 

[Guest]

I am not expaining this clearly. I apologize. Let me try to clarify:
The combo boxes in my main form cascade. Base Comm, Sec Comm, Vendor Name
(and vendorID).

The user chooses a Base Comm, then a Sec Comm (from a list filtered off
cboBaseComm) then a Vendor (from a list filtered off cboSecComm). It is not
until a Vendor name (and ID) is chosen that the record in the subform is
added.

This new record added includes the three fields in the main form that were
selected, automatically creates the PK (autonumber), and has blank fields for
% change, effectdate, and explaination.

This is okay...but most often the user has to duplicate the same information
for each of the vendors listed in the main form combo box. (ie: All vendors
that sell steel increase at the same time, on the same date, the same percent)

What a really want to happen is this. Once the user selcts a Base and Sec
comm code, they can input data into %change, effectdate, and explaination
fields in the main form (i think). Then, this new data will be added, along
with the BaseCode, SecCodes, to a new record for each of the Vendors listed
in the Vendor combo box (filtered off the cboSecCode). For example, all at
once, I want to be able to add five records that are identical with exception
to the PK and VendorID and Vendor Name feilds.

I know that I am close, but I am just not sure what steps I am missing.

Thanks again for your help,

Diane

 

[David W. Fenton]

I am not expaining this clearly. I apologize. Let me try to
clarify:
The combo boxes in my main form cascade. Base Comm, Sec Comm,
Vendor Name
(and vendorID).

The user chooses a Base Comm, then a Sec Comm (from a list
filtered off cboBaseComm) then a Vendor (from a list filtered off
cboSecComm). It is not until a Vendor name (and ID) is chosen
that the record in the subform is added.

So far I see nothing that contradicts the model in my head that
drove my responses.

This new record added includes the three fields in the main form
that were selected,

Why would you need that? If the combos are based on each other,
surely the last, the Vendor, implies the other two, and storing the
values from those two is un-normalized duplication of data.

automatically creates the PK (autonumber), and has blank fields
for % change, effectdate, and explaination.

You're saying that the AfterUpdate event of the Vendor combo runs
code that adds a new record in the subform? OK.

This is okay...but most often the user has to duplicate the same
information for each of the vendors listed in the main form combo
box. (ie: All vendors that sell steel increase at the same time,
on the same date, the same percent)

How about a multiselect listbox instead of a combo box, then? You
could have the AfterUpdate of the previous combo box fire code that
selects all the items in the listbox, and the user could then
deselect any that don't apply. Then you'd have a command button to
add the records, which would work by walking through the listbox and
checking the .Selected property. Or maybe there's a Selected
collection -- I forget. But that would collect the VendorIDs and you
could use that as criteria for an append query based on your Vendor
table, filtered on the list of VendorIDs collected by walking
through the selected items in the listbox.

What a really want to happen is this. Once the user selcts a Base
and Sec comm code, they can input data into %change, effectdate,
and explaination fields in the main form (i think). Then, this
new data will be added, along with the BaseCode, SecCodes, to a
new record for each of the Vendors listed in the Vendor combo box
(filtered off the cboSecCode).

Then adding to what I said above, add unbound fields for the three
values you need in each record and then click the button that does
the append. You'd then requery the subform to display the matching
records, which would no longer be linked on VendorID, but on the
first two fields you selected (and you'd have to link to the values
in the appropriate tables from the vendor table in your recordsource
for your subform)..

For example, all at
once, I want to be able to add five records that are identical
with exception to the PK and VendorID and Vendor Name feilds.

Don't even worry about the child table's PK, since that is going to
be populated when the records are appended to the table. All you
need to know is the value of the VendorIDs.

 

[Guest]

Why would you need that? If the combos are based on each other,
surely the last, the Vendor, implies the other two, and storing the
values from those two is un-normalized duplication of data.

Thank you...you are correct...I don't know how I overlooked that...

How about a multiselect listbox instead of a combo box, then? You
could have the AfterUpdate of the previous combo box fire code that
selects all the items in the listbox, and the user could then
deselect any that don't apply. Then you'd have a command button to
add the records, which would work by walking through the listbox and
checking the .Selected property. Or maybe there's a Selected
collection -- I forget. But that would collect the VendorIDs and you
could use that as criteria for an append query based on your Vendor
table, filtered on the list of VendorIDs collected by walking
through the selected items in the listbox.

Then adding to what I said above, add unbound fields for the three
values you need in each record and then click the button that does
the append. You'd then requery the subform to display the matching
records, which would no longer be linked on VendorID, but on the
first two fields you selected (and you'd have to link to the values
in the appropriate tables from the vendor table in your recordsource
for your subform)..

Don't even worry about the child table's PK, since that is going to
be populated when the records are appended to the table. All you
need to know is the value of the VendorIDs.

Thank you... this is where I was headed but I was stuck on what to do next.
This will give me a lot to try out (as I am still learning VBA). Hopefully I
won't have too many more questions before I get it up and running.

Thanks again
DIane

 

[David W. Fenton]

this is where I was headed but I was stuck on what to do next.
This will give me a lot to try out (as I am still learning VBA).
Hopefully I won't have too many more questions before I get it up
and running.

Basically, the concept is quite simple

1. you need to collect several values that all records will have.

2. you need to select the multiple vendors that these records will
apply to.

So, you need an unbound form where you put in the values for 1) and
some method of choosing multiple vendors. I suggested a multiselect
listbox, but getting the data out of that is fairly complicated (I
always have to look it up myself!).

Another alternative would be to use a combo box and an unbound text
box. Have the combo box's AfterUpdate event append the VendorID to
the unbound textbox:

Me!txtVendorList = (Me!VendorList + " OR ") & Me!cmbVendors

You'll end up with a list of VendorIDs like this:

1 OR 2 OR 3 OR 4

And then you can use Application.BuildCriteria to convert that to a
format for use in a WHERE clause:

strWhere = "WHERE " _
& Application.BuildCriteria("tblVendors.VendorID", _
dbLong, Me!txtVendorIDs)

Application.BuildCriteria converts "1 OR 2 OR 3 OR 4" into:

tblVendors.VendorID=1 Or tblVendors.VendorID=2 Or
tblVendors.VendorID=3 Or tblVendors.VendorID=4

Then you can use that like this:

INSERT INTO tblChild ( VendorID, Field1, Field2, Field3 )
SELECT tblVendors.VendorID, "Value1", "Value2", "Value3"
FROM tblVendors
WHERE tblVendors.VendorID=1 Or tblVendors.VendorID=2 Or
tblVendors.VendorID=3 Or tblVendors.VendorID=4

The "Value1", "Value2" will be the data you put into your unbound
text fields on the parent form, and Field1, Field2, etc. are the
fields you want those data inserted into. "tblChild" is the table
you're inserting the data into.

Now, to display these records, you'll need to figure out the
appropriate recordsource. But maybe the above can get you started.