Duplicate a record from one table to another 2

[djfeldspiff]

I have the following piece of code behind a command button within a sub. My goal is to examine a record from the OrderProduct table and, pending the value in the Card_Qty field, duplicate the record in the CCIN table based on the OrderProduct!Card_Qty value. The problems is I'm only getting the first record from the OrderProduct table to process but not all of the data. Any help/direction would be greatly appreciated.

Thanks!

DJF



Dim rstOPQ As Recordset
Dim rst As Recordset
Dim intCounter As Integer

Set rstOPQ = CurrentDb.OpenRecordset("OrderProduct&quot
Set rst = CurrentDb.OpenRecordset("CCIN&quot

For intCounter = 1 To rstOPQ!Card_Qty
rst.AddNew
rst![CUSTNO] = rstOPQ![CUSTNO]
rst![CARDCOMP] = rstOPQ![CARDCOMP]
rst![CARDNAME] = rstOPQ![CARDNAME]
rst![TOLLFREENO] = rstOPQ![TOLLFREENO]
rst![CARDAXNO] = rstOPQ![CARDAXNO]
rst.Update
Next

 

[JerryDennison]

My question is why would you want to duplicate the exact same data in multiple records? What purpose will this serve(other than to violate the forms of data normalization)? There may be a better solution to your particular "problem". Can you describe what you're trying to do rather than what you think the solution is?

 

[Rohdem]

you need to loop through your orderproduct table:
Dim rstOPQ As Recordset
Dim rst As Recordset
Dim intCounter As Integer

Set rstOPQ = CurrentDb.OpenRecordset("OrderProduct&quot
Set rst = CurrentDb.OpenRecordset("CCIN&quot

Do until rst.eof
For intCounter = 1 To rstOPQ!Card_Qty
rst.AddNew
rst![CUSTNO] = rstOPQ![CUSTNO]
rst![CARDCOMP] = rstOPQ![CARDCOMP]
rst![CARDNAME] = rstOPQ![CARDNAME]
rst![TOLLFREENO] = rstOPQ![TOLLFREENO]
rst![CARDAXNO] = rstOPQ![CARDAXNO]
rst.Update
Next
rstOPQ.movenext
Loop

Mike Rohde
rohdem@marshallengines.com

 

[djfeldspiff]

Hi Jerry,

Thanks for your reply! I'm trying to take the data and eventually export to a text file used for printing. We print calling cards and if a customer requests two (or more) of the same card, I need to have two (or more) rows of data for the same person in order to print from our datacard machine. My thinking was to create a tmp table of the data needed for printing and then once the data set was created, export from the tmp table to a text file. As we process each file we receive from our client, we would clear the tmp table and the process would start over.

Should you have another option, I'd be more than happy to hear your feedback.

Thanks!

DJF

 

[JerryDennison]

Having 30 years of printing behind me I just may be able to help integrate the two(Access and printing).

If you are ganging more than one customer's business card for plating(i.e. running several customers at the same time with varying numbers of their cards "up" on the plate, or different people at the same company, etc.) AND you have more than one card per individual you could use the temporary table method for defining the output file parameters. Are you including as part of the record the number up on the print sheet? But a better method might be to have a table for the output(plating setup as it were) that has fields for each plating position. These fields would be nothing more than foreign key links to the customer information. You could then place a customer in one or all of the positions available or mix and match any way you wish WITHOUT creating a temporary table OR actually duplicating any information.