Urgent! How to deal with Save, Cancel, Exit and data validation

[Elizabeth]

My form requires that all fields are completed in order
for the user to save the record and exit. The form has 3
command buttons:

cmdSave
cmdCancel
cmdExit

I'm having a bear of a time figuring out the logic for all
of the scenarios that involve these buttons:

cmdSave should be disabled until all the form fields are
complete. What is the command to do this and where does it
go? I suspect it has something to do with Dim.?

User click cmdSave, all the fields are complete, then
clicks cmdExit, the form closes and all is right with the
world.

However, if the user clicks cmdSave, then changes a field
(s), then clicks cmdExit, a message box should come up
asking if they want to cmdSave or just cmdExit without
saving the changes. Or, perhaps they just want to forget
about adding the record (cmdCancel and/or cmdExit).

If the user clicks cmdSave, changes a field to IsNull
(which is a no-no), then clicks cmdExit, they should be
prompted to complete all the fields, forget about the
changes or cancel adding the record (cmdCancel and
cmdExit). If any of the fields IsNull after, cmdSave
should be disabled.

Then there's the situation when the user clicks cmdCancel
or cmdExit without entering any information, which would
mean they decided not to add to the table at all.

I've tried all sorts of data validation exercises on the
three command buttons, but the number of lines of code is
mind-boggling, very messy, and almost always gets cocked
up!

It all seems really rather silly since Save is a moot
point because the information is being written into the
table anyway, but I think users like the security and
familarily of seeing a Save command in programs.

Can anyone advise me how to deal with all this with some
sample code (please know that I'm a Newbie).

Many thanks,
Elizabth

 

[Allen Browne]

Access automatically saves the record when the form is closed, you move to
another record, the user presses Shift+Enter or chooses Save from the
Records menu, or filters or sorts the form or several other scenarios.

The only way you can programmatically catch all of those is to use the
BeforeUpdate event procedure of the form.

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim strMsg As String

If IsNull(Me.SomeField) Then
Cancel = True
strMsg = strMsg & "SomeField required." & vbCrLf
End If
If IsNull(Me.AnotherField) Then
Cancel = True
strMsg = strMsg & "AnotherField required." & vbCrLf
End If
'etc.
If Cancel Then
strMsg = strMsg & "Correct the entry, or press <Esc> to undo."
MsgBox strMsg, vbExclamation, "Invalid entry."
End If
End Sub


Now, you can program your other buttons:

Private Sub cmdCancel_Click()
If Me.Dirty Then
Me.Undo
End If
End Sub
Private Sub cmdSave_Click()
If Me.Dirty Then
Me.Dirty = False
End If
End Sub
Private Sub cmdExit_Click()
If Me.Dirty Then
Me.Undo
End If
DoCmd.Close acForm, Me.Name, acSaveNo
End Sub

 

[Neil]

Hello Elizabeth,

Here are some pointers to help you:

1) When you are in design view for your form, Double Click on the square box
located in the top left corner (wher the horizontal and vertical rulers
meet). This will bring up the properties for your form.

2) The form has a lot of events (Click the Event tab). A few usefull events
for you to code would be the 'Before Update', 'After Update', 'Undo' and
'Dirty' events. The before update event will fire just before Access is
about to save changes to a record (usefull for checking that all the
required fields have been completed successfully). The After Update event
will fire after Access succesfully saves the record (usefull for updating
the command buttons). The dirty event will fire as soon as a record is
modified (usefull for updating your command buttons). The Undo event will
fire when changes to the form are undone (usefull for updating the command
buttons).

3) Every control on your form has a Tag property (right click on the control
and Click Properties then click on the 'Other' tab). If you enter a unique
value (in my example below, you would enter 'Required Field' into the tag
property for each required field) into here you can then loop through the
controls and check for anything you like. For example, the Before Update
event for your form may look like the following:

Private Sub Form_BeforeUpdate(Cancel As Integer)

Dim ctl As Control
Dim intMsgResponse As Integer
' Loop through all the controls on the form
For Each ctl In Me.Controls
' Check the tag property
If ctl.Tag = "Required Field" Then
' Check this controls has a value
If ctl = "" Or IsNull(ctl) Then
' No Value - Cancel the update event
Cancel = True
End If
End If
Next
' Check to see if a required field was blank and inform the user
If Cancel = True Then
' Display message
MsgBox "A required Field is still left blank. Please fill in all
required fields and try again"
Else
' Ask the user if they want to save the changes
intMsgResponse = MsgBox("Do you want to save the changes you have
made to the current record?", vbYesNo + vbQuestion,"Save Changes?"
' Check the response
If intMsgResponse = vbNo Then
' Cancel the update event
Cancel = True
' Undo the changes made to the form
Me.Undo
End If
End If

End Sub

The Dirty event (and After Update and Undo events) could disable/enable the
exit button but disable/enable the save button. For e.g.

Private Sub Form_Dirty(Cancel As Integer)

' Enable the save button
Me.cmdSave.Enabled = True
' Disable the Exit button
Me.cmdExit.Enabled = False

End Sub

4) Set the .Enabled property for the command buttons so that the user has to
click Save before they can click Exit.

Hopefully there is enough here to help you get on your way.

Good luck!

Neil.

 

[Elizabeth]

Dear Allen and Neil,

Thanks so very much for your assistance. I'll have a go
with your suggestions this afternoon. I've always wondered
what to do with Tags!

Cheers,
Elizabeth

 

[Elizabeth]

Dear Neil,

Yesterday and well into the night I have tried to
implement your code and pointers, with dismal results.
The only thing I was able to figure out was that the.

If Cancel = True Then
' Display message
MsgBox "A required Field is still left blank.
Please fill in .

..works only if the Cancel button is initially enabled.
However, I can't figure out how to tell it not to exit
the form unless all the fields are complete without
restating all the code that looks for IsNull fields.

I think the problem is this: The user might click cmdExit
and want to literally cancel adding anything to the
table, in which case cmdExit would close the form rather
than simply cancelling changes to the record. Right now,
if the user clicks cmdExit, the prompt to fill in all the
fields is displayed and then it hits the DoCmd.CloseForm.
IsDirty and AfterUpdate have also done my head in. I feel
so thick. Would you mind providing some more assistance
with the code and/or can you recommend someplace where I
can see all this actually put into practice?

Thanks for your patience,
Elizabeth

 

[TC]

I haven't read all of this thread, but, you need to know that there is >no
way< you can have a properly functioning Cancel button on a bound form.

Say the user changes a bound field in an illegal way; eg. by erasing a
mandatory field, or entering a duplicate primary key value. When the user
tries to leave the field (or save the record), an error message will appear.
Say the user then thinks, "I give up, I'll cancel these unsaved changes & go
do something else".

Problem: you can not click the Cancel button until you can move the focus
out of the illegal field. But, you can not move the focus out of that field
until you (a) correct the value, or (b) press Esc to discard the unsaved
change. And having to do (a) or (b), completely negates the purpose of
having the Cancel button in the first place!

For that reason, I never provide a Cancel button on bound forms. It offers
more than it can ever deliver. If there are any underlying validations on
the bound form - eg. from table primary keys - there is >no way< you can
make a command button cancel arbitrary unsaved changes.

This is avoided in certain other products (eg. Oracle Forms), which let you
define a command button as "not taking the focus". You can click such a
button >without< removing the focus from whatever field it is currently in.
Such a button >can< discard all unsaved changes. But Access does not have
this feature for buttons.

HTH,
TC

 

[Neil]

Hello Elizabeth,

I should have mentioned that I hadn't tested this code so that could be a
possible reason as to why you can't get your code to work. Also, the code
was just an example, it may not be exactly what you need, but would
hopefully have given you ideas on how you could code your form. I think I
see what the problem is though. Are you sure your command button is named
cmdCancel? The Cancel I refere to is the Variable that is passed into the
Sub (declared at the top Pivate Sub... (Cancel As Integer), not the cancel
button on the form. The sample code I gave you would check that each field
has a value - not that the value was of the correct format. I agree with
TC's reply with regards to the Cancel button and I am not too sure as to
what other problems you may have using this. If you were to post the code
that you have then i (and other people) could help you out better. The Exit
button code could be something like:

Dim intResponse As Integer
' Check to see if the form is dirty
If Me.Dirty Then
' Inform user that changes will be lost
intResponse = MsgBox("The record has not been saved. Are you sure
you want to exit and lose all changes made?", vbYesNo + vbQuestion,"Exit?"
' Check the response
If intResponse = vbYes Then
' Undo the changes and close
Me.Undo
DoCmd.Close acForm, Me.Name
End If
Else
' Close the form
DoCmd.Close acForm, Me.Name
End If

I think you would also need to disable the close button on the form as
clicking on this would bypass all your buttons.

Neil.