VBA to clone recordset then go to newly created clone (1 Viewer)

tmyers

Well-known member
Local time
Today, 09:42
Joined
Sep 8, 2020
Messages
1,090
Today seems to be a VBA kind of day.

I have a process that clones a recordset then prompts the user to name the newly create clone via an Inputbox.

I then typically use a combobox populated with the name of the original and all clones to navigate to each record. I however want to add in the code that after the clone is created, go to that record. This is to prevent the user from think the form is on the newly created clone, when it is in fact not until they select it in the combo.

I have the user value from the Inputbox as strinput.

I tried:
Code:
DoCmd.GoToRecord acDataForm, "JobQuote", acGoTo = "[RevisionName] = '" & strinput & "'"
But that failed.

What did a mess up on?
 

theDBguy

I’m here to help
Staff member
Local time
Today, 06:42
Joined
Oct 29, 2018
Messages
21,358
Hi. Not sure I follow. You may be using the terminology a little differently. As far as I know, you don't "name" a cloned recordset. Instead, you assign it to a recordset variable.

Are you maybe referring to a cloned record?
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 09:42
Joined
Feb 19, 2002
Messages
42,981
Why do you not want the form bound to the actual record? What do you think using a RecordSetClone is doing for you? A RecordSetClone still points to the physical table so if you updated it, it would still be updated.
 

tmyers

Well-known member
Local time
Today, 09:42
Joined
Sep 8, 2020
Messages
1,090
The code takes the record and all child records and clones/copies them. I then use strinput to update a field [RevName] to identify it from the original. So all records are the exactly same (at time of creation) except for their [RevName] in the "master" table.

I then use a combo that is populated with the [RevName] that allows the user to navigate to each particular one. The code that creates all of this however, doesn't navigate/filter you to the newly created record and the time it finishes, which I think is unintuitive.

Does that help clear it up a little bit?
 

theDBguy

I’m here to help
Staff member
Local time
Today, 06:42
Joined
Oct 29, 2018
Messages
21,358
Does that help clear it up a little bit?
Yes, you are using the term "clone" to mean duplicating records. Access also has a method called RecordsetClone, which means something else.
 

tmyers

Well-known member
Local time
Today, 09:42
Joined
Sep 8, 2020
Messages
1,090
Yes, you are using the term "clone" to mean duplicating records. Access also has a method called RecordsetClone, which means something else.
Ah. I tend to use incorrect terminology a lot. Makes finding things via Google searches quite rough!
 

theDBguy

I’m here to help
Staff member
Local time
Today, 06:42
Joined
Oct 29, 2018
Messages
21,358
Ah. I tend to use incorrect terminology a lot. Makes finding things via Google searches quite rough!
For example, a Recordset is an object. It has a property called RecordsetClone, which is a copy of itself. But, that doesn't mean you'll now have double the number of records in your source table.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 09:42
Joined
Feb 19, 2002
Messages
42,981
Copying sets of data is best done with queries. Use a DAO .AddNew to copy the top level record. This makes it easy to pick up the new autonumber. Then use an append query to copy each child table from the original record to the copy. Supply the autonumber of the new record and the new RevNum as arguments. One line of code.

I came into Access with over 20 years of programming experience. I had written my million lines of code and didn't need the practice so I have always been happy to use the RAD tools of Access to save me from writing hundreds or even thousands of lines of code in each application. So, although I do write a lot of code, it is my last choice rather than my first. Start with action queries. If they can't do it, try property settings, then look for a VBA or SQL function, when all else fails, write code.
 

tmyers

Well-known member
Local time
Today, 09:42
Joined
Sep 8, 2020
Messages
1,090
I could believe it. The code I wrote out to do the cloning/copying is a monster. It is very long. It works though :)
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 09:42
Joined
Feb 19, 2002
Messages
42,981
Just because it works doesn't mean that you want to leave it. Save it for now so you have a safety blanket. Then try to do the process as I described it. DAO .AddNew to copy the bound controls to a new record. Then an append query to copy the details. If you have multiple child tables, you will need an append query for each child table.

Once you get your sea legs, you will realize that writing code is actually the easiest part of a project. It is much harder to conceptualize it and come up with a solution the user is comfortable with and then make a table schema to support the required data than it is to implement. The worst programmers I have ever worked with have the opinion that a line of code written, must be preserved at all costs. One of the things I learned at the school of hard knocks is that frequently, when you are making major logic changes, it is best to start again from the beginning. I make far fewer mistakes writing logic when I write original code. When I change existing code, I am much more likely to introduce errors.
 

Kayleigh

Member
Local time
Today, 13:42
Joined
Sep 24, 2020
Messages
706
Found that code really useful @MajP ! But I am trying to copy records on forms with multiple subforms (related tables) - have enclosed a basic sample of what I'm working on...
Would really appreciate help on coding the cmdCopySession for this form.
 

Attachments

  • SessionTest11.accdb
    1 MB · Views: 147

Users who are viewing this thread

Top Bottom