copying and moving records

[clueless]

With the help of this message board, my code is working almost like it
should; however, right now, when the new record is placed immediately
before the copied record, i.e. if you copy the 6th record in the
database, the new record becomes the 6th record and the record that was
copied becomes the 7th (this issue is different than the numbering
issued that I'm about to describe). I would like the form to go to the
new record and I would like the new record to be listed as the last one
in the table. Now for the numbering issue. The primary key, LoanID,
seems rather inconsistent. I have it setup in Access an autonumber,
but when I copy a record, the new record gets the copied record's
LoanID and the old record gets a brand new LoanID. The new LoanID
jumps all over the place. My original thougth was that the new record
would get the next incremental LoanID based on the autonumber feature
of the primary key. Finally, when I copy a record, I cannot make a
copy of the copy. When Access tries to perform this operation, I get
the following error message: "No current record." Can anyone help me
on this one? My code is outlined below. Thanks.

Private Sub ContinueCopy_Click()

Dim rstFrom As DAO.Recordset
Dim rstTo As DAO.Recordset
Dim i As Integer
Dim IngNewID As Long

If IsNull(Me.txtLoanNameChange) And IsNull(Me.txtClientNameChange)
Then
MsgBox "Please update at least one of the two fields in the
form."
Exit Sub
End If

Set rstFrom = Forms!Deals_Rework.Recordset
Set rstTo = Forms!Deals_Rework.RecordsetClone

rstTo.AddNew

For i = 1 To rstFrom.Fields.Count - 1

rstTo(i) = rstFrom(i)

Next i

IngNewID = rstTo(0) 'get the new loan ID

If IsNull(Me.txtLoanNameChange) = False Then
Forms!Deals_Rework.txtLoanName = Me.txtLoanNameChange
End If

If IsNull(Me.txtClientNameChange) = False Then
Forms!Deals_Rework.txtClientName = Me.txtClientNameChange
End If

DoCmd.Close

rstTo.Update 'move form to new record

rstFrom.Requery 're-load record set to show new record added
rstTo.FindLast "LoanID = " & IngNewID

Set rstFrom = Nothing
Set rstTo = Nothing

End Sub

 

[Rick B]

The order of records in a table is irrelevant.

If you want items sorted in a particular order on your queries, forms, or
reports, then you need to include a field that can be used for this.

An autonumber should not be used for anything meaningful. Autonumbers are
used to create a unique number.

 

[Rick B]

One more thought. There are several ways to build an incrementing field.
Here is one method...

Use a form for data entry. In that form, for the control that is bound to
the field to be incremented, set the Default Value property to this:

=Nz(DMax("NameOfField","NameOfTable"),0)+1

This will put the number in the control (when you create a new record) that
is the next sequential number for the table. The user can modify that value
in the form if desired.

 

[clueless]

Rick,

I'm with you. The autonumber is not used for anything meaningful
(other than to provide a unique identifier for each record in the
corresponding table). I was just wanted the data organized in a
certain way within the table. It's really not a big dea.

Do you have any insight on the last problem that I mentioned?
"Finally, when I copy a record, I cannot make a copy of the copy. When
Access tries to perform this operation, I get the following error
message: "No current record."

Thanks for your help.

 

[clueless]

I have tried refresh and requery and I still cannot use the button to
make a copy of the copied record without closing the form and starting
over from scratch. Any other ideas? Please see my code from the
original post above. thanks.