Hi, I have a table with two numeric fields; Value and Adj. Users need to move the value from Value into Adj. They may also want to move the value in Adj into the Value field, but only one field can contain a value at any one time. To complicate matters they need to be able to select multiple records and perform the transfer on a single click. So far, to handle just the first transfer I have come up with:
Private Sub Append_Click()
If MsgBox("This will append the selected value(s) , OK?", vbYesNo + vbQuestion, "Question") = vbNo Then Exit Sub
Dim lIndex As Long
Dim vItem As Variant
Dim Adj_Val As Variant
Dim Val As Variant
For Each vItem In Me!Record_ID.ItemsSelected
Adj_Val = DLookup("Value", "tblP11D_Main", "Record_ID = " & Me!Record_ID.ItemData(vItem))
CurrentDb.Execute "Update tblP11D_Main Set Adj =" & Adj_Val & " WHERE Record_ID = " & Me!Record_ID.ItemData(vItem)
CurrentDb.Execute "Update tblP11D_Main Set Value = 0 WHERE Record_ID = " & Me!Record_ID.ItemData(vItem)
For lIndex = 0 To Me.Record_ID.ListCount
Me.Record_ID.Selected(lIndex) = False
MsgBox "Value(s) appended", vbInformation, "Information"
Record_ID.Selected(lIndex) = True
It fails on the second "CurrentDb.Execute".
All help very much appreciated.