How to duplicate (!) selected records

  • Thread starter Thread starter GaryS
  • Start date Start date
G

GaryS

[Flak Jacket ON]

OK, I'm NOT REALLY denormalizing my database . . . hear me
out, please!:

I'm using a special-purpose label printer that's driven by
software that reads a table in .mdb format (call it
tLabel); the software prints one label per record. That's
the gist of the situation.

I'm using a MakeTable query to select a subset records out
of tA (which represents individual physical plants in a
garden, BTW), each of which will result in one row in
tLabel.

Thanks to you MVP's, I've gotten the formatting and
generation of the fields in tLabel down pat.

But I can see the requirement to produce more than one
label copy for a given plant (don't ask why!). From the
labeling end, the easiest way would be just to have
*identical* rows in tLabel, adjacent so the copies of the
labels would come out of the printer together.

That means I have to devise a way to make copies of a row
somewhere along the line. Right now, I display a
datasheet view of tLabel just so the user can make any
final edits. I'm thinking, at that point (which is the
only point of manual intervention in the Access-driven
part of the process), they could type a value in a
tLabel.Quantity field if they want more than 1 label for
that plant.

OK, so, when they finish reviewing tLabel, I would need
some postprocessing (Close event of the datasheet?) to
insert the copies of the records.

That's my analysis of the problem.

I see two obstacles:

1. I don't know a slick way to make copies of a row.

2. Right now, tLabel contains tA.id, the Autonumber ID
for tA, which gives traceability back to tA records. I
noticed that in tLabel tA.id appears as an Autonumber
also. So the MakeTable query carried over that Autonumber
attribute from tA to tLabel, I guess. Now, if I'm making
copies of the same record, I'm going to also get
duplications of tLabel.tA.id values, which will trigger an
error.

In writing this, I'm thinking maybe the first
postprocessing step would be to change the type attribute
of tLabel.tA.id from Autonumber to Number.

If so, then the logic would be something like this (I need
help with syntax, too):

Private Function fncGenCopies_tLabel_Close()

tLabel.Fields.Aid.Type = "Number" 'or something
like that
 
Gary,

Fair enough.

Don't use a Make-Table query. Set up the table first, empty, with the
ID field as a Number data type. Use an Append Query to put your
temporary label data in there, and run it twice. Feed to the label
printer from a query that sorts the data, so you get the identical
labels together.

- Steve Schapel, Microsoft Access MVP


[Flak Jacket ON]

OK, I'm NOT REALLY denormalizing my database . . . hear me
out, please!:

I'm using a special-purpose label printer that's driven by
software that reads a table in .mdb format (call it
tLabel); the software prints one label per record. That's
the gist of the situation.

I'm using a MakeTable query to select a subset records out
of tA (which represents individual physical plants in a
garden, BTW), each of which will result in one row in
tLabel.

Thanks to you MVP's, I've gotten the formatting and
generation of the fields in tLabel down pat.

But I can see the requirement to produce more than one
label copy for a given plant (don't ask why!). From the
labeling end, the easiest way would be just to have
*identical* rows in tLabel, adjacent so the copies of the
labels would come out of the printer together.

That means I have to devise a way to make copies of a row
somewhere along the line. Right now, I display a
datasheet view of tLabel just so the user can make any
final edits. I'm thinking, at that point (which is the
only point of manual intervention in the Access-driven
part of the process), they could type a value in a
tLabel.Quantity field if they want more than 1 label for
that plant.

OK, so, when they finish reviewing tLabel, I would need
some postprocessing (Close event of the datasheet?) to
insert the copies of the records.

That's my analysis of the problem.

I see two obstacles:

1. I don't know a slick way to make copies of a row.

2. Right now, tLabel contains tA.id, the Autonumber ID
for tA, which gives traceability back to tA records. I
noticed that in tLabel tA.id appears as an Autonumber
also. So the MakeTable query carried over that Autonumber
attribute from tA to tLabel, I guess. Now, if I'm making
copies of the same record, I'm going to also get
duplications of tLabel.tA.id values, which will trigger an
error.

In writing this, I'm thinking maybe the first
postprocessing step would be to change the type attribute
of tLabel.tA.id from Autonumber to Number.

If so, then the logic would be something like this (I need
help with syntax, too):

Private Function fncGenCopies_tLabel_Close()

tLabel.Fields.Aid.Type = "Number" 'or something
like that
.
.
For each record in tLabel:
numCopies = nz(.Quantity)
.Quantity = 1 ' in original
if numCopies > 1 then *make numCopies-1 of
current record with .Quantity of each set to 1*
EndFor

*sort if needed to get copies together with originals*
Exit
End Function

Lots of verbiage, but hopefully it will make it easier to
answer the 2 questions!

TIA

Gary
Click to expand...
 
OK, that solves the Autonumber problem I figured I was headed for.
Thanks.

But if I run the query twice, I'll get 2 copies of each label. That's
not what I need, though. I need x copies of each label, where the
default is 1 and the number required, if different, is given by
tLabel.Quantity. It could be any number, in theory.

Gary

Steve Schapel said:
Gary,

Fair enough.

Don't use a Make-Table query. Set up the table first, empty, with the
ID field as a Number data type. Use an Append Query to put your
temporary label data in there, and run it twice. Feed to the label
printer from a query that sorts the data, so you get the identical
labels together.

- Steve Schapel, Microsoft Access MVP


[Flak Jacket ON]

OK, I'm NOT REALLY denormalizing my database . . . hear me
out, please!:

I'm using a special-purpose label printer that's driven by
software that reads a table in .mdb format (call it
tLabel); the software prints one label per record. That's
the gist of the situation.

I'm using a MakeTable query to select a subset records out
of tA (which represents individual physical plants in a
garden, BTW), each of which will result in one row in
tLabel.

Thanks to you MVP's, I've gotten the formatting and
generation of the fields in tLabel down pat.

But I can see the requirement to produce more than one
label copy for a given plant (don't ask why!). From the
labeling end, the easiest way would be just to have
*identical* rows in tLabel, adjacent so the copies of the
labels would come out of the printer together.

That means I have to devise a way to make copies of a row
somewhere along the line. Right now, I display a
datasheet view of tLabel just so the user can make any
final edits. I'm thinking, at that point (which is the
only point of manual intervention in the Access-driven
part of the process), they could type a value in a
tLabel.Quantity field if they want more than 1 label for
that plant.

OK, so, when they finish reviewing tLabel, I would need
some postprocessing (Close event of the datasheet?) to
insert the copies of the records.

That's my analysis of the problem.

I see two obstacles:

1. I don't know a slick way to make copies of a row.

2. Right now, tLabel contains tA.id, the Autonumber ID
for tA, which gives traceability back to tA records. I
noticed that in tLabel tA.id appears as an Autonumber
also. So the MakeTable query carried over that Autonumber
attribute from tA to tLabel, I guess. Now, if I'm making
copies of the same record, I'm going to also get
duplications of tLabel.tA.id values, which will trigger an
error.

In writing this, I'm thinking maybe the first
postprocessing step would be to change the type attribute
of tLabel.tA.id from Autonumber to Number.

If so, then the logic would be something like this (I need
help with syntax, too):

Private Function fncGenCopies_tLabel_Close()

tLabel.Fields.Aid.Type = "Number" 'or something
like that
.
.
For each record in tLabel:
numCopies = nz(.Quantity)
.Quantity = 1 ' in original
if numCopies > 1 then *make numCopies-1 of
current record with .Quantity of each set to 1*
EndFor

*sort if needed to get copies together with originals*
Exit
End Function

Lots of verbiage, but hopefully it will make it easier to
answer the 2 questions!

TIA

Gary
Click to expand...
Click to expand...
 
Apologies, Gary. Looks like I didn't read your original carefully
enough... for some reason I got the idea you wanted two of each.

I am sure there is a fancier way of doing it. But to follow the model
we have started...

Private Sub YourButton_Click()
Dim i As Integer
DoCmd.SetWarnings False
For i = 1 to Me.Quantity
DoCmd.OpenQuery "YourAppend"
Next i
DoCmd.SetWarnings True
DoCmd.OpenReport "YourLabels"
End Sub

- Steve Schapel, Microsoft Access MVP
 
Thanks, Steve, the code makes sense to me.

re: Apologies
Well, "duplicate" does mean "double", I guess; wasn't sure what verb
to use for clarity: "Copy" wasn't right either, since not copying
records from A to B.

Oh, well, I think I can bring it all together now!

Gary
 
Back
Top