How to validate a form when the user closes?

  • Thread starter Thread starter David Rose
  • Start date Start date
D

David Rose

There are several fields on a form that must be filled in by the user. When
the user closes the form, I would like to check these fields to make sure
that they have values. There is an event that fires before closing and from
the documentation it seems that it is called QueryClose (for a "user form" -
whatever that is). I cannot see anywhere where Access exposes this event.
It is not in the event list.

How can I validate fields on closing?

Thanks.

David
 
Presuming that the controls on your form are bound to fields in an Access
table, you can let the table's design do the validating for you in this case
to make sure that the fields will always contain a value. Open the table in
design view and click on one of the fields which must be filled. Then, look
in the lower left corner of the design window at the field's properties.
Change the Allow Zero Length property from 'Yes' to 'No'.
 
Hi Cheryl,

I have existing records with NULLs in these fields. Does this affect the
functionality?

When I change the Allow Zero Length to "No" it does not make a difference.
When I also make the field "Required" it will fire an error dialog when the
user does not enter data in that field, but this is very messy.

I am not used to Access, but normally when writing code I would handle the
event that is fired before the window actually closes, check the field
values, and cancel the close, pop up an error dialog, and set the focus to
the required field. Is there no way to do this with Access?

David
 
Hi David,

Yes, you can do this using VBA code if you prefer not to use Field
properties in a Table.

If the record is an existing one and is being edited, use the Form's Before
Update event to do your validation (something like the following untested
(air) code:

If IsNull(Me!MyRequiredField) or Len(Trim(Me!MyRequiredField))=0 then
MsgBox "RequiredField must have a value."
MeMyRequiredField.SetFocus
Cancel = True
End If

If the record is a new one being added, use the Form's Before Insert event
to do the validation.
 
Hi Cheryl,

That actually didn't quite do it either. The events do not fire at the best
time and there is still no way to cancel the close.
I read somewhere that Access has a QueryClose handler that can be used to
validate before closing the window. I cannot find it anywhere. It seems
like such a simple thing for them to handle the WM_CLOSE message, allowing
validation. I do not understand why they do not have it.

I have a "Close" button, and I can put the validation code in the handler.
If the user closes the window in another way, the field just does not get
filled in.

Thanks for your help. I am slowly learning about Access and you gave me a
few things to use.

David
 
You can get information on QueryClose in Access Help (in Access 2002,
anyway) by opening any code module and clicking Help from the VBA menu. Use
the Answer Wizard and type queryclose in the textbox. In addition,
there are a few articles relating to its use in Access that can be reviewed
in Google. Go to http://groups.google.com enter the following:

"queryclose" group:microsoft.public.access.*

in the search box and click the Search button.
 
David said:
Hi Cheryl,

That actually didn't quite do it either. The events do not fire at the best
time and there is still no way to cancel the close.
I read somewhere that Access has a QueryClose handler that can be used to
validate before closing the window. I cannot find it anywhere. It seems
like such a simple thing for them to handle the WM_CLOSE message, allowing
validation. I do not understand why they do not have it.

I have a "Close" button, and I can put the validation code in the handler.
If the user closes the window in another way, the field just does not get
filled in.
Click to expand...


Try using the form's Unload event to trap the form's being
closed. At least it has a Cancel argument.
 
Cheryl,

I have seen the documentation - it should be very simple to use. I just
cannot find it in Access.

To handle events, I have been using the Properties dialog in design mode.
Just selecting the event and clicking on the nice little button. Access
creates the function and pops me into the code editor.

But, how do you write an event handler in Access when the event does not
appear in the Events list?

David
 
Back
Top