Duplicating the previous record 3

[integritycare]

Hello,

I have a request to be able to duplicate the previous record on an access form so that the user can just change a date field on that duplicated field on that record

Have tried various code from different sites, but have had no luck.
Is using an append query the way to go or is there a simpler solution.

Many thanks,

Integrity

 

[dhookom]

Have you found any examples that simply set the default value of the bound controls to the value just entered?

Duane
Hook'D on Access
MS Access MVP

 

[kjv1611]

Well, if you are always wanting to capture the last entered record, and you have an autonumber ID field, you could do something like this:

SELECT MyFields
FROM MyTable
WHERE MyAutoNumberField = (SELECT MAX(AutoNumberField) FROM MyTable)

And have the default values, maybe, set to the relative fields in that query.

But this is just a guess. I haven't tried it

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[integritycare]

Hi guys,

Thanks for your input.
Last night I was fiddling with some ideas that seem to work.Below is what i have used..

Private Sub Status_AfterUpdate()
 With Me.RecordsetClone
            .AddNew
                ![InvoiceNumber] = Me.[InvoiceNumber]
                 ![DateRaised] = Me.[DateRaised]
                 ![DateRcvd] = Me.[Date1]
                 ![ChqNo] = Me.[ChqNoA]
                 ![AmmtRcvd] = Me.[BalDue]
                 ![BrokerstaffID] = Me.[BrokerstaffID]
                 ![Companyname] = Me.[Companyname]
                 ![AccountNo] = Me.[AccountNo]
                 ![acType] = Me.[acType]
                
               
           .Update
            Me.Bookmark = .LastModified
        
End With
End If

End Sub

Now while this works I am not sure if this is the way to do it. ANy thoughts..

Integrity

 

[dhookom]

Looks like it should work.

Duane
Hook'D on Access
MS Access MVP

 

[kjv1611]

integritycare,

Thanks for sharing what seems to be working for you. It's an interesting concept that could fit in some instances. I wonder, though, if it would still be a little better to use an append query for adding the record, and then moving to the last modified record the way you currently do. It probably would be so insignificant that it would make no difference that you could see, but it may be a little faster to run an Append SQL string from the code.

Well, since I'm curious, I'll get around to playing with the idea later, but have other things I have to do right now.

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[missinglinq]

These six lines of code will copy all Fields to a New Record with the exception of the Control named DateFieldToLeaveEmpty. When it is finished executing, you will automatically be sitting on the New Record.

If Me.Dirty Then Me.Dirty = False

 DoCmd.RunCommand acCmdSelectRecord
 DoCmd.RunCommand acCmdCopy
 DoCmd.GoToRecord , , acNewRec
 DoCmd.RunCommand acCmdPaste

 Me.DateFieldToLeaveEmpty = Null

The Missinglinq

Richmond, Virginia

The Devil's in the Details!

 

[kjv1611]

Good deal, missinglinq. Should have thought about that, now that I see it, it seems so obvious. Thanks for sharing!

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57