Duplicate a record including subform records

[chefboyrz]

I am working on a fairly complex many-to-many database and want to add a feature to allow a user to copy the current record as a new one by clicking a button on the form.

It is set up as a main form bound to a query (to calculate time) and there are 6 subforms, each bound to a separate table. Each subform is Child/Master linked by the same primary key to the main form. This is to support the many-to-many relationship of the information collected with each record. (Essentially the user is selecting a number of options from 6 different lists for each record).

I have the button set up to duplicate the main form record with this (modified wizard-generated) code, which works swimmingly:

Me.AllowAdditions = True 'added because I have controls in place that limit data entry on the form
​

DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 2, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 5, , acMenuVer70
​

I need the user to be able, with the same click, to duplicate the records in the related tables and I am not sure what the method would be for that. Can you help? Thanks in advance!!

 

[Uncle Gizmo]

The key information as you have stated: "linked by the same primary key to the main form" this primary key will automatically be duplicated in each table behind your subform for each record you enter in the subform. Let's assume the primary key in the case you want to duplicate is "100"

You need to create a query for each table behind each subform which returns the records associated with the primary key on the main form.

Once you are sure each query is returning the right information, then you can change the select queries you have created to append queries. Bear in mind that you cannot append to a unique ID field, these have to be generated automatically.

 

[chefboyrz]

I think I understand the first half of what you say... I can create the queries, infact I have probably already have them done in some form on the reporting side of my DB, and they work. So a query for each subform... i think i get it.

This is where my VBA experience falls off. How do I call (?) the query from the button?

 

[Uncle Gizmo]

>>>This is where my VBA experience falls off. How do I call (?) the query from the button?<<<

The command button wizard has an option for running a query. I suggest you start with this and then incorporate that code into your form.

 

[chefboyrz]

OK I'm a little stuck. Here's what I have so far:

Dim stDocName As String
Dim RecordToCopy As Integer

Me.AllowAdditions = True
RecordToCopy = Me.WorkRecordID

'Dupe the Main Record
DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 2, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 5, , acMenuVer70

'Run the Append Query
stDocName = "qCopyRecord_Cash"
DoCmd.OpenQuery stDocName, acViewNormal, acEdit
​

Now, the record that I want to copy is variable based on the record that I am currently viewing. I thought I might be able to include a 'where condition' in my OpenQuery command like when I DoCmd.OpenReport (I realze that speaks to the filter in the report not the query), but that's not in the parameters. How do I limit my query to the current record?

AND after all this typing I THINK i just solved the puzzle. I'll put it here for others' benefit...

I added a criteria to the query WHERE [Forms].[MyForm].[FieldWithPK]=table.PK

WHERE (tWorkRecords.WorkRecordID)=[Forms].[fEntry].[WorkRecordID];
in the query.

 

[chefboyrz]

Hold on.
This is more tricky. I'll be back on this on Monday.

 

[pbaldy]

Here's the process I suspect you need:

1. Note the primary key of the main record to be copied
2. Append a new record into the main table using that key
3. Get the primary key of the newly appended record
4. Use that along with the old key to append records to the child table(s)

Searching for @@Identity here will probably turn up code, as that is a common method of capturing the primary key of the newly appended"record.

 

[Uncle Gizmo]

>>>Note the primary key of the main record to be copied<<<

You are right Paul, I missed that!

 

[pbaldy]

Teamwork, my friend.

My daughter lives in your area now. Well, "your area" in the world sense anyway. They just moved to Bury St Edmunds northeast of London. I'm hoping to get over there this year, perhaps in the fall. Is that a good time to visit?

 

[Uncle Gizmo]

>>>Is that a good time to visit?<<<

Yes anytime is good, we could perhaps have a beer or two?

 

[pbaldy]

I'd like that; a genuine English beer in a genuine English pub with a genuine Englishman.

My wife and I were there a couple of years ago, but only had an afternoon and evening to spend (overnight layover on a trip to Italy). We just wandered around London, took the typical double-decker bus tour, and had dinner at what was supposedly a locals place. We were going to go on the London Eye. There was nobody getting on, so we figured it would be no problem. There was a massive line inside to get tickets. It's like they weren't actually getting anybody through the line and onto the stupid thing.

I'm looking forward to spending more time exploring the area. I'm hoping to avoid the heavy tourist season, whenever that is.

 

[Uncle Gizmo]

>>>I'd like that; a genuine English beer in a genuine English pub with a genuine Englishman<<<

You will have to be very quick, pubs are closing over here faster than you can say "can I have another pint please"

 

[Henley12]

I understand how to run a query using the old key to pull up the values I need, but how do I append them to the table using the new key? I have the query running with criteria on the key pulling from the form and the old key value.

 

[pbaldy]

Duplicate question, answered here:

http://www.access-programmers.co.uk/forums/showthread.php?t=169502