INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Duplicating two related records in two tables

Duplicating two related records in two tables

(OP)
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.

RE: Duplicating two related records in two tables

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

RE: Duplicating two related records in two tables

(OP)
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

RE: Duplicating two related records in two tables

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

Try:
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 =
Whatever record ID you want to copy

Have fun.

---- Andy

There is a great need for a sarcasm font.

RE: Duplicating two related records in two tables

(OP)
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]

RE: Duplicating two related records in two tables

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

Have fun.

---- Andy

There is a great need for a sarcasm font.

RE: Duplicating two related records in two tables

(OP)
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?

RE: Duplicating two related records in two tables

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

[Desc]

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

RE: Duplicating two related records in two tables

Then you will (probably) find that another field: For 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...ponder

Have fun.

---- Andy

There is a great need for a sarcasm font.

RE: Duplicating two related records in two tables

(OP)
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?

RE: Duplicating two related records in two tables

If this is your PODetTbl table:

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

I assume field POId is the foreign key to POTbl.POId PK?
So what id the PK in PODetTbl table?

Have fun.

---- Andy

There is a great need for a sarcasm font.

RE: Duplicating two related records in two tables

(OP)
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.

RE: Duplicating two related records in two tables

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:

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



Have fun.

---- Andy

There is a great need for a sarcasm font.

RE: Duplicating two related records in two tables

(OP)
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.

RE: Duplicating two related records in two tables

"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 sad

Have fun.

---- Andy

There is a great need for a sarcasm font.

RE: Duplicating two related records in two tables

(OP)
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!

RE: Duplicating two related records in two tables

So this statement:

Quote (from 4 Apr 17 16:14)

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.

RE: Duplicating two related records in two tables

(OP)


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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close