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.
jonlakeAsked:
Who is Participating?
 
Rey Obrero (Capricorn1)Commented:
try this


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 = [Value], [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
0
 
Boyd (HiTechCoach) Trimmell, Microsoft Access MVPCommented:
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.
0
 
jonlakeAuthor Commented:
I will test this tomorrow (back in the office) and update you with the result.
0
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
Rey Obrero (Capricorn1)Commented:
jonlake,

take note that you don't need to run two update queries, one is enough.
0
 
Boyd (HiTechCoach) Trimmell, Microsoft Access MVPCommented:
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.

0
 
jonlakeAuthor Commented:
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.
0
 
jonlakeAuthor Commented:
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:
 Update
0
 
jonlakeAuthor Commented:
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
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.