Close command button allows form to be closed when required fields are left blank
Lauren,
1. In the general declarations section of your form, define a new
variable (bAllowClose) as boolean
2. In the forms Open event, set this variable to false (bAllowClose =
false)
3. In the forms Unload event, enter the following line:
Cancel = Not bAllowClose
Up to this point what you have done is prevent Access from allowing
the form to be closed, by any method. This is the key to making sure
the user cannot close the form except by pressing your Close button.
You might also want to add a Cancel button that will undo any changes
to the record (me.undo) and then close the form (ensuring that you set
bAllowClose to True before implementing the Close method).
4. In the code behind your Close button, add some error checking code
similiar to below.
Private sub cmdClose_Click()
if me.dirty 'checks to see whether any bound fields have been
changed since the form was opened to this record
If Len(me.txt_text1.value & "") = 0 then
msgbox "Text1 field cannot be blank!"
me.txt_text1.setfocus
exit sub
Elseif Len(me.txt_text2.value & "") = 0 then
msgbox "Text2 field cannot be blank!"
me.txt_text2.setfocus
exit sub
ENDIF
me.dirty = false 'this will force Access to write the
record
endif
bAllowClose = true 'allow the form to be closed
docmd.close acForm, "your form name"
End sub
--
HTH
Dale Fye
****** Avoid Spam **************
To reply directly, modify my email address
************************************
I've created an Access 97 database, running on both Windows 95 and
2000. One of the forms contains fields which are designated in the
underlying table as Required. When a user leaves a field blank and
tries to add a new record, they get the system message about how the
field can't contain a Null value. When they try to close the form
using the X, they first get the system message about Null values, then
they get a system message about how the form will be closed but the
record will not be saved because some fields were left blank.
The problem is this: I added a Close command button using the command
button wizard. When users click the button to close the form, they
don't get any error messages. Access just closes the form without
saving the record. The user is not prompted to fill in required
fields, and they have no idea that the record has not been saved.
I've tried different types of VBA code in various events (such as the
Before Update and Error events) for both the Form and the Close
button, but nothing works.
It seems like it should be simple enough to do. It's not unusual for
fields to be required, and it's not unusual to close forms with a
command button rather than the X.
The Close command button is called cmdClose1. The form is called
frmClassesCoreSchedule.
