Multiple record creation

  • Thread starter Thread starter RD Wirr
  • Start date Start date
R

RD Wirr

I need the help of you wizards out there. I am trying to
create an application in Access that tracks production
materials. When we receive a load of semi-finished
products in our place of business, we assign each piece a
serial number and enter other information about the
product in a database such as supplierID, date, grade,
etc. My problem is, creating a new record and filling the
data for each product for quantities of the same product
is a lot of work. My question is this: is there some
method of creating multiple records and filling the
fields with a single routine. I am thinking to have a
data entry form that allows the user to enter (or select
from combo boxes) all the data listed above, enter the
quantity of pieces and then have the program create the
number of new records specified and fill them with the
data from that form, assigning a unique (autonumber)
serial number to each new record.
Can anyone help me with this?
Thanks in advance,
RDW
 
RD,

Make a new table, with just one field, number data type, and enter
numbers in this field from 1 to however many will be the most number of
items you will ever conceivably have in a multiple delivery.

Make a query based on your table, and add all the fields to the query
design grid that you will want to have relicated for the multiple
records. Add also the autonumber ID field.

Add the new number lookup table to the query. Do not join it to the
other table. Put the single number field also into the query design grid.

Make this into an Append Query (select Append from the Query menu),
nominate the name of your main table when prompted for the table to
append to. Make sure the name of the field is in the Append To row of
the grid for all applicable fields *except* the ID field.

In the criteria of the ID field, put [Forms]![NameOfYourForm]![ID] and
in the criteria of the number field from the number lookup table, put...
<[Forms]![NameOfYourForm]![Quantity]

Save and name this query.

The NameOfYourForm above refers to the form bound to your main table
where you can enter the record for the first item in the load. On this
form also, possible in the form header section would be applicable, put
an unbound textbox, name it Quantity (unless you have a field in the
table called Quantity, in which case name it something else!) where you
will enter the number of items for which you require separate records.

Put a command button on this form, and on this command button assign a
macro using the OpenQuery action to run the Append Query.

Test on a backup copy of your database!
 
Hi Steve,

Thanks very much for outlining this procedure. I have
built the table form and query you suggested but I think
I'm doing something wrong. When I run the query, it only
replicates and appends one copy (one record) of the top
record in the main table regardless of what I fill the
fields on the data entry form with. If I go to the end of
the ProdData table displayed in the ProdDataEntry, enter
new data and run the query, it appends 0 new records.

Here is the SQL version of the append query:
INSERT INTO ProdData ( MoNum, SupplierID, DateRec,
GradeIn, Inps1, Accpt1 )
SELECT ProdData.MoNum, ProdData.SupplierID,
ProdData.DateRec, ProdData.GradeIn, ProdData.Inps1,
ProdData.Accpt1
FROM LookupQty, ProdData
WHERE (((ProdData.PieceID)=[Forms]![ProdDataEntry]!
[PieceID]) AND ((LookupQty.Qty)=[Forms]![ProdDataEntry]!
[QuantityRec]));

Where ProdData is the main table, PieceID is the
autonumber ID field and QuantityRec is the unbound text
box Quantity of new records on the data entry form. The
new table with the lookup quantities is LookupQty.

I should also mention that the Data Entry form,
[ProdDataEntry] displays the current data in the ProdData
table which in a way it not bad however, it could easily
allow careless users to modify exiting data while
selecting the data to fill the new records with. Is there
a good way to get around this?

Thanks again.
RDW
-----Original Message-----
RD,

Make a new table, with just one field, number data type, and enter
numbers in this field from 1 to however many will be the most number of
items you will ever conceivably have in a multiple delivery.

Make a query based on your table, and add all the fields to the query
design grid that you will want to have relicated for the multiple
records. Add also the autonumber ID field.

Add the new number lookup table to the query. Do not join it to the
other table. Put the single number field also into the query design grid.

Make this into an Append Query (select Append from the Query menu),
nominate the name of your main table when prompted for the table to
append to. Make sure the name of the field is in the Append To row of
the grid for all applicable fields *except* the ID field.

In the criteria of the ID field, put [Forms]! [NameOfYourForm]![ID] and
in the criteria of the number field from the number lookup table, put...
<[Forms]![NameOfYourForm]![Quantity]

Save and name this query.

The NameOfYourForm above refers to the form bound to your main table
where you can enter the record for the first item in the load. On this
form also, possible in the form header section would be applicable, put
an unbound textbox, name it Quantity (unless you have a field in the
table called Quantity, in which case name it something else!) where you
will enter the number of items for which you require separate records.

Put a command button on this form, and on this command button assign a
macro using the OpenQuery action to run the Append Query.

Test on a backup copy of your database!

--
Steve Schapel, Microsoft Access MVP


RD said:
I need the help of you wizards out there. I am trying to
create an application in Access that tracks production
materials. When we receive a load of semi-finished
products in our place of business, we assign each piece a
serial number and enter other information about the
product in a database such as supplierID, date, grade,
etc. My problem is, creating a new record and filling the
data for each product for quantities of the same product
is a lot of work. My question is this: is there some
method of creating multiple records and filling the
fields with a single routine. I am thinking to have a
data entry form that allows the user to enter (or select
from combo boxes) all the data listed above, enter the
quantity of pieces and then have the program create the
number of new records specified and fill them with the
data from that form, assigning a unique (autonumber)
serial number to each new record.
Can anyone help me with this?
Thanks in advance,
RDW
Click to expand...
.
Click to expand...
 
RDW,

You have used an = instead of a < in the WHERE clause for Qty. SHould
be ... AND ((LookupQty.Qty)<[Forms]![ProdDataEntry]![QuantityRec]...

As regards the danger of accidental changes to the data, there are a few
possibilities to condider. Obviously the users will need to be able to
edit data if necessary. You could, for example, have an Option Group in
the form header, containing two toggle buttons labelled Edit Mode and
Add Mode. On the AfterUpdate event of this Option Group, you could have
a macro or vba procedure to adjust the properties of the form and the
command button. In a macro, it would use the SetValue action, e.g.

Condition: [NameOfOptionGroup]=1
Action: SetValue
Item: [Forms]![ProdDataEntry].[AllowEdits]
Expression: Yes

Condition: ...
Action: SetValue
Item: [Forms]![ProdDataEntry].[AllowAdditions]
Expression: No

Condition: ...
Action: SetValue
Item: [Forms]![ProdDataEntry]![CommandButton].[Enabled]
Expression: Yes

Condition: [NameOfOptionGroup]=2
Action: SetValue
Item: [Forms]![ProdDataEntry].[AllowEdits]
Expression: No

Condition: ...
Action: SetValue
Item: [Forms]![ProdDataEntry].[AllowAdditions]
Expression: Yes

Condition: ...
Action: SetValue
Item: [Forms]![ProdDataEntry]![CommandButton].[Enabled]
Expression: No

(CommandButton being the name of the command button that you are using
to run the Append Query for the entry of multiple records)

Hope that makes sense.

--
Steve Schapel, Microsoft Access MVP


RD said:
Hi Steve,

Thanks very much for outlining this procedure. I have
built the table form and query you suggested but I think
I'm doing something wrong. When I run the query, it only
replicates and appends one copy (one record) of the top
record in the main table regardless of what I fill the
fields on the data entry form with. If I go to the end of
the ProdData table displayed in the ProdDataEntry, enter
new data and run the query, it appends 0 new records.

Here is the SQL version of the append query:
INSERT INTO ProdData ( MoNum, SupplierID, DateRec,
GradeIn, Inps1, Accpt1 )
SELECT ProdData.MoNum, ProdData.SupplierID,
ProdData.DateRec, ProdData.GradeIn, ProdData.Inps1,
ProdData.Accpt1
FROM LookupQty, ProdData
WHERE (((ProdData.PieceID)=[Forms]![ProdDataEntry]!
[PieceID]) AND ((LookupQty.Qty)=[Forms]![ProdDataEntry]!
[QuantityRec]));

Where ProdData is the main table, PieceID is the
autonumber ID field and QuantityRec is the unbound text
box Quantity of new records on the data entry form. The
new table with the lookup quantities is LookupQty.

I should also mention that the Data Entry form,
[ProdDataEntry] displays the current data in the ProdData
table which in a way it not bad however, it could easily
allow careless users to modify exiting data while
selecting the data to fill the new records with. Is there
a good way to get around this?

Thanks again.
RDW
Click to expand...
 
Hi Steve,

I did as you suggested and it worked for the append
query. Thanks.
I created the macro you suggested for going into the edit
or add modes but didn't really get the results I'm
looking for. I wonder if you could bear with me for one
more cycle and help me expand on the append query a bit
more. What I'd like to do is have a form that only is
used for data entry and use a different form for editing.
I am thinking of making the data entry form one that is
not based on the ProdData table and has data entry fields
as unbound text boxes and unbound combo boxes with the
row source still coming from the data in the ProdData
table or the tables joined to ProdData that provide the
data for the comboboxes. The question then is how to make
an append query that creates the multiple records and
fills the data into those records from the data in the
unbound controls on the AddRecords form? I built the form
and tried tweaking the append query you gave me by
putting expressions referencing the form controls but I
could get it to work. Is this something that's possible
or do I need to take a different approach?

Thanks again,
RDW
-----Original Message-----
RDW,

You have used an = instead of a < in the WHERE clause for Qty. SHould
be ... AND ((LookupQty.Qty)<[Forms]![ProdDataEntry]! [QuantityRec]...

As regards the danger of accidental changes to the data, there are a few
possibilities to condider. Obviously the users will need to be able to
edit data if necessary. You could, for example, have an Option Group in
the form header, containing two toggle buttons labelled Edit Mode and
Add Mode. On the AfterUpdate event of this Option Group, you could have
a macro or vba procedure to adjust the properties of the form and the
command button. In a macro, it would use the SetValue action, e.g.

Condition: [NameOfOptionGroup]=1
Action: SetValue
Item: [Forms]![ProdDataEntry].[AllowEdits]
Expression: Yes

Condition: ...
Action: SetValue
Item: [Forms]![ProdDataEntry].[AllowAdditions]
Expression: No

Condition: ...
Action: SetValue
Item: [Forms]![ProdDataEntry]![CommandButton].[Enabled]
Expression: Yes

Condition: [NameOfOptionGroup]=2
Action: SetValue
Item: [Forms]![ProdDataEntry].[AllowEdits]
Expression: No

Condition: ...
Action: SetValue
Item: [Forms]![ProdDataEntry].[AllowAdditions]
Expression: Yes

Condition: ...
Action: SetValue
Item: [Forms]![ProdDataEntry]![CommandButton].[Enabled]
Expression: No

(CommandButton being the name of the command button that you are using
to run the Append Query for the entry of multiple records)

Hope that makes sense.

--
Steve Schapel, Microsoft Access MVP


RD said:
Hi Steve,

Thanks very much for outlining this procedure. I have
built the table form and query you suggested but I think
I'm doing something wrong. When I run the query, it only
replicates and appends one copy (one record) of the top
record in the main table regardless of what I fill the
fields on the data entry form with. If I go to the end of
the ProdData table displayed in the ProdDataEntry, enter
new data and run the query, it appends 0 new records.

Here is the SQL version of the append query:
INSERT INTO ProdData ( MoNum, SupplierID, DateRec,
GradeIn, Inps1, Accpt1 )
SELECT ProdData.MoNum, ProdData.SupplierID,
ProdData.DateRec, ProdData.GradeIn, ProdData.Inps1,
ProdData.Accpt1
FROM LookupQty, ProdData
WHERE (((ProdData.PieceID)=[Forms]![ProdDataEntry]!
[PieceID]) AND ((LookupQty.Qty)=[Forms]! [ProdDataEntry]!
[QuantityRec]));

Where ProdData is the main table, PieceID is the
autonumber ID field and QuantityRec is the unbound text
box Quantity of new records on the data entry form. The
new table with the lookup quantities is LookupQty.

I should also mention that the Data Entry form,
[ProdDataEntry] displays the current data in the ProdData
table which in a way it not bad however, it could easily
allow careless users to modify exiting data while
selecting the data to fill the new records with. Is there
a good way to get around this?

Thanks again.
RDW
Click to expand...
.
Click to expand...
 
RD,

I am not sure why you would prefer this approach over the way we have
been discussing. But anyway, the answer is Yes, you should be able to
use the values in the unbound form in an Append Query. You would put
the references to the form controls into the Field row of the qyuery
design grid... is this what you were trying?
 
Hi Steve,

Sorry, I should have given you the SQL statement I ended
up with last time. Here is the SQL result of entering
references to the new data entry form [ProdDataEntry1],
in the Query design grid:

INSERT INTO ProdData ( MoNum, SupplierID, DateRec,
GradeIn, Inps1, Dry1, Accpt1, Comment1 )
SELECT [Forms]![ProdDataEntry1]![MoNum] AS Expr1, Forms]!
[ProdDataEntry1]![SupplierID] AS Expr2, [Forms]!
[ProdDataEntry1]![DateRec] AS Expr3, [Forms]!
[ProdDataEntry1]![GradeIn] AS Expr4, [Forms]!
[ProdDataEntry1]![Inps1] AS Expr5, [Forms]!
[ProdDataEntry1]![Dry1] AS Expr6, [Forms]!
[ProdDataEntry1]![Accpt1] AS Expr7, [Forms]!
[ProdDataEntry1]![Comment1] AS Expr8
FROM LookupQty, ProdData
WHERE (((ProdData.PieceID)=[Forms]![ProdDataEntry1]!
[PieceID]) AND ((LookupQty.Qty)<([Forms]![ProdDataEntry1]!
[QuantityRec])));

As you have seen, I am no expert but I think my problem
is that I cannot find the correct reference to the
[ProdDataEntry1] form in the FROM statement but here
again I defer to your expertise.

FYI, the reason I want to have separate data entry and
edit forms is because the initial data entry is a quick
and dirty (read: simple) mass record creation task that
enters some initial information and then assigns a range
of new serial numbers (with their associated records) for
which we print serial number tags (a project I have yet
to get into) for the materials we have received. The
editing function is done by other groups that enter much
more production information into the records from several
task specific forms. The record editors are not allowed
to add or delete records and the record creators are not
allowed to modify production data. Another reason is
because the method we have been discussing seems to have
a limitation. The macro you described to set the Mode of
the Form to allow either edits or additions, when we
disallow edits, it also disables the button to add the
series of new records. Maybe I've got something wrong in
the macro but even so, the user separation requirement is
the thing that moves me toward the task specific, unbound
form. For sure I'm open to other suggestions on how to
accomplish this task. If I could limit the record
creator's editing to only the records they have newly
created, for example lock the record to that group after
someone else modifies the record, it would be great. If
you have other ideas, I'm all eyes...

Thanks again,
RDW
 
RDW,

Now that you are using the values of unbound controls for your multiple
record data entry, instead of the values in an existing record in
ProdData table, you no longer need the ProdData table in the FROM
clause. Try it like this...
FROM LookupQty
WHERE [Qty]<[Forms]![ProdDataEntry1]![QuantityRec]

--
Steve Schapel, Microsoft Access MVP


Hi Steve,

Sorry, I should have given you the SQL statement I ended
up with last time. Here is the SQL result of entering
references to the new data entry form [ProdDataEntry1],
in the Query design grid:

INSERT INTO ProdData ( MoNum, SupplierID, DateRec,
GradeIn, Inps1, Dry1, Accpt1, Comment1 )
SELECT [Forms]![ProdDataEntry1]![MoNum] AS Expr1, Forms]!
[ProdDataEntry1]![SupplierID] AS Expr2, [Forms]!
[ProdDataEntry1]![DateRec] AS Expr3, [Forms]!
[ProdDataEntry1]![GradeIn] AS Expr4, [Forms]!
[ProdDataEntry1]![Inps1] AS Expr5, [Forms]!
[ProdDataEntry1]![Dry1] AS Expr6, [Forms]!
[ProdDataEntry1]![Accpt1] AS Expr7, [Forms]!
[ProdDataEntry1]![Comment1] AS Expr8
FROM LookupQty, ProdData
WHERE (((ProdData.PieceID)=[Forms]![ProdDataEntry1]!
[PieceID]) AND ((LookupQty.Qty)<([Forms]![ProdDataEntry1]!
[QuantityRec])));

As you have seen, I am no expert but I think my problem
is that I cannot find the correct reference to the
[ProdDataEntry1] form in the FROM statement but here
again I defer to your expertise.

FYI, the reason I want to have separate data entry and
edit forms is because the initial data entry is a quick
and dirty (read: simple) mass record creation task that
enters some initial information and then assigns a range
of new serial numbers (with their associated records) for
which we print serial number tags (a project I have yet
to get into) for the materials we have received. The
editing function is done by other groups that enter much
more production information into the records from several
task specific forms. The record editors are not allowed
to add or delete records and the record creators are not
allowed to modify production data. Another reason is
because the method we have been discussing seems to have
a limitation. The macro you described to set the Mode of
the Form to allow either edits or additions, when we
disallow edits, it also disables the button to add the
series of new records. Maybe I've got something wrong in
the macro but even so, the user separation requirement is
the thing that moves me toward the task specific, unbound
form. For sure I'm open to other suggestions on how to
accomplish this task. If I could limit the record
creator's editing to only the records they have newly
created, for example lock the record to that group after
someone else modifies the record, it would be great. If
you have other ideas, I'm all eyes...

Thanks again,
RDW
Click to expand...
 
Hi Steve,

That worked great. Thanks very much for sticking with me
on this. I really appreciate it.

By the way, can you tell me the reason this query always
creates one less record than the Qty I entered in the qty
control? I fixed it with a calculation adding 1 to the
number of the qty control. Nothing urgent, but maybe I
can learn something by knowing why this would happen.

Thanks n regards,
RDW
-----Original Message-----
RDW,

Now that you are using the values of unbound controls for your multiple
record data entry, instead of the values in an existing record in
ProdData table, you no longer need the ProdData table in the FROM
clause. Try it like this...
FROM LookupQty
WHERE [Qty]<[Forms]![ProdDataEntry1]![QuantityRec]

--
Steve Schapel, Microsoft Access MVP


[email protected] said:
Hi Steve,

Sorry, I should have given you the SQL statement I ended
up with last time. Here is the SQL result of entering
references to the new data entry form [ProdDataEntry1],
in the Query design grid:

INSERT INTO ProdData ( MoNum, SupplierID, DateRec,
GradeIn, Inps1, Dry1, Accpt1, Comment1 )
SELECT [Forms]![ProdDataEntry1]![MoNum] AS Expr1, Forms]!
[ProdDataEntry1]![SupplierID] AS Expr2, [Forms]!
[ProdDataEntry1]![DateRec] AS Expr3, [Forms]!
[ProdDataEntry1]![GradeIn] AS Expr4, [Forms]!
[ProdDataEntry1]![Inps1] AS Expr5, [Forms]!
[ProdDataEntry1]![Dry1] AS Expr6, [Forms]!
[ProdDataEntry1]![Accpt1] AS Expr7, [Forms]!
[ProdDataEntry1]![Comment1] AS Expr8
FROM LookupQty, ProdData
WHERE (((ProdData.PieceID)=[Forms]![ProdDataEntry1]!
[PieceID]) AND ((LookupQty.Qty)<([Forms]! [ProdDataEntry1]!
[QuantityRec])));

As you have seen, I am no expert but I think my problem
is that I cannot find the correct reference to the
[ProdDataEntry1] form in the FROM statement but here
again I defer to your expertise.

FYI, the reason I want to have separate data entry and
edit forms is because the initial data entry is a quick
and dirty (read: simple) mass record creation task that
enters some initial information and then assigns a range
of new serial numbers (with their associated records) for
which we print serial number tags (a project I have yet
to get into) for the materials we have received. The
editing function is done by other groups that enter much
more production information into the records from several
task specific forms. The record editors are not allowed
to add or delete records and the record creators are not
allowed to modify production data. Another reason is
because the method we have been discussing seems to have
a limitation. The macro you described to set the Mode of
the Form to allow either edits or additions, when we
disallow edits, it also disables the button to add the
series of new records. Maybe I've got something wrong in
the macro but even so, the user separation requirement is
the thing that moves me toward the task specific, unbound
form. For sure I'm open to other suggestions on how to
accomplish this task. If I could limit the record
creator's editing to only the records they have newly
created, for example lock the record to that group after
someone else modifies the record, it would be great. If
you have other ideas, I'm all eyes...

Thanks again,
RDW
Click to expand...
.
Click to expand...
 
RDW,

Pleased to know that we have made progress.

The reason we get one less record than the number entered is because we
told it to :-) This is in this section of the SQL...
WHERE [Qty]<[Forms]![ProdDataEntry1]![QuantityRec]
To make it do the same number, we would use...
WHERE [Qty]<=[Forms]![ProdDataEntry1]![QuantityRec]
When we first drew this up, we *wanted* one less. This was because you
had already entered one record, and then adding copies of the added
record. In this case, you would end up with the right amount in the
end. Now that you are basing the multiple entry on unbound data, the
query has to be adjusted accordingly. Make sense?
 
Ah-ha! Great, That is much nicer than adding a +1 to the
end.
You've helped me a lot Steve. Thanks very much.
-----Original Message-----
RDW,

Pleased to know that we have made progress.

The reason we get one less record than the number entered is because we
told it to :-) This is in this section of the SQL...
WHERE [Qty]<[Forms]![ProdDataEntry1]![QuantityRec]
To make it do the same number, we would use...
WHERE [Qty]<=[Forms]![ProdDataEntry1]![QuantityRec]
When we first drew this up, we *wanted* one less. This was because you
had already entered one record, and then adding copies of the added
record. In this case, you would end up with the right amount in the
end. Now that you are basing the multiple entry on unbound data, the
query has to be adjusted accordingly. Make sense?

--
Steve Schapel, Microsoft Access MVP


RD said:
Hi Steve,

That worked great. Thanks very much for sticking with me
on this. I really appreciate it.

By the way, can you tell me the reason this query always
creates one less record than the Qty I entered in the qty
control? I fixed it with a calculation adding 1 to the
number of the qty control. Nothing urgent, but maybe I
can learn something by knowing why this would happen.

Thanks n regards,
RDW
Click to expand...
.
Click to expand...
 
Back
Top