Duplication of Data on a form

[David627]

I have a user that needs to enter many records that are basically the same with the exception of one or two fields.

Instead of making keying errors and having to type most of the same stuff a dozen or so times, is there a function that will duplicate the entire record that I could attach to a button so the entire previous record is duplicated where he can change his fields and then it would become a unique record. (the primary keys are part of what will be changed)

Thanks,
David

[CreganTur]

Just set defaul values in your table :thumb

[David627]

set default values to what?

David

[CreganTur]

set default values to what?

David

To whatever values you want to prepopulate in your fields.

[David627]

This doesn't make sense. Randy - please reread my original post. Setting default values won't help me duplicate values on an ad hoc basis

David

[CreganTur]

This doesn't make sense. Randy - please reread my original post. Setting default values won't help me duplicate values on an ad hoc basis


Misunderstood your first post. Didn't realize that this was for ad hoc where data that needs to be duplicated could change.

Depending on how the user moves to the next record, you could read all of the values on the form into variables, move to a new record, and then write in the values from the variables. If your user clicks a command button to move to the next record, this would be easy... but if they use Access' native record navigation buttons, then you'd have to figure out which event to use.

[OBP]

David, open a RecordsetClone, move to the last record and set the new Records Fields to the values that you require in the RecordsetClone, because it is a recordsetclone it will have the previous record until the Current "New" record is written to the table.
If you can give me a list of the field names that you want reproduced Randy or I can write the code for you.
A simple example would be

dim rs as object
Set rs = Me.RecordsetClone
rs.movelast
me.name = rs.name 'or me.name = rs!name or if there are spaces in the field names me.[Last name] = rs![last Name]
rs.close
set rs = nothing

[David627]

ODP:

I need to be looking at the data on a form, click a cmd button and have it replicate the data in the form for me to amend a few fields before saving the data. The field names are:

RecID
Entry Date
CHECK_NO
Check Date
PAYER
Permit Fee
Surcharge
Amendment Fee
Configuration Fee
Contiguous County Fee
Permit Number
Permit Type
Permit Class
Rejected
USDOT

Thanks

[OBP]

dim rs as object
Set rs = Me.RecordsetClone
rs.movelast
docmd.GoToRecord , , acNewRec
me.RecID = rs.RecID
me.[Entry Date] = rs![Entry Date]
me.CHECK_NO = rs.CHECK_NO
me.[Entry Date] = rs![Entry Date]
me.PAYER = rs.PAYER
me.[Permit Fee] = rs![Permit Fee]
rs.close
set rs = nothing
I think that you can work out the rest of the fields.l

[David627]

Here is what I entered:

Private Sub PBReplicate_Click()
Dim rs As Object
Set rs = Me.RecordsetClone
rs.MoveLast
DoCmd.GoToRecord , , acNewRec
Me.RecID = rs.RecID + 1
Me.[Entry Date] = rs![Entry Date]
Me.CHECK_NO = rs.CHECK_NO
Me.[Entry Date] = rs![Entry Date]
Me.PAYER = rs.PAYER
Me.[Permit Fee] = rs![Permit Fee]
Me.Surcharge = rs.Surcharge
Me.Amendment Fee = rs![Amendment Fee]
Me.[Configuration Fee] = rs![Configuration Fee]
Me.[Contiguous County Fee] = rs![Contiguous County Fee]
Me.[Permit Number] = rs![Permit Number]
Me.[Permit Type] = rs![Permit Type]
Me.[Permit Class] = rs![Permit Class]
Me.Rejected = rs.Rejected
Me.USDOT = rs.USDOT
rs.Close
Set rs = Nothing
End Sub

RecID is the primary key so I wanted to increment it by 1.

I get a Compile Error: Method or Data member not found

Any assistance would be beneficial.

Thanks
David

[hansup]

I get a Compile Error: Method or Data member not found
This line confuses Access:

Me.Amendment Fee = rs![Amendment Fee]

Use brackets to tell Access that "Amendment Fee" should not be evaluated as two separate words:

Me.[Amendment Fee] = rs![Amendment Fee]

I have been tripped up by the same issue enough times in the past that I avoid using spaces in field (and other object) names. I use only letters, digits, and the underscore character, to avoid problems.

Good luck,
Hans

[David627]

I see - I just copied and pasted and didn't apply that rule to the other like fields. Thanks

[David627]

I fixed per Hans post and I get the same error and it highlights .RecID in the first Me. line.

[OBP]

If it is an Autonumber you can't set it.

[David627]

I took out RecID because it is an autonumber - now I get it for CHECK_NO

[hansup]

I took out RecID because it is an autonumber - now I get it for CHECK_NOSorry, David. I spotted one problem, but didn't look further.

Change your CHECK_NO line to this:

Me.CHECK_NO = rs!CHECK_NO

In each case where you reference a field in the recordset, use an exclamation point instead of a dot between the name of the recordset object variable ("rs") and the field name.

Good luck,
Hans

[David627]

still didn't work :banghead:

[OBP]

What error message do you get now?
It would help if you could post the database.

[CreganTur]

Me.CHECK_NO = rs!CHECK_NO
Try using this:
Me.CHECK_NO = rs!CHECK_NO.Value

In my experience, bad things can happen if you don't explicitly use the .Value method when trying to pull the value of a recordset field.

HTH:thumb

[hansup]

still didn't work :banghead:Are you still getting the very same error message? Sorry, I don't see why.

Please consider attaching a copy of your database as a zip file. Discard or muddle any sensitive/confidental data. Then do a compact/repair before creating the zip. Maybe actually seeing your database will allow us to figure out the problem quicker. My gut reaction is this *should* be a simple one to fix, but it's taking us way too long to get there.

[hansup]

What error message do you get now?
It would help if you could post the database. Hey, OBP! You said it more concisely! :thumb

Cheers,
Hans

[David627]

I am getting the same error as before - will add .value to all statements to see if that helps

[David627]

In the ChecksMain form - the code is attached to the Replicate Record button.

Thanks all... :help

David

[OBP]

You have the Button on the Main form and the data in the Subform, just move the Button and it's code to the Header of the Subform and it works great.

[David627]

Thanks ODP!! Works great - just had to add in a field that wasn't accounted for in the data. One question - even though the data is replicated perfectly in the table, how come the form doesn't display some info (payee, DOT# and permit number) unless you click on the field?