How to update a form control from a msg box response?
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=listSimDi
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_BeforeUpdat
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
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=listSimDi
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_BeforeUpdat
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
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.
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.
ASKER
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_BeforeUpdat
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
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_BeforeUpdat
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
ASKER
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
ASKER
That was what I was looking for!
Thanks,
StagIraq
Thanks,
StagIraq
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