Solved Clone record to new entry with multiple selections allowed in combo box (1 Viewer)

Manicduck

New member
Local time
Today, 22:06
Joined
May 22, 2020
Messages
29
Good afternoon, I wonder if someone out there could help please?

I am trying to copy one record from my table to a new record. I found an example on this forum in relation to another question which appeared to meet my requirements.

I want to copy a previously entered record and assign it with the next sequential issue number and allow the user to change the new record, but I want to retain the original record unaltered.

Unfortunately, when I attempt to run the code in my project, I get a Runtime Error Msg (64224).

I have attempted to fault find by substituting elements of my table into the OP’s example until it fails and I have reached the conclusion that it is tripping on my use of combo boxes that allow multiple selections.

Is there a way to adapt the code to allow for the use of these combo boxes as they are integral to how my project will work? I really don’t want to use an audit trail as it seems far too complicated.

I have attached the OP’s code with my addition of a combo box. Data is just an example, please bear in mind also, that I am a complete novice

Thank you for any help you can offer.

p.s. My table is quite large and as such, I am looking to avoid naming every field if possible
 

Attachments

  • Revise existing added combo box.zip
    75.4 KB · Views: 120

Gasman

Enthusiastic Amateur
Local time
Today, 22:06
Joined
Sep 21, 2011
Messages
14,047
And error 64224 is what?
 

theDBguy

I’m here to help
Staff member
Local time
Today, 15:06
Joined
Oct 29, 2018
Messages
21,358
Hi. Have you tried simply doing something like?
  1. SelectRecord
  2. CopyRecord
  3. PasteAppend
 

Manicduck

New member
Local time
Today, 22:06
Joined
May 22, 2020
Messages
29
Msg box states: Method ‘Value’ of object ‘Field2’ failed

in debug, the line of code highlighted= clone.Fields (f.Name) = f

hovering over the highlighted code, a box indicates the error in the f.Name = “Emotion” which is the combo box with multiple selections.

Hope this helps - thanks
 

Manicduck

New member
Local time
Today, 22:06
Joined
May 22, 2020
Messages
29
Hi. Have you tried simply doing something like?
  1. SelectRecord
  2. CopyRecord
  3. PasteAppend
Hi,

if it was just me working on the database, I would probably do as you suggest, in fact, I would probably use Excel, but, there will be quite a few people using it, so it needs to be as robust as possible.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 15:06
Joined
Oct 29, 2018
Messages
21,358
Hi,

if it was just me working on the database, I would probably do as you suggest, in fact, I would probably use Excel, but, there will be quite a few people using it, so it needs to be as robust as possible.
Hi. What's not robust about using built-in commands to do what you're trying to do with custom code?
 

Manicduck

New member
Local time
Today, 22:06
Joined
May 22, 2020
Messages
29
Hi. What's not robust about using built-in commands to do what you're trying to do with custom code?
Sorry, I misunderstood, I thought you were suggesting Manually copying and then pasting
 

Cronk

Registered User.
Local time
Tomorrow, 09:06
Joined
Jul 4, 2013
Messages
2,770
The problem is the combo lookup for the field Emotion in your table. Remove this and it will work.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 15:06
Joined
Oct 29, 2018
Messages
21,358
Sorry, I misunderstood, I thought you were suggesting Manually copying and then pasting
Hi. Sorry if I wasn't clear in the first place. But after seeing @Cronk's post, I wonder if it will work still. Please give it a try and let us know if it does/doesn't. Thanks!

Edit: On second thought, if you can "manually" copy and paste the record with the combobox, then it should work using the RunCommand method as well. Still, it would be nice if you can confirm it for us. Cheers!
 

Manicduck

New member
Local time
Today, 22:06
Joined
May 22, 2020
Messages
29
The problem is the combo lookup for the field Emotion in your table. Remove this and it will work.
Hi, yes I had worked that one out, but thanks.... the trouble is, it is a fairly important aspect of the project
 

Manicduck

New member
Local time
Today, 22:06
Joined
May 22, 2020
Messages
29
Hi. Sorry if I wasn't clear in the first place. But after seeing @Cronk's post, I wonder if it will work still. Please give it a try and let us know if it does/doesn't. Cheers!
Hey DBguy, no apologies necessary, it was my mistake..... removing the subject combo boxes will work, but they are fairly important
 

theDBguy

I’m here to help
Staff member
Local time
Today, 15:06
Joined
Oct 29, 2018
Messages
21,358
Hey DBguy, no apologies necessary, it was my mistake..... removing the subject combo boxes will work, but they are fairly important
Hi. See the edit in my last post. If you can leave the combo and do a manual copy and paste, then you should be able to use the RunCommand method. Please give it a shot.
 

Manicduck

New member
Local time
Today, 22:06
Joined
May 22, 2020
Messages
29
Hi. See the edit in my last post. If you can leave the combo and do a manual copy and paste, then you should be able to use the RunCommand method. Please give it a shot.
Hi DBguy, Yes I can manually select the row in the table, copy and paste in a new row, all data, except the ID auto number (as expected) Copies
 

theDBguy

I’m here to help
Staff member
Local time
Today, 15:06
Joined
Oct 29, 2018
Messages
21,358
Hi DBguy, Yes I can manually select the row in the table, copy and paste in a new row, all data, except the ID auto number (as expected) Copies
Okay, that tells me the RunCommand method should work, but you have to test it for us first.
 

Manicduck

New member
Local time
Today, 22:06
Joined
May 22, 2020
Messages
29
As I said, I am a real novice so might need a bit more information,

I take it any code would need to be placed in the form‘s VBA window (can’t see the need to use it elsewhere in the project at this stage)

I have put the following In the on click event for a new button

Code:
Private sub Go_click()

DoCmd.RunCommand acCmdSelectRecord
DoCmd.RunCommand acCmdCopy
DoCmd.RunCommand acCmdPasteAppend

Exit Sub

which seems to work for my test DB, is there anything else I should be doing?

what are the pitfalls??

thank you
 

theDBguy

I’m here to help
Staff member
Local time
Today, 15:06
Joined
Oct 29, 2018
Messages
21,358
As I said, I am a real novice so might need a bit more information,

I take it any code would need to be placed in the form‘s VBA window (can’t see the need to use it elsewhere in the project at this stage)

I have put the following In the on click event for a new button

Code:
Private sub Go_click()

DoCmd.RunCommand acCmdSelectRecord
DoCmd.RunCommand acCmdCopy
DoCmd.RunCommand acCmdPasteAppend

Exit Sub

which seems to work for my test DB, is there anything else I should be doing?

what are the pitfalls??

thank you
Nope, that should be it. Pitfalls are like you could potentially create multiple duplicate records.
 

Manicduck

New member
Local time
Today, 22:06
Joined
May 22, 2020
Messages
29
That is great, thank you for all of your help, duplicate records I can cope with!
 

Users who are viewing this thread

Top Bottom