?
Solved

Question on command button Exit code

Posted on 2007-10-02
9
Medium Priority
?
726 Views
Last Modified: 2013-11-28
I have a small snag in my command Exit code.
If a user enters data, clicks exit and chooses to save the data, BUT a mandatory field isn't completed, the form just exits without saving. In this case I will do better to cancel the exit and flag up the mandatory fields.
In addition, because the mandatory field isn't completed, record should not save until the mandatory field is completed. This way focus will remain on the mandatory function.

My current Mandatoryfields function works. However, In order to avoid pasting too many code on this post, the MandatoryFields Function can be found at the link below:

I will appreciate assistance in editing this code to perform better and expected.
' *********
Here is my current code:
Private Sub btnExit_Click()
On Error GoTo ErrRtn
Dim txStr As String
If Me.Dirty Then
  txStr = "Do you want to save the new data?"
  Select Case MsgBox(txStr & vbCrLf & _
    "(Cancel to return to the form.)", vbYesNoCancel)
  Case vbYes
    If Not MandatoryFields Then ' < ----- Checks for missing fields
        Exit Sub
    End If
    DoCmd.RunCommand acCmdSave
  Case vbNo
  Forms!frmMain.Undo
  GoTo Finish
  Case vbCancel:
    GoTo Finish
  End Select
End If
DoCmd.Close
Finish:
    Exit Sub
ErrRtn:
    MsgBox err.Description
    Resume Finish
End Sub
0
Comment
Question by:billcute
  • 4
  • 4
9 Comments
 
LVL 10

Expert Comment

by:slamhound
ID: 20003247
The best method to use would be to set the Required setting of the fields in question to "Yes". This way Access will take care of all the issues of the manditory fields. This means it won't save the record if this field is empy and will make the user choose to either enter the right information or quit the page without saving.
0
 
LVL 11

Accepted Solution

by:
Angelp1ay earned 2000 total points
ID: 20004642
If you want to use your function for checking mandatory fields (this might allow you to perform more complicated checks) then the code you posted should be fine. I've rearranged it a bit and tidied it up a little for you below:

    Private Sub btnExit_Click()
        On Error GoTo ErrRtn
       
        Dim txStr As String
        If Me.Dirty Then
            ' User has entered new data
            txStr = "Do you want to save the new data?"
            Select Case MsgBox(txStr & vbCrLf & _
              "(Cancel to return to the form.)", vbYesNoCancel)
                Case vbYes
                    If Not MandatoryFields Then
                        ' MandatoryFields sub has hilighted missing mandatory data
                        ' Exit sub and let them correct
                        Exit Sub
                    End If
                    ' Data is ok, save it
                    DoCmd.RunCommand acCmdSave
                    ' Exit form
                    DoCmd.Close
               
                Case vbNo
                    ' Undo changes
                    Forms!frmMain.Undo
                    ' Exit form
                    DoCmd.Close
               
                Case vbCancel:
                    ' Exit sub and return to form
                    Exit Sub
            End Select
        End If

    btnExit_Exit:
        Exit Sub
    ErrRtn:
        MsgBox err.Description
    End Sub
0
 
LVL 11

Expert Comment

by:Angelp1ay
ID: 20004669
Urgh, missed a bit - swap "End If" just after the select for this:

        Else
            DoCmd.Close
        End If

Which closes the form on pressing exit if no changes have been made.
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 4

Author Comment

by:billcute
ID: 20010627
slamhound,
Thanks for responding, I will rather like to work and improve my current code as posted. If you could assist with it, it will be great.

Regards
Bill
0
 
LVL 4

Author Comment

by:billcute
ID: 20010683
Angelplay:
I tested your suggested "btnExit" code,  there are still problems with it..

       (b).  Problems:
      When user add data on the form and clicks the btnExit; but the mandatory fields are not completed; user responds "Yes"  to a msgbox question to save data.

        ....... missing fields flagged as expected but saved the partially filled or incomplete data - (this should not be so); and further, the main form closed unrequested

It seems that the "Exit Sub" did not work here as expected or something else is still not synchronising in the code.

Regards
Bill
0
 
LVL 11

Expert Comment

by:Angelp1ay
ID: 20012506
Bill, please amend your code to the below and repeat your actions. Then please post which msgboxes you see :o)

    Private Sub btnExit_Click()
        On Error GoTo ErrRtn
       
        Dim txStr As String
        If Me.Dirty Then
            ' User has entered new data
            txStr = "Do you want to save the new data?"
            Select Case MsgBox(txStr & vbCrLf & _
              "(Cancel to return to the form.)", vbYesNoCancel)
                Case vbYes
                    Msgbox ("Case YES")
                    If Not MandatoryFields Then
                        ' MandatoryFields sub has hilighted missing mandatory data
                        ' Exit sub and let them correct
                        Msgbox ("Mandatory Fields not complete - exit sub")
                        Exit Sub
                    End If
                    Msgbox ("Data is ok, save it")
                    ' Data is ok, save it
                    DoCmd.RunCommand acCmdSave
                    ' Exit form
                    DoCmd.Close
               
                Case vbNo
                    Msgbox ("Case NO")
                    ' Undo changes
                    Forms!frmMain.Undo
                    ' Exit form
                    DoCmd.Close
               
                Case vbCancel:
                    Msgbox ("Case CANCEL")
                    ' Exit sub and return to form
                    Exit Sub
            End Select
        Else
            DoCmd.Close
        End If

    btnExit_Exit:
        Exit Sub
    ErrRtn:
        MsgBox err.Description
    End Sub
0
 
LVL 4

Author Comment

by:billcute
ID: 20012824
Angelplay:
I have just tested your last posted code and here are the results.
(a).
With MandatoryFields = True  '---->>>"removed" from my main function,
                                                             I added just one data into my main form then
                                                             clicked the btnExit, , then clicked "yes"
                                                              to save record, I received the msgbox below.
Case Yes
Mandatory Fields not complete - Exit Sub.
Note:
I checked the bounded table, the single record I added was saved in the bounded table.

(b).
With MandatoryFields = True  '---->>>"Not removed" from my main function,
                                                             I added just one data into my main form then
                                                             clicked the btnExit, , then clicked "yes"
                                                              to save record, I received the msgbox below.
Case Yes
Data Ok - Save it.
Note:
Again, I checked the bounded table, the single record I added was saved in the bounded table.

What I have done was to check in addition as to whether or not editing my mandatoryfields function has anything to do with the "btnExit" code problem and as we can see the tests above proved that it did not  make any difference with what was happening at "btnExit".

Regards
Bill
0
 
LVL 11

Expert Comment

by:Angelp1ay
ID: 20013226
Well the if statement seems to be responding correctly. It appears something is forcing the save other than the line DoCmd.RunCommand acCmdSave. I'll have a think :o)
0
 
LVL 4

Author Comment

by:billcute
ID: 20044587
Angelp1ay:
Your code works fine. I manipulated my function a little.

Regards
Bill
0

Featured Post

Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

830 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