Move to last record after replication

[David627]

One of my users has a form that he uses for data entry. Sometimes the information in the form is used a second time (check is applied to more than 1 item, invoice same number, etc.) so he wants the record duplicated. I have the code that does that, however I can't get the form to end up on the newly replicated record on the form so he can alter it and proceed.

I've tried DoCmd.RunCommand acCmdRecordsGoToLast and DoCmd.GoToRecord , , acLast and noe have worked.

The rest of my code (which works) is:

Private Sub PBReplicate_Click()

Dim rs As Object
Set rs = Me.RecordsetClone
rs.MoveLast
DoCmd.GoToRecord , , acNewRec
Me.[Entry Date] = rs![Entry Date].Value
Me.CHECK_NO = rs!CHECK_NO.Value
Me.[Entry Date] = rs![Entry Date].Value
Me.[Check Date] = rs![Check Date].Value
Me.PAYER = rs!PAYER.Value
Me.[Permit Fee] = rs![Permit Fee].Value
Me.Surcharge = rs!Surcharge.Value
Me.[Amendment Fee] = rs![Amendment Fee].Value
Me.[Configuration Fee] = rs![Configuration Fee].Value
Me.[Contiguous County Fee] = rs![Contiguous County Fee].Value
Me.[Permit Number] = rs![Permit Number].Value
Me.[Permit Type] = rs![Permit Type].Value
Me.[Permit Class] = rs![Permit Class].Value
Me.[RejectedAmendedCancelled] = rs![RejectedAmendedCancelled].Value
Me.USDOT = rs!USDOT.Value
rs.Close
Set re = Nothing
DoCmd.GoToRecord , , acLast
End Sub


Thanks,
David

Edited 29-Oct-09 by geekgirlau. Reason: add vba tags

[OBP]

David, why do you have to move the Record, you are on the new Record with the
DoCmd.GoToRecord , , acNewRec

[David627]

It should be on the new record, however it doesn't show it in the form, hense why I added the GoToRecord commands

David

[CreganTur]

For the form that's displaying these records, is the form's Allow Additions property set to Yes?

If this Form is bound to a table, then what are the settings for the Recordset Type and Record Locks?

[David627]

Allow additions is yes
Recordset type=Dynaset
Record locks=no locks

[CreganTur]

Hmmm...:think:

This is just off the top of my head (it's almost quitting time, so my brain's just about fried), but you may need to requery since you're adding in a record. Try adding Me.Requery before you attempt to move to the last record- see if that makes a difference.

HTH:thumb

[OBP]

Manually check if the New record is in the table, in the Form's Query (if there is one) and then Enter a new record using your method and manually search through the Form's records to see where it is Displayed (if at all)
If it is being displayed, but for some reason you are no longer on the record as it was generated you could use the recordset to "find" it and move to it.
Ensure that you are not trying to Update an Autonumber Field with your VBA.

[David627]

I know there is a new record - I open the table. I can manually navigate to it in the form, but that isn't the desired result. The record ID is an AutoNumber field but the VBA isn't replicating it

Me.Requery doesn't work :(

:banghead:

[CreganTur]

Please post an example database that recreates this problem- be sure to use 2003 format, as most don't have 2007.

[OBP]

I have just noticed in your original post that you do not have any error trapping and you have an error, which may or may not be affecting the outcome.
you have
Set re = Nothing
when it should be
Set rs = Nothing

[David627]

Thanks for the catch OBP - unfortunately that didn't remedy....

[CreganTur]

You need to turn Option Explicit on. from the VBA IDE Click tools-> Options-> Require Variable Declaration. This will keep you from making a number of mistakes by forcing you to declare all variables that you use in your code. It would have automatically detected the error that Tony found.

[OBP]

Can you do as Randy asked and post an example