Access VBA: Array values changing when record is changed

Question

I have a piece of code that is meant to duplicate a record when executed but instead it is creating a blank record.

I have managed to track the bug to the array I was using to hold the field values from the old record. It seems that when .AddNew was run the array holding the values would change all its values to match this new record, in other words making them all null or 0.

Here is the code I am using:

With rs1
    .MoveLast
    .MoveFirst
    Do Until .EOF
        rs2.FindFirst "ItemID = " & !ItemID
        rs2.Edit
        rs2!ItemQuantity = rs2!ItemQuantity - !OrderQuantity
        TempArray = Array(rs2!ItemCode, rs2![Dia/Size], rs2!ItemType, rs2!Description, rs2!ItemAdded, rs2!ItemSaleValue, rs2!ItemBuyValue, rs2!ItemLocation, rs2!ItemCondition, rs2!ItemOrigin, rs2!ItemOriginDescription, rs2!ItemHistory)
        rs2.Update

        rs2.AddNew
        rs2!ItemCode = TempArray(0)
        rs2![Dia/Size] = TempArray(1)
        rs2!ItemType = TempArray(2)
        rs2!Description = TempArray(3)
        rs2!ItemAdded = TempArray(4)
        rs2!ItemSaleValue = TempArray(5)
        rs2!ItemBuyValue = TempArray(6)
        rs2!ItemLocation = TempArray(7)
        rs2!ItemCondition = TempArray(8)
        rs2!ItemOrigin = TempArray(9)
        rs2!ItemOriginDescription = TempArray(10)
        rs2!ItemHistory = TempArray(11)
        rs2!ItemBooked = True
        rs2!ItemBookedBy = Me.OrderBy
        rs2!ItemBookedWhen = Now()
        rs2!ItemBookedNote = Me.OrderID
        rs2.Update
        .MoveNext
    Loop
End With

Here is where the record-sets reference to:

• rs1 is the items in the current order
• rs2 is the stock table/query

TempArray is the array I'm having trouble with so if someone could point out a way to stop it changing or a better way to do this it would be much appreciated.

Answer 1

This is a common mistake. You are adding the recordset's field objects to the array instead of the recordset field values.

Here is the code fix:

TempArray = Array(rs2!ItemCode.Value, rs2![Dia/Size].Value, rs2!ItemType.Value, rs2!Description.Value, rs2!ItemAdded.Value, rs2!ItemSaleValue.Value, rs2!ItemBuyValue.Value, rs2!ItemLocation.Value, rs2!ItemCondition.Value, rs2!ItemOrigin.Value, rs2!ItemOriginDescription, rs2!ItemHistory.Value)