Code to Loop a Paste Append x times

[Guest]

Hello -

I have 2 linked tables - Table "A" is a product , and table "B" generates a
serial number (AutoNumber field) for each individual existence of that
product.

For example, I have receive 20 new items of MyProduct. I would like to be
able to generate the next 20 serial numbers for MyProduct by entering a
starter record in table "B" then running some code that creates "x"
additional records. The user would enter the "X" value in a field that the
code should refer to for the count of the number of new entries in table "B".

Basically all I need some "loop" code to be able to tell it how many times
to paste append the starter record.

Thanks
sandy

 

[Jeff Boyce]

Sandy

You've described a "how" -- how you want to accomplish something. Doing the
way you've decided will result in "X" new records.

Now how 'bout the "why" -- as in "why do you feel you need X new records in
tblB? What will having those records allow you to do?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[John W. Vinson]

For example, I have receive 20 new items of MyProduct. I would like to be
able to generate the next 20 serial numbers for MyProduct by entering a
starter record in table "B" then running some code that creates "x"
additional records. The user would enter the "X" value in a field that the
code should refer to for the count of the number of new entries in table "B".

You can do this conveniently using a handy little auxiliary table. Create a
table named Num with one Long Integer field N - fill it (manually, or using
Fill... Series in Excel and copy and paste, or in code) with values from 0
through the most items you'll ever need. Be generous, it's a tiny table even
with 10000 records.

Fill your items table using an Append query based on Num; use a criterion on N
of

< [Forms]![yourform]![txtX]

where txtX is the name of the unbound textbox in which the user specifies the
number of records desired. You can specify the productID and other fields
either from the form or from a join to the products table.

John W. Vinson [MVP]

 

[Guest]

Hello Jeff -

The Products in my table are populated circuit boards that my company builds
to test our RF Chip. Each board has a particular layout (PCB) and set of
components on it. We build 20 or 30 of each type of board and assign serial
numbers to those boards. Each board undergoes a series of tests and we report
on the test results which are tracked by serial number. The boards are
sometimes changed a bit and the best results go forward.

So when I build 20 boards, I want to generate 20 new serial numbers for them
in one step and start the tracking process. I have been working directly in
the tables but I need to get the Engineers to manage this process themselves.
Fool proof is the word of the day!

Hope that helps!

s-

 

[Guest]

Hello John -

This is perfect and simple. Thank you very much
regards
sandy

For example, I have receive 20 new items of MyProduct. I would like to be
able to generate the next 20 serial numbers for MyProduct by entering a
starter record in table "B" then running some code that creates "x"
additional records. The user would enter the "X" value in a field that the
code should refer to for the count of the number of new entries in table "B".

You can do this conveniently using a handy little auxiliary table. Create a
table named Num with one Long Integer field N - fill it (manually, or using
Fill... Series in Excel and copy and paste, or in code) with values from 0
through the most items you'll ever need. Be generous, it's a tiny table even
with 10000 records.

Fill your items table using an Append query based on Num; use a criterion on N
of

< [Forms]![yourform]![txtX]

where txtX is the name of the unbound textbox in which the user specifies the
number of records desired. You can specify the productID and other fields
either from the form or from a join to the products table.

John W. Vinson [MVP]

 

[Jeff Boyce]

Thanks for the clarification, Sandy.

It looks like John has provided a solution that works for you.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Guest]

Hi John -

I have set up the form for the data that needs to be used for the appended
records.

I have created the t_Num table with numbers to 500

My query is as follows:

INSERT INTO BoardTraveller ( BoardID, FabDate )
SELECT BoardTraveller.BoardID, BoardTraveller.FabDate
FROM BoardTraveller, t_Num
WHERE (((BoardTraveller.BoardID)=[forms]![f_LogABuild].[BoardID_DD]) AND
((BoardTraveller.FabDate)=[forms]![f_LogABuild].[FabDate_DD]) AND
((t_Num.[Num])=[forms]![f_LogABuild].[BuildNum_DD]));

The part I don't understand is how to get this query to append a specific
number of records based on the value in [forms]![f_LogABuild].[BuildNum_DD]

Currently the query results in "You are about to append 0 rows..."

Of course the t_Num is not doing any good at this point, I have just added
it to the query...

Thanks for your help!
sandy

For example, I have receive 20 new items of MyProduct. I would like to be
able to generate the next 20 serial numbers for MyProduct by entering a
starter record in table "B" then running some code that creates "x"
additional records. The user would enter the "X" value in a field that the
code should refer to for the count of the number of new entries in table "B".

You can do this conveniently using a handy little auxiliary table. Create a
table named Num with one Long Integer field N - fill it (manually, or using
Fill... Series in Excel and copy and paste, or in code) with values from 0
through the most items you'll ever need. Be generous, it's a tiny table even
with 10000 records.

Fill your items table using an Append query based on Num; use a criterion on N
of

< [Forms]![yourform]![txtX]

where txtX is the name of the unbound textbox in which the user specifies the
number of records desired. You can specify the productID and other fields
either from the form or from a join to the products table.

John W. Vinson [MVP]

 

[John W. Vinson]

The part I don't understand is how to get this query to append a specific
number of records based on the value in [forms]![f_LogABuild].[BuildNum_DD]

Currently the query results in "You are about to append 0 rows..."

Of course the t_Num is not doing any good at this point, I have just added
it to the query...

Try one tiny change: rather than an = use a <= (less than or equals) operator:

INSERT INTO BoardTraveller ( BoardID, FabDate )
SELECT BoardTraveller.BoardID, BoardTraveller.FabDate
FROM BoardTraveller, t_Num
WHERE (((BoardTraveller.BoardID)=[forms]![f_LogABuild]![BoardID_DD]) AND
((BoardTraveller.FabDate)=[forms]![f_LogABuild]![FabDate_DD]) AND
((t_Num.[Num])<=[forms]![f_LogABuild]![BuildNum_DD]));

Assuming that the textbox BuildNum_DD contains (say) 25, you should get 25
rows in the resulting query, with t_Num values from 1 to 25.

John W. Vinson [MVP]

 

[Guest]

Hmm - still no records -

How is t_num associated with the BoardTraveller table? should it be linked
to the Autonumber field or some such?

My Query does not have the AutoNumber [BrdSN] field in it as the data is
automatically generated and not appended.

I am still missing something...

thanks!
s

The part I don't understand is how to get this query to append a specific
number of records based on the value in [forms]![f_LogABuild].[BuildNum_DD]

Currently the query results in "You are about to append 0 rows..."

Of course the t_Num is not doing any good at this point, I have just added
it to the query...

Try one tiny change: rather than an = use a <= (less than or equals) operator:

INSERT INTO BoardTraveller ( BoardID, FabDate )
SELECT BoardTraveller.BoardID, BoardTraveller.FabDate
FROM BoardTraveller, t_Num
WHERE (((BoardTraveller.BoardID)=[forms]![f_LogABuild]![BoardID_DD]) AND
((BoardTraveller.FabDate)=[forms]![f_LogABuild]![FabDate_DD]) AND
((t_Num.[Num])<=[forms]![f_LogABuild]![BuildNum_DD]));

Assuming that the textbox BuildNum_DD contains (say) 25, you should get 25
rows in the resulting query, with t_Num values from 1 to 25.

John W. Vinson [MVP]

 

[John W. Vinson]

Hmm - still no records -

How is t_num associated with the BoardTraveller table? should it be linked
to the Autonumber field or some such?

It's not. It's a Cartesian Join - joining each selected record in the Source
table with *EVERY SINGLE RECORD* in t_num.

My Query does not have the AutoNumber [BrdSN] field in it as the data is
automatically generated and not appended.

The problem is that this is set up to pull a single existing record *FROM*
BoardTraveller - SELECT BoardTraveller.BoardID etc. - and generate BuildNum_DD
copies of that record. If BoardID is an Autonumber field you will get
*nothing* inserted, since it would try to create that many copies, all with
the exact same Board_ID - and since it's a primary key, you can only get one.

If you want *new* BoardID values, and you're not taking anything from any
existing record in BoardTraveller, just keep BoardTraveller as the target
table (the INSERT INTO) and only select from t_num:

INSERT INTO BoardTraveller (FabDate)
SELECT [forms]![f_LogABuild]![FabDate_DD]
FROM t_Num
WHERE ((t_Num.[Num])<=[forms]![f_LogABuild]![BuildNum_DD]));

This will create BuildNum_DD copies of a record, autonumbering the BoardID and
storing the same FabDate in each record. The sequential number N will be lost
(and I don't see where you would be putting it anyway).

John W. Vinson [MVP]

 

[Guest]

Thanks - this worked perfectly!

Hmm - still no records -

How is t_num associated with the BoardTraveller table? should it be linked
to the Autonumber field or some such?

It's not. It's a Cartesian Join - joining each selected record in the Source
table with *EVERY SINGLE RECORD* in t_num.

My Query does not have the AutoNumber [BrdSN] field in it as the data is
automatically generated and not appended.

The problem is that this is set up to pull a single existing record *FROM*
BoardTraveller - SELECT BoardTraveller.BoardID etc. - and generate BuildNum_DD
copies of that record. If BoardID is an Autonumber field you will get
*nothing* inserted, since it would try to create that many copies, all with
the exact same Board_ID - and since it's a primary key, you can only get one.

If you want *new* BoardID values, and you're not taking anything from any
existing record in BoardTraveller, just keep BoardTraveller as the target
table (the INSERT INTO) and only select from t_num:

INSERT INTO BoardTraveller (FabDate)
SELECT [forms]![f_LogABuild]![FabDate_DD]
FROM t_Num
WHERE ((t_Num.[Num])<=[forms]![f_LogABuild]![BuildNum_DD]));

This will create BuildNum_DD copies of a record, autonumbering the BoardID and
storing the same FabDate in each record. The sequential number N will be lost
(and I don't see where you would be putting it anyway).

John W. Vinson [MVP]

 

[Stretchcoder]

John,
I have the same problem, but considerably less Access skill. Could you
"dumb this down" a bit for me?

I have a table Num with one field (long int) with rows 1 to 20.
I have a table Products with 5 fields of various definitions (an autonum
primary key)
I have a form MakeProducts with no data source and 6 unbound objects - 5 for
the Products table fields and one for the Num field.
I have a button on this form "Make New Products" and I have a feeling I
should be putting the code into the Make_New_Products_Click() routine.

After this I get confused...
Is there a way to do this with an appen query in the access GUI, or do I
have to modify the VB code?

Thanks,
Emily

For example, I have receive 20 new items of MyProduct. I would like to be
able to generate the next 20 serial numbers for MyProduct by entering a
starter record in table "B" then running some code that creates "x"
additional records. The user would enter the "X" value in a field that the
code should refer to for the count of the number of new entries in table "B".

You can do this conveniently using a handy little auxiliary table. Create a
table named Num with one Long Integer field N - fill it (manually, or using
Fill... Series in Excel and copy and paste, or in code) with values from 0
through the most items you'll ever need. Be generous, it's a tiny table even
with 10000 records.

Fill your items table using an Append query based on Num; use a criterion on N
of

< [Forms]![yourform]![txtX]

where txtX is the name of the unbound textbox in which the user specifies the
number of records desired. You can specify the productID and other fields
either from the form or from a join to the products table.

John W. Vinson [MVP]

 

[John W. Vinson]

John,
I have the same problem, but considerably less Access skill. Could you
"dumb this down" a bit for me?

I have a table Num with one field (long int) with rows 1 to 20.
I have a table Products with 5 fields of various definitions (an autonum
primary key)
I have a form MakeProducts with no data source and 6 unbound objects - 5 for
the Products table fields and one for the Num field.
I have a button on this form "Make New Products" and I have a feeling I
should be putting the code into the Make_New_Products_Click() routine.

After this I get confused...
Is there a way to do this with an appen query in the access GUI, or do I
have to modify the VB code?

You won't need to use the controls on this form at all. For that matter I
don't see what function you're visualizing this form as playing!

Could you explain what you want this button to do? Are you trying to "clone"
one product into 1 or 5 or 20 identical products, or change the value of one
or more fields, or what?

 

[Stretchcoder]

John,

I am trying to "clone" one product into 1 or 5 or 20 identical products...

In a nutshell, I need a way for untrained access users to enter information
about a product lot(once) and state how many of this product is being
created. I then need to populate a table with all of the fields about this
product this many times.

When the product is created, all instances are exactly the same. Later, each
product undergoes individual changes that will affect all of the fields under
consideration. So on day one I will have N duplicate rows, but on day 35 I
will have N unique rows... (I'm pretty sure I normalized the design
correctly, I know it seems an odd thing to do in a RDB)

Any help would be great - thanks!

 

[John W. Vinson]

John,

I am trying to "clone" one product into 1 or 5 or 20 identical products...

In a nutshell, I need a way for untrained access users to enter information
about a product lot(once) and state how many of this product is being
created. I then need to populate a table with all of the fields about this
product this many times.

When the product is created, all instances are exactly the same. Later, each
product undergoes individual changes that will affect all of the fields under
consideration. So on day one I will have N duplicate rows, but on day 35 I
will have N unique rows... (I'm pretty sure I normalized the design
correctly, I know it seems an odd thing to do in a RDB)

ok... just wanted to be sure I understood! I actually have code that does just
this in my animal shelter application: when somebody brings in a litter of six
kittens they can enter the data for the first one, and type 6 in an unbound
textbox. The code makes five more copies of that animal's data.

Your code would run an Append query such as

INSERT INTO yourtable
SELECT [Forms]![Yourform]![txtThis], [Forms]![Yourform]![txtThat],
[Forms]![yourform]![txtTheOther]
FROM Num
WHERE N < [Forms]![yourform]![txtN];

This will generate as many replicates of the data shown on the form (in the
textboxes txtThis, txtThat and txtTheOther) as specified in the unbound
textbox txtN. You'll want to check for reasonable (>0, < 20 say) values in
txtN before running the query. The data textboxes can be bound to an existng
record or unbound for free entry of data, whichever works better for you.

 

[Stretchcoder]

John,

Thanks for the code snippet! Unfortunately I am still having problems...

I created a button on my form, and in the onclick event property clicked on
event procedure to get to Visual Basic. Here is how I worked the code in:

Private Sub Create_Lot_Click()

INSERT INTO [Wafers in FAB]
SELECT [Forms]![Wafer Starts]![FLN]
FROM N
WHERE N < [Forms]![Wafer Starts]![N];

End Sub

I am getting a compile error: Expected: end of statement (after I type the
INSERT INTO line). I tried naming my table something without spaces and using
that table name. It's almost like my tables aren't in scope in this method...
My forms appear in the VB properties list with no problem.

My second question is: Why am I selecting the fields from N? should't I be
selecting them from a data source for the form or something?

Thanks, I appreciate your patience with my inexperience in Access and VB!
Emily

John,

I am trying to "clone" one product into 1 or 5 or 20 identical products...

In a nutshell, I need a way for untrained access users to enter information
about a product lot(once) and state how many of this product is being
created. I then need to populate a table with all of the fields about this
product this many times.

When the product is created, all instances are exactly the same. Later, each
product undergoes individual changes that will affect all of the fields under
consideration. So on day one I will have N duplicate rows, but on day 35 I
will have N unique rows... (I'm pretty sure I normalized the design
correctly, I know it seems an odd thing to do in a RDB)

ok... just wanted to be sure I understood! I actually have code that does just
this in my animal shelter application: when somebody brings in a litter of six
kittens they can enter the data for the first one, and type 6 in an unbound
textbox. The code makes five more copies of that animal's data.

Your code would run an Append query such as

INSERT INTO yourtable
SELECT [Forms]![Yourform]![txtThis], [Forms]![Yourform]![txtThat],
[Forms]![yourform]![txtTheOther]
FROM Num
WHERE N < [Forms]![yourform]![txtN];

This will generate as many replicates of the data shown on the form (in the
textboxes txtThis, txtThat and txtTheOther) as specified in the unbound
textbox txtN. You'll want to check for reasonable (>0, < 20 say) values in
txtN before running the query. The data textboxes can be bound to an existng
record or unbound for free entry of data, whichever works better for you.

 

[John W. Vinson]

John,

Thanks for the code snippet! Unfortunately I am still having problems...

I created a button on my form, and in the onclick event property clicked on
event procedure to get to Visual Basic. Here is how I worked the code in:

Private Sub Create_Lot_Click()

INSERT INTO [Wafers in FAB]
SELECT [Forms]![Wafer Starts]![FLN]
FROM N
WHERE N < [Forms]![Wafer Starts]![N];

End Sub

I am getting a compile error: Expected: end of statement (after I type the
INSERT INTO line). I tried naming my table something without spaces and using
that table name. It's almost like my tables aren't in scope in this method...
My forms appear in the VB properties list with no problem.

The problem is that I posted SQL code to be put into a Query... and you put it
into a VBA module. SQL is one language, VBA a very different language - and
neither makes sense in the other's context!

Copy that text into the SQL window of a new query. Save it as qryClone. In
your VBA for the Click event put

Private Sub Create_Lot_Click()
Dim db As DAO.Database
Set db = CurrentDb
On Error GoTo Proc_Error

db.Execute "qryClone", dbFailOnError

Proc_Exit:
Exit Sub
Proc_Error:
Msgbox "Error " & Err.Num & " in Create_Lot_Click" & vbCrLf & Err.Description
Resume Proc_Exit
End Sub

(Added some error trapping code just in case).

My second question is: Why am I selecting the fields from N? should't I be
selecting them from a data source for the form or something?

Ummm...

What I suggested will in fact insert them from the form directly. That's what
you asked for. Your example will insert only one field - FLN; I don't know if
other fields are needed. Don't insert anything into [Wafers in FAP]'s
Autonumber field, it will increment automatically.

You're basing the append query on N purely to get N records. That's the only
role that N plays.