Access VBA On Error, print msg, DON'T close form

[MushMouse]

I have a form that requires company name to be entered. When the user closes the form using the x in the upper right corner, if no company name was entered, some automated messages appear. I'd like to print my own message & return the user to the form without losing any info that they entered, and with the cursor on the company name field. I DON'T want the form to close, until after the company name has been entered, or the user has indicated that he/she wants to cancel this transaction.

Can anyone help me with this?

Thanks in advance!

 

[BusMgr]

Why don't you set the border style on the form to none, removing the ability of the user to use the X to close the form; create your own buttons to close the form and then write the error checking code to see that the user has input the desired info before he or she can close the form.

 

[MushMouse]

I had showed that option to my users. I thought it was great, but they want to be able to leave their windows in the same way throughout their Microsoft systems. Is there any way to do it if they leave through the X?

 

[nice95gle]

Leave your form the way it is just add the following code to your Form Unload event

Private Sub Form_Unload(Cancel As Integer)

If IsNull(Me!YOUR FIELD NAME) = True Or Me!YOUR FIELD NAME = "" Then
Msgbox "YOUR MESSAGE"
Cancel = 1
End If

End Sub

 

[MushMouse]

I just tried adding that code. When I added it in a form that was open in add mode, it kept me in the form, but wiped out all the other data I had entered in that form.

When I tried blanking out the company name in a record that had been entered previously, it still closes the form after I respond OK to the message.

Should I be doing something else?

 

[RoyVidar]

Think there's just a sliht typo in nice95gle's suggestion, try either cancel = -1 or cancel = true

Else, perhaps the best event to perform data validation, is the forms before update event, which you can also cancel. It triggeres whenever an attempt to save is perfomed (as in moving to a new record, closing the form...)

The custom message. You can probably use the forms on error event to substitute the default access message. In the event, the DataErr will contain the error number (3022 I think for dupes, 3317 I think for datavalidation) it might look like this:

[tt]if dataerr=3317 then
msgbox "your message"
response = acdataerrcontinue ' don't show default message
end if[/tt]

Roy-Vidar

 

[MushMouse]

tried cancel = -1 and cancel = true in the forms before update event. Both closed the form.

Any other ideas?

 

[RoyVidar]

Not without seing the actual code (include the whole sub), and having some extra information. How is the companyname required, table level index/required, control level validation... The companyname control - is it bound to the forms recordsource, what happens if you put a breakpoint in your code (F9) and step thru it (F8), what values do the control(s) have when debugging, is there any errormsg...

Roy-Vidar