Message Boxes

[mark curtis]

Dear All,

A little problem for all the experts out there:

I have a form which consists of 6 tabs, one of the tabs is for change requests. When a user clicks on the change request form I set the focus to the change title, which must be populated in order to register a record in the change title.

My problem is that if the user skips the change title field and tries to populate other fields an error message "index primary key can't contain null". So what I need is an on exit of the change title field: a message box that will force the user to populate the field or close the change tab.

I have tried the following:
If IsNull(Me.ChangeTitle) Or Me.ChangeTitle = "<MANDATORY>" Then
Cancel = True
MsgBox "Please enter a Change Title before proceeding", 48, "Change Title Required"
Me.ChangeTitle.SetFocus
Exit Sub
End If

And it seems to solve part of the problem but if I close the form via the 'x' in top right of form, the message box is still visible and I have to click it 4 times. I believe I need some error trapping but have not got a clue?? Please help

 

[simongallop]

One way around it is to get rid of the x in the top right hand corner, forcing people to close the form via a command button. To do this, in the properties of the form set controlbox to NO

 

[Neal]

One way to get around this is to go to the ChangeTitle field in the table and under the Validation Rule property, put Is Not Null. Then, under the Validation Text field, you can put whatever message you want to convey. When someone tries to leave that field empty on the form, they will get the message in a Modal message box. They click okay and are returned to the field on the form.

 

[mark curtis]

Thanks Neal,

I will try that

 

[Pat Hartman]

Neal's suggestion is good and since it involves no coding, I prefer it. But, in the cases where you can't use table constraints and need to resort to code, the best place to put the code is the form's BeforeUpdate event. Using any of the events related to a control fails if the cursor never enters the control and can also cause great confusion if for some reason, the user moves around the form using the mouse instead of using the tab key to move from control to control in the path you think he should take.

The form's BeforeUpdate event is the LAST event executed before a row is updated or inserted. If any edit contained in this event fails, you can cancel the update with the statement -
cancel = true
and then pop up a message box with an explaination and even set focus to the control with the problem.

This event has the added benefit of being executed (as long as the form is dirty) even if the user clicks the form's close button or even the database close button. So, short of a complete system meltdown, errors will be trapped and you can prevent bad data from being saved.