asked on

Update Field to Zero

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)

Next
For lIndex = 0 To Me.Record_ID.ListCount
Me.Record_ID.Selected(lIndex) = False
Next
MsgBox "Value(s) appended", vbInformation, "Information"
Record_ID.Requery
Record_ID.Selected(lIndex) = True

End Sub

It fails on the second "CurrentDb.Execute".

All help very much appreciated.

ASKER CERTIFIED SOLUTION

Rey Obrero (Capricorn1)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Boyd (HiTechCoach) Trimmell, Microsoft Access MVP 2010-2015

First: Value is a reserved word (a property) and should not be used for your own object names.

Try:

CurrentDb.Execute "Update tblP11D_Main Set tblP11D_Main.[Value] = 0 WHERE Record_ID = " & Me!Record_ID.ItemData(vItem)

Open in new window

Note:

... MsgBox("This will append the selected value(s) , OK?" ...

Open in new window

This is not accurate. You are updating not appending. There is a huge difference.

jonlake

ASKER

I will test this tomorrow (back in the office) and update you with the result.

Rey Obrero (Capricorn1)

jonlake,

take note that you don't need to run two update queries, one is enough.

Boyd (HiTechCoach) Trimmell, Microsoft Access MVP 2010-2015

capricorn1,

That is a great point. You can update (not append) two fields at the same time in an update query. That is a lot more efficient since you only have to process each record once. This also become more important in a multiple user application.

jonlake

ASKER

Capricorn1, your solution works well though I didn't mention one important thing. The records are within a multi-select (simple) listbox. I need users to be able to select multiple records and then perform the function.

If the value field is not null (or greater than 0) append it to the Adj field, or vice versa.

jonlake

ASKER

A picture may help explain what I am trying to do. If the Value field has a value then it can be updated to the Adj field. If the Value field has a value then it can be updated to the Adj field:

jonlake

ASKER

I customised it as you might expect and now have this, which works well. I'm learning, if a little slowly, and really do appreciate all the help:

Private Sub Append_Click()

If MsgBox("This will update the selected value(s) , OK?", vbYesNo + vbQuestion, "Question") = vbNo Then Exit Sub
Dim lIndex As Long
Dim vItem As Variant
Dim Val As Variant

For Each vItem In Me!Record_ID.ItemsSelected

If IsNull(DLookup("Value", "tblP11D_Main", "Record_ID = " & Me!Record_ID.ItemData(vItem))) Then
MsgBox "No update possible", vbInformation, "Information"
Forms!frmUpdate!Record_ID.SetFocus
Exit Sub
End If

If Not IsNull(DLookup("Value", "tblP11D_Main", "Record_ID = " & Me!Record_ID.ItemData(vItem))) Then
CurrentDb.Execute "Update tblP11D_Main Set Adj = [Value], [Value]=Null WHERE Record_ID = " & Me!Record_ID.ItemData(vItem)
Else
Exit Sub
End If
Next
For lIndex = 0 To Me.Record_ID.ListCount
Me.Record_ID.Selected(lIndex) = False
Next
MsgBox "Value(s) updated", vbInformation, "Information"
Record_ID.Requery
Record_ID.Selected(lIndex) = True

End Sub