Duplicating some form fields to create a new record.

How do I add a button to a form that will create a new record that is populated with just some of the data from the current record?

The fields to duplicate are: Source, IM_Date, Port, Ship and Address

I'm not using a sub-form.

Thanks in advance.
LVL 18
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rey Obrero (Capricorn1)Commented:
using a click event of a command button

private sub cmdDuplicateRecord_click()


docmd.gotorecord ,,acnewrecord

end sub

in the current event of your form, place these codes

private sub form_current()
if me.newrecord and tempvars!copyrecord then


end if

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
WaterStreetAuthor Commented:
Need help to make it work.

I added some additional fields to duplicate, but the command button does not cause the duplication into a new record.  I don't see it in that form or in the table.

The code is shown below for that form.  Note that it includes two previous procedures that have worked fine for me.

Option Compare Database

Private Sub cmdDuplicateRecord_click()
TempVars!vSource = Me.Source.Value
TempVars!vIM_Date = Me.IM_Date.Value
TempVars!vPort = Me.Port.Value
TempVars!vShip = Me.Ship.Value
TempVars!vAddress = Me.Address.Value

TempVars!vRecDate = Me.RecDate.Value
TempVars!vRecRef = Me.RecRef.Value
TempVars!vRecLocation = Me.RecLocation.Value
TempVars!vLastName = Me.LastName.Value
TempVars!vNoOfRecords = Me.NoOfRecords.Value
TempVars!vLastName = Me.LastName.Value
TempVars!vEM_From = Me.EM_From.Value

TempVars!copyrecord = -1

DoCmd.GoToRecord , , acnewrecord
End Sub

Private Sub Form_MouseWheel(ByVal Page As Boolean, ByVal Count As Long)
Call DoMouseWheel(Me, Count)
End Sub

Private Sub Source_Click()
Me.Source = "NatDecOrPet"
End Sub

Private Sub form_current()
If Me.NewRecord And TempVars!copyrecord Then
Me.Source.Value = TempVars!vSource
Me.IM_Date.Value = TempVars!vIM_Date
Me.Port.Value = TempVars!vPort
Me.Ship.Value = TempVars!vShip
Me.Address.Value = TempVars!vAddress

Me.RecDate.Value = TempVars!vRecDate
Me.RecRef.Value = TempVars!vRecRef
Me.RecLocation.Value = TempVars!vRecLocation
Me.LastName.Value = TempVars!vLastName
Me.NoOfRecords.Value = TempVars!vNoOfRecords
Me.LastName.Value = TempVars!vLastName
Me.EM_From.Value = TempVars!vEM_From
End If
End Sub
Rey Obrero (Capricorn1)Commented:
hmm, can you upload a copy or stripped version of the db,,

are you getting any error?
Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

Rey Obrero (Capricorn1)Commented:
also, try saving the current record first

Private Sub cmdDuplicateRecord_click()
' add this line
if me.dirty then me.dirty=false   'force save the current record

' other codes here

end sub
Rey Obrero (Capricorn1)Commented:
another thing to look for is the Data Entry property of the form,
is it set to Yes? if not then we need to set it to Yes before going to new record

Private Sub cmdDuplicateRecord_click()
 ' add this line
 if me.dirty then me.dirty=false   'force save the current record

 ' other codes here

'add this line
DoCmd.GoToRecord , , acnewrecord
 End Sub
WaterStreetAuthor Commented:

Still doesn't work after making the changes.  Data entry is set to Yes.

Attached is a db copy after I made your changes to form "NaturalizationDecAndPet"

Note that form is based on a Select Query with criteria for only displaying records where Source = "NatDecOrPet"

Thank You
Rey Obrero (Capricorn1)Commented:
test this revised db,

Not:-- you did not name the command button as cmdDuplicateRecord
WaterStreetAuthor Commented:

Got it.

Thanks a lot.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.