Solved

Check Record Status if Changed Before Navigating To Next Record: Prompt User to Save

Posted on 2007-03-18
33
385 Views
Last Modified: 2012-05-05
MS Access: Have Form with combo box for record lookup.  Want to prompt the user if record status changed before navigating to next record.  How do I check the status of the record before user navigates to another record.  I have a save button, but this is bypassed when then they navigate with the combo box to another record.

-CK
0
Comment
Question by:ckaliveas
  • 12
  • 11
  • 5
  • +1
33 Comments
 
LVL 18

Expert Comment

by:p912s
Comment Utility
>>Navigating To Next Record: Prompt User to Save
When the user moves to the next record the data is automatically saved...

Also, if the form is bound to a table you can use the dirty property of the form.

If Me.Dirty then
    'do something here...
End If
0
 
LVL 75

Expert Comment

by:DatabaseMX (Joe Anderson - Access MVP)
Comment Utility
By 'record status' ... do you mean if the record is being edited? or that some other user has changed the record on another system?
0
 

Author Comment

by:ckaliveas
Comment Utility
Yes.  If the record is being edited on the form.  Oracle is backend and that appears to be managing multi user.  I get a prompt if record is edited by another user.

I put the Me.Dirty on Form.Before Update event and it works.  The issue I am facing is that I also have a "SAVE" button.  The Form.Before Update event is also firing prompting the user with my message box if they want to save the changes.  I prefer not to have this msg box appear as they intentionally hit the "SAVE" button.  Please advise.

Thank you for your help.  

-CK
0
 
LVL 75

Expert Comment

by:DatabaseMX (Joe Anderson - Access MVP)
Comment Utility
Well ... IF ... the record has been edited ... then that IS the Status ... and if you have code in the BU ... then it will trigger.  So, what exactly do you want to get out of you 'checking'.  The Save button is going to save the record ... assuming all required field conditions have been met, etc.  Not really sure what you are trying to accompolish? Sorry.

mx
0
 

Author Comment

by:ckaliveas
Comment Utility
Just don't want the message box I have in the form.before update to fire when the user clicks on the SAVE button.  When they click on the SAVE button now, the message box appears will always appear asking them if they want to SAVE.  I prefer not to have the message box appear when they are clicking the SAVE button I have added using the button wizard.

So...somwhere in the form.before update event..if the user clicked the "SAVE" button, how do I bypass the message box in this event?  Or is there an alternative approach.

-CK
0
 
LVL 18

Expert Comment

by:p912s
Comment Utility
>>form.before update to fire when the user clicks on the SAVE button
Can you post the code for both of the? The for event and the button...
0
 
LVL 75

Expert Comment

by:DatabaseMX (Joe Anderson - Access MVP)
Comment Utility
Well ... it's a 'Catch 22'.  Any code in the BU will execute when you try to save the current record - if ... the record is actually Dirty ... which it will be if any edits have been made.

'Dirty' and 'changed' and 'edited' are synonymous.  So ... when DO you want the msg box to appear ?

mx
0
 
LVL 18

Expert Comment

by:p912s
Comment Utility
Wow! missing some keystrokes there...

Should read

  Can you post the code for both of these? The before event and the button...
0
 
LVL 75

Expert Comment

by:DatabaseMX (Joe Anderson - Access MVP)
Comment Utility
lol :-}
0
 

Author Comment

by:ckaliveas
Comment Utility
I only want the message box to appear if they do not click the "SAVE" button.

-CK
0
 
LVL 75

Expert Comment

by:DatabaseMX (Joe Anderson - Access MVP)
Comment Utility
Well .... some Event will have to run code to trigger the msg box
0
 
LVL 75

Expert Comment

by:DatabaseMX (Joe Anderson - Access MVP)
Comment Utility
I suppose you could use the Form OnDirty event to display the message box ... try that.  OnDirty will trigger as soon as an edit is made.
0
 
LVL 75

Expert Comment

by:DatabaseMX (Joe Anderson - Access MVP)
Comment Utility
In fact ... forget 'I suppose' ... that is what you need to do ... now that I see what you are trying to do ... I think ?

mx
0
 

Author Comment

by:ckaliveas
Comment Utility
Here is my SAVE button code:
Private Sub Save_Click()
On Error GoTo Err_Save_Click


    DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70

Exit_Save_Click:
    Exit Sub

Err_Save_Click:
    MsgBox Err.Description
    Resume Exit_Save_Click
   
End Sub

----
Here is my BU Code:
Private Sub Form_BeforeUpdate(Cancel As Integer)
   If Me.Dirty Then
    If MsgBox("The current record has been updated.  Do you want to update the record?", vbQuestion + vbYesNo) = vbNo Then
        Cancel = True
        Me.Undo
     Else
    LST_UPD_USR_ID.Value = CurrentUser()
    LST_UPD_DT.Value = Now()
    End If
    End If
   
End Sub

--

I guess to bypass this message in BU, I need a variable that I can assign in the SAVE funtion...then check the variable in the BU function in IF statement..... If you can provide this that may be just what I want.

-CK
0
 
LVL 75

Expert Comment

by:DatabaseMX (Joe Anderson - Access MVP)
Comment Utility
Did you try the Form OnDirty Event?
0
 
LVL 75

Expert Comment

by:DatabaseMX (Joe Anderson - Access MVP)
Comment Utility
Use the BU to do input validation ... not to display the kind of message.

Also ... for the 'Save' button ... all you need is:

Me.Dirty = False.
Thank will save the record.

mx
0
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 75

Expert Comment

by:DatabaseMX (Joe Anderson - Access MVP)
Comment Utility
opps .... s/b 'THAT' will save the record
0
 

Author Comment

by:ckaliveas
Comment Utility
I put me.Dirty in SAVE button per below.  I still have the message box in my BU event..do not understand why not to have it there...where should I put it.

Having tested the me.dirty in SAVE button, my BU if statement still thinks it is TRUE.  I still get my message box asking if user wants to save.  My SAVE code below.

Private Sub Save_Click()
On Error GoTo Err_Save_Click
Me.Dirty = False

    DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70

Exit_Save_Click:
    Exit Sub

Err_Save_Click:
    MsgBox Err.Description
    Resume Exit_Save_Click
   
End Sub

-----

0
 
LVL 75

Expert Comment

by:DatabaseMX (Joe Anderson - Access MVP)
Comment Utility
Put your msg box code in the Form OnDirty. This lets the user know an edit has been started - if that's your intent.

Remove all code from the BU - for now.

The code in your Save button needs to be:

Private Sub Save_Click()
    Me.Dirty = False
End Sub

Take out the  DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
Executing Me.Dirty=False will do the Save.


0
 

Author Comment

by:ckaliveas
Comment Utility
I did put the code in the form.dirty but the problem is that on show message once the record has been changed on the first cell I update.

My intent is for the message to appear if user updated record, did not click save to save the record, and attempts to navigate to another record.  

SAVE:
Private Sub Save_Click()
On Error GoTo Err_Save_Click
Me.Dirty = False

'   DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70

Exit_Save_Click:
    Exit Sub

Err_Save_Click:
    MsgBox Err.Description
    Resume Exit_Save_Click
   
End Sub

---

BU
Private Sub Form_BeforeUpdate(Cancel As Integer)
  ' If Me.Dirty Then
   ' If MsgBox("The current record has been updated.  Do you want to update the record?", vbQuestion + vbYesNo) = vbNo Then
    '    Cancel = True
     '   Me.Undo
     'Else
    'LST_UPD_USR_ID.Value = CurrentUser()
    'LST_UPD_DT.Value = Now()
    'End If
    'End If
   
End Sub

-----

Private Sub Form_Dirty(Cancel As Integer)
MsgBox ("The current record has been updated.")
End Sub
0
 
LVL 39

Expert Comment

by:stevbe
Comment Utility
if you only want the prompt if they DO NOT click the save record then make a miodule level variable that gets set BEFORE the record save in the buton click. You will need to reset that variable in the form current event ...

Private mblnSaveClick As Boolean

Private Sub Form_Current()
    mblnSaveClick  = False
End Sub
Private Sub cmdSave_Click()
    mblnSaveClick = True
    If Me.Dirty = True Then
        Me.Dirty = False
End Sub
Private Sub Form_BeforeUpdate(Cancel As Integer)
    If mblnSaveClick = False Then
        If Msgbox("Do You want to save?", vbYesNo) vbNo Then
            Me.Undo
        End If
    End If
End Sub

Steve
0
 

Author Comment

by:ckaliveas
Comment Utility
I tried the recommendation above...but when I altered a field on the form and did not click save and navigated to another record...I did not get the msge.  

- Opened Form
- Altered Field
- Clicked Save
-Moved to another record
- Altered Field
- Moved to another record
>> Problem is "NO Message Appeared to prompt User to SAVE"

My code below:
---
Option Compare Database

Private mblnSaveClick As Boolean
---

Private Sub Save_Click()
    mblnSaveClick = True
    If Me.Dirty = True Then
        Me.Dirty = False
    End If
End Sub

-----
 If mblnSaveClick = False Then
        If MsgBox("The current record has been updated.  Do you want to update the record?", vbQuestion + vbYesNo) = vbNo Then
        Cancel = True
        Me.Undo
        Else
            LST_UPD_USR_ID.Value = CurrentUser()
            LST_UPD_DT.Value = Now()
        End If
    End If
---

Private Sub Form_Current()
  mblnSaveClick = False
End Sub
0
 

Author Comment

by:ckaliveas
Comment Utility
I added the following code and it appears to be working now.  Any thoughts/concerns?

Private Sub Form_Dirty(Cancel As Integer)
mblnSaveClick = False
End Sub
0
 
LVL 39

Expert Comment

by:stevbe
Comment Utility
the BeforeUpdate should not fire unless a change was made so I don't see why you need to force mblnSaveClick to be false in the Form_Dirty, that is what is already happening in the Form_Current. The only issue I can see is that if you dirty the form but then undo those chnages you may still get prompted.

Steve
0
 

Author Comment

by:ckaliveas
Comment Utility
I added a piece of code to my BU which checks field value is correct and if not, then I cancel the save.

Private Sub Form_BeforeUpdate(Cancel As Integer)

If Me.AVAIL_FROM_SOURCE_TIME_REFMT > 23.99 Then
MsgBox ("Available From Source Time cannot exceed 23.99 hundred hours military time.  Please enter time of day (i.e 2:30 PM = 14.50)")
Cancel = True  >>>>>>> I just added this code to stop record from saving....after I added it, I get an error message below

Private Sub Save_Click()
    mblnSaveClick = True
    If Me.Dirty = True Then
        Me.Dirty = False  >>>>> errors out here!!!!!
    End If
End Sub
0
 
LVL 39

Expert Comment

by:stevbe
Comment Utility
I image that is the 2501 error ... "action was cancelled" that is what you want to happen if they need to fix the data so you can just capture and bury that error

Private Sub Save_Click()
On Error GoTo ErrHanlder
    mblnSaveClick = True
    If Me.Dirty = True Then
        Me.Dirty = False  >>>>> errors out here!!!!!
    End If

ExitHandler:
    Exit Sub

ErrHandler:
    Select Case Err.Number
        Case 2051    'cancelled because invalid record caught in BeforeUpdate
                             'do nothing
        Case Else
            Msgbox Err.Number ": " & Err.Description
            'you should have a central error log function to call here.
    End Select
    Resume ExitHandler
End Sub
0
 

Author Comment

by:ckaliveas
Comment Utility
I do not have a central error log to capture....novice here.  Can you provide one please?

-CK
0
 

Author Comment

by:ckaliveas
Comment Utility
The error is a '2101' error message.  "The setting you entered is not valid for this property".
0
 
LVL 39

Expert Comment

by:stevbe
Comment Utility
Ok ... the 2051 I posted should have been a 2501 (blame it on typo or old age, thake your pick:-)

The 2101 I *think* really amounts to the same thing, just bury it. As for a central error handler, you need to decide where you want to log to, local db, back end db, a sperate error loggin db, a text file, your system events, what ever and then you need some code to push it all there. I think that would be another question entirely. For the question today, I think the message box should suffice.

ErrHandler:
    Select Case Err.Number
        Case 2501    'cancelled because invalid record caught in BeforeUpdate
                             'do nothing
        Case 2101    'invalid property, you can't save a record that has been cancelled
                             'do nothing
        Case Else
            Msgbox Err.Number ": " & Err.Description
            'you should have a central error log function to call here.
    End Select
    Resume ExitHandler
End Sub


Steve
0
 
LVL 18

Accepted Solution

by:
p912s earned 125 total points
Comment Utility
ckaliveas - are you still having a problem?
0
 
LVL 39

Expert Comment

by:stevbe
Comment Utility
<ckaliveas>
While I consider my solution to be very easy and does address your concern about writing code in all the your controls afterupdate events, it is obvious that you could care less about anything offered because you just accepted a post that was only asking where you were with this question.

Good luck,
Steve
0
 
LVL 75

Expert Comment

by:DatabaseMX (Joe Anderson - Access MVP)
Comment Utility
So much effort for free ... so little reward sometimes.  Geeze !!!
I didn't even know you could accept your own answer !!

mx
0
 
LVL 18

Expert Comment

by:p912s
Comment Utility
ckaliveas - can you chime in here and explain why my question (which was merely a follow-up) was accepted as the answer? Perhaps one of the many other comments was actually the answer?

>>I didn't even know you could accept your own answer !!
mx - I'm confused by this statement?
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now