How to update a form control from a msg box response?

StagIraq
StagIraq used Ask the Experts™
on
Experts,

I have worked this for too long, now and so I humbly submit this simple question that eludes me...

I have a list box "listSimDiscrID" that is populated with a limited list from a table.  The information I need form this list is (1) the discrepancy ID and (2) the title of the discrepancy.  I had this list box working fine and it would store the DiscrID to the control data that the list box control was bound to, AND it would store the discrepancy title in the txtSimComments textbox on the form (using the _afterUpdate event and having Me.txtSimComment=listSimDiscrID.Column(2).

However, I had to go and ruin a good thing by trying to question the user if they return to the form and try to change the discrepancy for the event.  I added the following msg box (derived from another EE thread):

Private Sub listSimDiscrID_BeforeUpdate(Cancel As Integer)
  If MsgBox("Do you want to change sim available code?", vbOKCancel) = vbOK Then
       
        Me.SimDiscrID = listSimDiscrID.Column(0)     'this line added to try and get listbox to update
        Me.txtSimComment = listSimDiscrID.Column(2)    'this line always works fine
         
    Else

        Me.txtSimComment = Me.txtSimComment.OldValue
       
    End If
   
End Sub


The problem with above code:  I click on the listbox item and the msg box pops up just fine...I click "OK" in msg box and the comments box lists the new title great...but the listSimDiscrID listbox does not save the change to the bound countrol data until the pop-up is closed?  

If that is not clear, here is an example - - - say there is already a discrepancy for this record on the form (DISCREPANCY#1), and a user clicks on a new discrepancy in the list and says, "ok" (discrepancy #2)...the code does not change the discrepancy until the form is closed!?!  So, if the user then changes his mind and clicks on a different discrepancy in the list (DISCREPANCY#3),and when the msg box asks "do you want to change this discrepancy? he decides, no, he wants to keep it at the previous (DISCREPANCY#2) so he clicks, Cancel on msg box...the comment box reverts back to (DISCREPANCY#1) which leaves me with a discrepancy ID that is not aligned with its correct title?!?

How do I get the listbox to update its value from the "OK"  selection on the msg box?

Long discription/question to what has to be a short answer.

Thanks,
StgIraq
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012

Commented:
Sample database notes:
1. Back up your database(s).
2. Combine the front and back ends into one database file.
3. Remove any startup options, unless they are relevant to the issue.
4. Remove any records unless they are relevant to the issue.
5. Delete any objects that do not relate directly to the issue.
6. Remove any references to any "linked" files (files outside of the database, Images, OLE Files, ...etc)
7. Remove any references to any third party Active-x Controls (unless they are relevant to the issue)
8. Remove, obfuscate, encrypt, or otherwise disguise, any sensitive data.
9. Compile the code. (From the VBA code window, click: Debug-->Compile)
10. Run the compact/Repair utility.
11. Remove any Passwords and/or security.
12. If a form is involved in the issue, set the Modal and Popup properties to: No
    (Again, unless these properties are associated with the issue)
13. Post the explicit steps to replicate the issue.
14. Test the database before posting.

In other words, ...post a database that we can easily open and immediately see and/or troubleshoot the issue.
And if applicable, also include a clear graphical representation of the *Exact* results you are expecting, based on the sample data.

JeffCoachman
LucasMS Dynamics Developer

Commented:
Why dont you just requery the list box?  Me.SimDiscrID.requery
I think your original code in the afteUpdate of the list box will update the description.
If they select by mistake, they can cancel the update (press ESC) before leaving the record.
If you must do this in code, before you leave in the OnCancel code, update the description based on the current ID value.

Author

Commented:
Thanks for the ideas, guys, but it is not working.  However, from today's attempts I might hbave a better description of what is happening.

boag2000, unfortunately, my being in a deployed location I do not have the bandwidth to do an upload to your site; otherwise I definitely would have posted a db to play with.

I have tweaked the code and it now reads:

Private Sub listSimDiscrID_BeforeUpdate(Cancel As Integer)
  If MsgBox("Do you want to change sim available code?", vbOKCancel) = vbOK Then
       
        SimDiscrID = Me.listSimDiscrID
        Me.txtSimComment = listSimDiscrID.Column(2)
       
    Else
       
        SimDiscrID = Me.listSimDiscrID.OldValue
        Me.txtSimComment = Me.txtSimComment.OldValue
       
    End If
       
End Sub

Lucas 911 - adding the requery of the list box did not fix the problem.

DoDahD - It did not make a difference if I placed the code in "after update" or "before UPdate"

Bottom line is this, this code allows one to click on a listbox item, click "OK" in the msg box and the comments text box gets updated with the title, but the listbox is not updating (or "saving" the new ID that was just clicked OK to) until the form is closed.  This means the listbox ID can be "1" and the comment could have the title for "2" on close.

I guess my question should be How do I have the listbox save the new value if OK is clicked in the msg box...because that is not happening.

Thank you,
Stagiraq

Author

Commented:
I have tmeporarily gone the route of diabling the listbox after the user makes a selection.  Not ideal, but the msg box idea was not working and I was getting mismatched data in my database fields.

I was probably over thinking the msgbox but I definitely wasnot getting an listbox update after clicking "OK" in the msgbox.

I'll keep watching this for a couple days to see if any new ideas show up.

Thanks,
Stag
Records are not saved until one moves to a new record or uses the DoCmd.Save statement in VBA.

If you are wanting to commit the change (save the record) after the MsgBox add the command in your code.


Private Sub listSimDiscrID_BeforeUpdate(Cancel As Integer)
  If MsgBox("Do you want to change sim available code?", vbOKCancel) = vbOK Then
       
        SimDiscrID = Me.listSimDiscrID
        Me.txtSimComment = listSimDiscrID.Column(2)
 
    Else
       
        SimDiscrID = Me.listSimDiscrID.OldValue
        Me.txtSimComment = Me.txtSimComment.OldValue
       
    End If

    DoCmd.Save  'saves (stores, commits) the current record into the database      
End Sub
I will add that this prevents a nice feature in Access that the user can 'Esc' out of any changes on a form.  Unlike Excel where one can cancel editing a cell, Access lets you cancel the current field with an 'Esc' key press and cancel all changes to the current record with a second 'Esc' key press.

Author

Commented:
That was what I was looking for!

Thanks,
StagIraq

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial