How to validate a form when the user closes?

[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

 

[Cheryl Fischer]

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'.

 

[Cheryl Fischer]

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.

 

[David Rose]

Thanks Cheryl,

That sounds like exactly what I am looking for.

David

 

[David Rose]

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

 

[Cheryl Fischer]

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.

 

[Marshall Barton]

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.

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

 

[David Rose]

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