Duplicating two related records in two tables

[breezett93]

Hello,


I have a purchase order form that uses two tables: a PO table and a detailed PO table.

I tried the duplicating macro that Access already has built in, but all it really did was just create a new blank record. The macro did not copy any of the additional data in the record.

So, I am looking to have a button that will copy the current PO record in both tables and create a new PO record in both tables.

 

[dhookom]

Can you please provide the actual table and field names as well as the relationship?

Duane
Vevey, Switzerland
Hook'D on Access
MS Access MVP 2001-2016

 

[breezett93]

Absolutely.

-POTbl

POId, VendId, PODt, Trms, ShpVia, Attn, Spec, FrtTrms, Notes, ShpTo, Issued


-PODetTbl

POId, POItm, Qty, ItemId, Unit, Desc, Cost, FrtCost, For, DueDt, RcvdDt, Recvr, Spec, Complete, FSC, Delivered, DelNotes

Relationships: 1 to Many from POTbl to PODetTbl

 

[Andrzejek]

I assume POId field in POTbl is a PK, like an AutoNumber?

Try:[tt]
INSERT INTO POTbl (VendId, PODt, Trms, ShpVia, Attn, Spec, FrtTrms, Notes, ShpTo, Issued)
SELECT VendId, PODt, Trms, ShpVia, Attn, Spec, FrtTrms, Notes, ShpTo, Issued
FROM POTbl
WHERE POId = [/tt] Whatever record ID you want to copy

Have fun.

---- Andy

There is a great need for a sarcasm font.

 

[breezett93]

For setting the POId at the where clause, that value is determined by which PO is being viewed in the form.


Do I use something like:

WHERE POId = Me![POId]

 

[Andrzejek]

One way to find out - give it a try and see.

Have fun.

---- Andy

There is a great need for a sarcasm font.

 

[breezett93]

Looks like the POTbl query worked. A new record was added and everything was copied correctly.

So I did the same thing with the PODetTbl query. However, the sixth column in that table is labeled "Desc" for description. I'm getting a syntax error; probably because desc is a reserved keyword?

Without having to go into the data file to rename the table, what are my options? Would an alias work here?

 

[dhookom]

You should be able to wrap the field name in []s.

[Desc]

Duane
Vevey, Switzerland
Hook'D on Access
MS Access MVP 2001-2016

 

[Andrzejek]

Then you will (probably) find that another field: [tt]For[/tt] also creates the same issue. So maybe it would be a good idea to rename the fields the way they should be named from the beginning: no reserved words, no spaces, etc...

Have fun.

---- Andy

There is a great need for a sarcasm font.

 

[breezett93]

Adding in the []s did allow me to run the command. I am getting a key violation for the PO detailed table. I think it's because the detailed table can have more than one record with the same POId (multiple items make up one PO).


This is currently the second query that is running:

INSERT INTO PODetTbl ( POItm, Qty, ItemId, Unit, [Desc], Cost, FrtCost, [For], DueDt, RcvdDt, Recvr, Spec, Complete, FSC, Delivered, DelNotes )
SELECT POItm, Qty, ItemId, Unit, Desc, Cost, FrtCost, For, DueDt, RcvdDt, Recvr, Spec, Complete, FSC, Delivered, DelNotes
FROM PODetTbl
WHERE POId = Forms![POFrm01]![POId];


How can I rewrite it to eliminate the key violation and correctly copy all line items?

 

[Andrzejek]

If this is your [tt]PODetTbl[/tt] table:
[tt]
POId, POItm, Qty, ItemId, Unit, Desc, Cost, FrtCost, For, DueDt, RcvdDt, Recvr, Spec, Complete, FSC, Delivered, DelNotes
[/tt]
I assume field [tt]POId[/tt] is the foreign key to [tt]POTbl.POId[/tt] PK?
So what id the PK in [tt]PODetTbl[/tt] table?

Have fun.

---- Andy

There is a great need for a sarcasm font.

 

[breezett93]

You are correct; POId is a foreign key from POTbl.
POItm is the PK in PODetTbl.

Both are Number Data Type, and both are not indexed.

 

[Andrzejek]

If POItm is the PK in PODetTbl, it is an AutoNumber, I assume, and should be inserted / incremented auto-'magically'. And should NOT be mentioned in the INSERT statement.

Consider this:
[tt]
INSERT INTO PODetTbl ( [blue]POId[/blue], Qty, ItemId, Unit, [Desc], Cost, FrtCost, [For], DueDt, RcvdDt, Recvr, Spec, Complete, FSC, Delivered, DelNotes )
SELECT [blue](Select MAX(POId) FROM POTbl)[/blue], Qty, ItemId, Unit, Desc, Cost, FrtCost, For, DueDt, RcvdDt, Recvr, Spec, Complete, FSC, Delivered, DelNotes
FROM PODetTbl
WHERE POId = Forms![POFrm01]![POId];
[/tt]


Have fun.

---- Andy

There is a great need for a sarcasm font.

 

[breezett93]

POItm does not appear to be an AutoNumber. It has a Number Data Type.

The most recent fifteen records have a POItm number of:
1, 1, 1, 1, 1, 1, 1, 5, 6, 2, 4, 3, 1, 2, 1

So I think it is being entered by someone manually.

I tried your modified query and got the same key violation error.

 

[Andrzejek]

key violation error" you need to take a look at your table definition and see which constrain is being violated.

PK in PODetTbl table - "I think it is being entered by someone manually." - bad, bad idea :-(

Have fun.

---- Andy

There is a great need for a sarcasm font.

 

[breezett93]

I modified the query just slighty, adding in POItm since it is not an Auto Number. The query below successfully copied the PO in POTbl and copied all the PO items in PODetTable.


INSERT INTO PODetTbl ( POId, POItm, Qty, ItemId, Unit, [Desc], Cost, FrtCost, [For], DueDt, RcvdDt, Recvr, Spec, Complete, FSC, Delivered, DelNotes )
SELECT (SELECT MAX(POId) FROM POTbl), POItm, Qty, ItemId, Unit, Desc, Cost, FrtCost, For, DueDt, RcvdDt, Recvr, Spec, Complete, FSC, Delivered, DelNotes
FROM PODetTbl
WHERE POId = Forms![POFrm01]![POId];


Thank you both for all your help!

 

[Andrzejek]

So this statement:

POItm is the PK in PODetTbl.

is not true, which means you do not have a PK on PODetTbl table.

So what is this field for: POItm ? Since you can have duplicates of it....

Have fun.

---- Andy

There is a great need for a sarcasm font.

 

[breezett93]

POItm is listed as a PK, which can be seen in the attached picture. This table (and database) was written long before I began working on it; so I have no idea what POItm actually is.