Fields not queried in same order as Access table-CodePudding

I have two simple VBA functions in MS Access to copy and paste an entry. However, when I copy the entry, the fields are not in the same order. I have ten fields in the access table, ordered from 1-10, but when the data is copied is ends up 1-8,10,9. The field in position 9 was newly added field so my thought is that there is a field index and its ID is actually 10 instead of 9, but I see no place to change that.

Unfortunately, I'm no expert and also not the one who built this Access database so I am hesitant to change to much about the code for risk of breaking other things.

Here is the copy function for reference:

Private Sub Copy_Click()
Dim RS As DAO.Recordset, i As Integer
Set RS = Me.Recordset
With RSC
    .FindFirst "[User] = '" & Active_User & "'"
    If .NoMatch Then
        .AddNew
    Else
        .Edit
    End If
    For i = 0 To .Fields.Count - 1
        .Fields(i).Value = RS.Fields(i).Value
    Next
    .Update
End With
Me.Paste.Enabled = True
End Sub

Any help is much appreciated.

CodePudding user response：

Not sure how to force Access to use a different field order, but if source and destination have the same columns, access the fields via name.

For i = 0 To .Fields.Count - 1
    fieldName = RS.Fields(i).name
    .Fields(fieldName).Value = RS.Fields(fieldName).Value
Next

CodePudding user response：

If the field in question is a numeric field, you can place it in the ORDER BY section, e.g i have a field called RESID, it is a numeric field, but not a primary key.

SELECT 
Table1.Rente_RES, Table1.Montant_Capital_Consitutif_N, Table1.Rente_RESID
FROM Table1
ORDER BY Table1.RESID;