Copying a record and all related records from other tables.

Is there a somewhat easy way to copy a record and have all related records from other tables copy as well. For instance, I have a maintenance database that has an equipment form. Within this form is a subform that lists schedules and yet another that lists ratings. I can copy the equipment record to a new record no problem, but the subform data does not copy. Is there a way to make that happen as well?

Basically you have to append the new main record, get the new ID number, and then copy the child data using the old and new ID numbers. Searching for @@Identity should turn up methods, as that is one way of getting the new ID number.

I've got the copy part down of the main record, but I'm having trouble with the related info. I can run a query to pull up the ratings based on the old key value, but how do I append them to the table with the new key value?

The simplest way would probably be an append query, using a SELECT clause that includes the new key value and a WHERE clause with the old key value.

I'm getting a key violation now when I try to copy the schedules. The primary key is the ScheduleID, shouldn't it just create a new key when a new record is created?

What is the SQL of your query, and what is the primary key(s) on that table?

Here is my SQL. The primary key is ScheduleID in the tblEquipSchedule table.

INSERT INTO tblEquipSchedule ( SchNextGenDate, DaysToCmplt, SchCalFreq, SchCalRun, SchXTime, Priority, Craft, eqsCompleteClassLuId, EquipKey, AssignTo, JobID, JobKey, JobDesc, SchRTInterval, SchRTNext, SchSeasonal, SchJan, SchFeb, ScheduleID, SchMar, SchApr, SchMay, SchJune, SchJuly, SchAug, SchSep, SchOct, SchNov, SchDec, SchInactive, SchMon, SchTue, SchWed, SchThu, SchFri, SchSat, SchSun, SchDay, SchWeekDay, EquipID )
SELECT tblEquipSchedule.SchNextGenDate, tblEquipSchedule.DaysToCmplt, tblEquipSchedule.SchCalFreq, tblEquipSchedule.SchCalRun, tblEquipSchedule.SchXTime, tblEquipSchedule.Priority, tblEquipSchedule.Craft, tblEquipSchedule.eqsCompleteClassLuId, tblEquipSchedule.EquipKey AS tblEquipSchedule_EquipKey, tblEquipSchedule.AssignTo, tblEquipSchedule.JobID, tblEquipSchedule.JobKey, tblEquipSchedule.JobDesc, tblEquipSchedule.SchRTInterval, tblEquipSchedule.SchRTNext, tblEquipSchedule.SchSeasonal, tblEquipSchedule.SchJan, tblEquipSchedule.SchFeb, tblEquipSchedule.ScheduleID, tblEquipSchedule.SchMar, tblEquipSchedule.SchApr, tblEquipSchedule.SchMay, tblEquipSchedule.SchJune, tblEquipSchedule.SchJuly, tblEquipSchedule.SchAug, tblEquipSchedule.SchSep, tblEquipSchedule.SchOct, tblEquipSchedule.SchNov, tblEquipSchedule.SchDec, tblEquipSchedule.SchInactive, tblEquipSchedule.SchMon, tblEquipSchedule.SchTue, tblEquipSchedule.SchWed, tblEquipSchedule.SchThu, tblEquipSchedule.SchFri, tblEquipSchedule.SchSat, tblEquipSchedule.SchSun, tblEquipSchedule.SchDay, tblEquipSchedule.SchWeekDay, [Forms]![frmNewEquipment]![EquipKey] AS NewKey
FROM tblEquipment INNER JOIN tblEquipSchedule ON tblEquipment.EquipKey = tblEquipSchedule.EquipID
WHERE (((tblEquipment.EquipKey)=[Forms]![frmNewEquipment]![SvKey]));

I see no need for the join; try without tblEquipment in there at all. If ScheduleID is an autonumber, you should not be including that field in your append.

Still getting a key violation. Updated code is:

INSERT INTO tblEquipSchedule ( SchNextGenDate, DaysToCmplt, SchCalFreq, SchCalRun, SchXTime, Priority, Craft, eqsCompleteClassLuId, EquipKey, AssignTo, JobID, JobKey, JobDesc, SchRTInterval, SchRTNext, SchSeasonal, SchJan, SchFeb, ScheduleID, SchMar, SchApr, SchMay, SchJune, SchJuly, SchAug, SchSep, SchOct, SchNov, SchDec, SchInactive, SchMon, SchTue, SchWed, SchThu, SchFri, SchSat, SchSun, SchDay, SchWeekDay, EquipID )
SELECT tblEquipSchedule.SchNextGenDate, tblEquipSchedule.DaysToCmplt, tblEquipSchedule.SchCalFreq, tblEquipSchedule.SchCalRun, tblEquipSchedule.SchXTime, tblEquipSchedule.Priority, tblEquipSchedule.Craft, tblEquipSchedule.eqsCompleteClassLuId, tblEquipSchedule.EquipKey AS tblEquipSchedule_EquipKey, tblEquipSchedule.AssignTo, tblEquipSchedule.JobID, tblEquipSchedule.JobKey, tblEquipSchedule.JobDesc, tblEquipSchedule.SchRTInterval, tblEquipSchedule.SchRTNext, tblEquipSchedule.SchSeasonal, tblEquipSchedule.SchJan, tblEquipSchedule.SchFeb, tblEquipSchedule.ScheduleID, tblEquipSchedule.SchMar, tblEquipSchedule.SchApr, tblEquipSchedule.SchMay, tblEquipSchedule.SchJune, tblEquipSchedule.SchJuly, tblEquipSchedule.SchAug, tblEquipSchedule.SchSep, tblEquipSchedule.SchOct, tblEquipSchedule.SchNov, tblEquipSchedule.SchDec, tblEquipSchedule.SchInactive, tblEquipSchedule.SchMon, tblEquipSchedule.SchTue, tblEquipSchedule.SchWed, tblEquipSchedule.SchThu, tblEquipSchedule.SchFri, tblEquipSchedule.SchSat, tblEquipSchedule.SchSun, tblEquipSchedule.SchDay, tblEquipSchedule.SchWeekDay, [Forms]![frmNewEquipment]![EquipKey] AS NewKey
FROM tblEquipSchedule
WHERE (((tblEquipSchedule.EquipID)=[Forms]![frmNewEquipment]![SvKey]));

You're still appending ScheduleID, so if that's the primary key, you'll get an error. Is it an autonumber field? If so, drop it from both clauses.

I looked right over that before. Sorry. However, I am still getting the key violation and I have removed the ScheduleID field.

INSERT INTO tblEquipSchedule ( SchNextGenDate, DaysToCmplt, SchCalFreq, SchCalRun, SchXTime, Priority, Craft, eqsCompleteClassLuId, EquipKey, AssignTo, JobID, JobKey, JobDesc, SchRTInterval, SchRTNext, SchSeasonal, SchJan, SchFeb, SchMar, SchApr, SchMay, SchJune, SchJuly, SchAug, SchSep, SchOct, SchNov, SchDec, SchInactive, SchMon, SchTue, SchWed, SchThu, SchFri, SchSat, SchSun, SchDay, SchWeekDay, EquipID )
SELECT tblEquipSchedule.SchNextGenDate, tblEquipSchedule.DaysToCmplt, tblEquipSchedule.SchCalFreq, tblEquipSchedule.SchCalRun, tblEquipSchedule.SchXTime, tblEquipSchedule.Priority, tblEquipSchedule.Craft, tblEquipSchedule.eqsCompleteClassLuId, tblEquipSchedule.EquipKey AS tblEquipSchedule_EquipKey, tblEquipSchedule.AssignTo, tblEquipSchedule.JobID, tblEquipSchedule.JobKey, tblEquipSchedule.JobDesc, tblEquipSchedule.SchRTInterval, tblEquipSchedule.SchRTNext, tblEquipSchedule.SchSeasonal, tblEquipSchedule.SchJan, tblEquipSchedule.SchFeb, tblEquipSchedule.SchMar, tblEquipSchedule.SchApr, tblEquipSchedule.SchMay, tblEquipSchedule.SchJune, tblEquipSchedule.SchJuly, tblEquipSchedule.SchAug, tblEquipSchedule.SchSep, tblEquipSchedule.SchOct, tblEquipSchedule.SchNov, tblEquipSchedule.SchDec, tblEquipSchedule.SchInactive, tblEquipSchedule.SchMon, tblEquipSchedule.SchTue, tblEquipSchedule.SchWed, tblEquipSchedule.SchThu, tblEquipSchedule.SchFri, tblEquipSchedule.SchSat, tblEquipSchedule.SchSun, tblEquipSchedule.SchDay, tblEquipSchedule.SchWeekDay, [Forms]![frmNewEquipment]![EquipKey] AS NewKey
FROM tblEquipSchedule
WHERE (((tblEquipSchedule.EquipID)=[Forms]![frmNewEquipment]![SvKey]));


Thanks for your patience and time in this matter.

Can you post the db?

It's too big to post on here. This database is in production at our facility.

I believe I have solved the problem, although I don't quite understand why this worked. I deleted the primary key field (which was just an autonumber key) and recreated it. That seemed to do the trick. Now my question is another matter. Let's suppose I'm on record number 10 when I copy the record. I need to refresh the new record without going back to record number one. I know I should be able to do this with the bookmark function, but I'm not quite sure where to put it. Any ideas on this subject? Thanks for your time on the other.

This kind of thing?

http://www.baldyweb.com/Requery.htm

Thanks for all your help. I believe I finally have it working as desired.