Button to copy values from 5 fields in the current form to new form

[wehoscottward]

Hello—I’m not having much luck with my Goggle research.
I would like to create a command button on my form that copies values from 5 fields in the current record into a new record—leaving all the other fields blank except for the new record ID.
The fields that contain the data that I want copied into a new form are:
1. Member_Name
2. Member_ID
3. Account
4. UBH/PBH
5. Assigned_WRCA

I would greatly appreciate any advice on this! Thanks.
Sincerely, Scott

 

[missinglinq]

You apparently don't mean "in the current form to new form," as your title says, but rather from the current Record to a new Record! Presumably the record ID will self-populate; an Autonumber, perhaps? Something like this:

'Copy fields to variables
MyMember_Name = Me.Member_Name
MyMember_ID = Me.Member_ID
MyAccount = Me.Account
MyUBH/PBH = Me.UBH/PBH
MyAssigned_WRCA = Me.Assigned_WRCA

'Go to a new record
DoCmd.GoToRecord , , acNewRec

'Reverse the process and plug old values into new record
Me.Member_Name = MyMember_Name
Me.Member_ID = MyMember_ID
Me.Account = MyAccount
Me.UBH/PBH = MyUBH/PBH 
Me.Assigned_WRCA = MyAssigned_WRCA

BTW, that slash in UBH/PBH is probably not a good idea; names ideally only contain AlphaNumeric characters, as special characters tend to cause problems, sooner or later.

Linq ;0)>

 

[wehoscottward]

Hi, Linq!

Thanks so much for your reply!

This works great except for 1 field—the Assigned_WRCA field.

If I copy the code for the 1st 4 fields everything works perfectly. When I add the code for the 5th field (Assigned_WRCA), I get runTime error '2424"

It says that it can't find: MyAssigned_WRCA = Me.Assigned_WRCA.

I double checked my table and the field name is spelled correctly. Any ideas why this might be happening?

 

[missinglinq]

I assumed that your were doing this in a Form, is that correct? If so, you need to check that the Control name in the Form is spelled correctly. Control names and the names of the Fields Bound to them are not always identical.

Linq ;0)>

 

[wehoscottward]

Oh, you're right! The field name was slightly different in my form. I changed it and everything works perfectly now. I'm so happy! Thanks so much!
Scott

 

[missinglinq]

Glad we could help!

Linq ;0)>

 

[wehoscottward]

Hi, Linq.
Unfortunately, I spoke too soon! The code you gave me is working and creates a new record with the 5 different fields copied from the old record into the new record. The problem I’m having now, is that the ID field which is programed to generate an AutoNumber, which should be unique to each new record is not generating a new unique number. For some reason, it’s generating a number that’s been used before which messes other stuff up.
Is there some code that I can use so that a new record is generated with the data from the 5 fields plus a new and unique ID number in the ID field?
Scott

 

[missinglinq]

...the ID field which is programed to generate an AutoNumber, which should be unique to each new record is not generating a new unique number...

You need to explain this in greater detail. Is this, in fact, an Autonumber? Or is this an auto-incremented number? If the latter, how is it generated?

Linq ;0)>

 

[wehoscottward]

Hi, Linq.

Never mind, I figured it out. I needed to make the ID field which is an AutoNumber field in my table into the Primary Key field. Once I did this, all records were given unique numbers rather than duplicated numbers and my problems were solved.

Thanks again for all your help yesterday.

Scott