Duplicating current record (1 Viewer)

[UserError]

I have looked and tried several solutions for this and cannot make any of them work.

I understand the idea that copying records is bad, but my client wants it as the input form for his assessments have 40+ fields and many of the records differ by one or two elements. Some of the fields are long text and not really suitable for putting in tables to draw on plus the dtata hasn't been entered yet (and we're out of time as he goes out on site tomorrow).

Given that MS provide a "Duplicate record" button, it would also seem not unreasonable. However, they seem to have neutered that and despite trusting everything a sper the myriad posts oj the web, it still doesn't work. Messing around with registry entries also isn't viable as two other people will have a copy of ths on their laptops and it's just not an option.

I've seen queries mentioned as being the correct way, but have been unable to select the current record to pass forward. All the VBA examples I've tried to adapt have just not worked - I'm so rusty with VBA (<10 years since I did any) that it's not going well.

I have no subtables, no incrementing, updating or whatever, I just need to reproduce the"Duplicate Record" button that my client has been using for years.
I would be extremely grateful if someone could point me to any basic resources that would help. It's getting late & my brain is getting frazzled too.
Many thanks.

 

[theDBguy]

Hi. I would probably use VBA, but check out the RunCommands acCmdSelectRecord and acCmdPasteAppend to see if you can use them. They may be available as macro actions too.

 

[Pat Hartman]

Here is a quick and dirty method. It is dirty because in a multi-user environment, you can't rely on the second query obtaining the ID you actually want.
You need three queries and two need arguments.
1. for the main record
Insert Into maintbl (fld1, fld2, fld3)
Select fld1, fld2, fld3
From maintbl WHERE PK = Forms!yourform!txtPK

This selects all columns of the record EXCEPT for the autonumber PK

2. Select Max PK From maintbl

to get the "newest" ID

3. for the child records
Insert into childtbl (fldFK, fldA, fldB, FldC)
Select qry2.PK, fldA, FldB, FldC
From qry2, childtbl
Where childtblPK = Forms!yourform!txtPK

this joins to the query that gets the newPK so it can be used as the FK in the inserted records.

This is air code so you're on your own.

You can't select * because you don't want the existing PK's so you need specific field lists.

The better method is to use DAO to insert the new main record because that allows you to obtain the newPK in the same thread so you know it is the one you want. The new autonumber is availabele immediately after the .AddNew. Then you can use an append query with two arguments to append the child records.

 

[UserError]

Hi. I would probably use VBA, but check out the RunCommands acCmdSelectRecord and acCmdPasteAppend to see if you can use them. They may be available as macro actions too.

Thanks. The button wizard creates a macro for this and Access disables it as unsafe, flagging up the RunMenuCommand. Just been looking in the Action Catalog and trying to work it out. This has grown somewhat beyond what I thought I was gong to be doing...

 

[Pat Hartman]

Did you try the solution I suggested?

You could also try converting the macro to code. Post it here if the code doesn't work once converted.

 

[theDBguy]

Thanks. The button wizard creates a macro for this and Access disables it as unsafe, flagging up the RunMenuCommand. Just been looking in the Action Catalog and trying to work it out. This has grown somewhat beyond what I thought I was gong to be doing...

Maybe try converting the macro to VBA?

 

[UserError]

Maybe try converting the macro to VBA?

Looking at that now thanks & to Pat. Just tracked tdown the convert macro button. FWIW, the acCmdPasteAppend wasn't available in the RunMenuCommands

 

[UserError]

Hmmm. Converting may have worked - the record count goes up when I click the button. Now to see what the fallout of the other conversions is. Thanks for that. Hopefully I'm over the hump.

 

[theDBguy]

FWIW, the acCmdPasteAppend wasn't available in the RunMenuCommands

I see. But it should be available in VBA.

DoCmd.RunCommand acCmdPasteAppend

 

[theDBguy]

Hmmm. Converting may have worked - the record count goes up when I click the button. Now to see what the fallout of the other conversions is. Thanks for that. Hopefully I'm over the hump.

Hi. Glad to hear you're making good progress. Continued success with your project.

 

[UserError]

Hi. Glad to hear you're making good progress. Continued success with your project.

Thank you all very much for your help. I was out of my depth here, but have now emailed the DB off to my client. Hope it works OK at his end Serious brownie points if it does and possibly ex-client if it doesn't. My main work is GIS & this was peripheral to it, using data I'd geoprocessed.