Check if required fields are empty on form

[Tadynn]

Hi all,

Using access 2k

I have a form that is called "frm_Receipt", this form is set to data entry mode, and it's data source is a table called "tbl_RMADetails". Out of the 5 text boxes and two buttons on this form, two of the text fields ("serial" and "part") are required fields.

To control this, I have set the two fields to required in the table. The problem is I don't really like the error messages that are generated whenever the user tries to close the form and either of these fields are blank.

The close button used is the "X" at the top right of the screen:

MsgBox1 = The field [fieldname] cannot contain a null value because the required property for this field is set to true. Enter a value for this field

MsgBox2 = You cannot save the record at this time
Microsoft access may have encountered an error when trying to save the record. If you close the object now, the database changes you made will be lost. Do you wish to close anyway? YES/NO


I have a save button on the form that I have the following code attached to. I have included some error handling, and this appears to work okay.

Private Sub SaveRMA_Click()
On Error GoTo Err_SaveRMA_Click


SetKey
Caption = "Product for RMA: " & Me.RMAHold & " saved!!"
Me.TimerInterval = 1000
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
DoCmd.GoToRecord , , acNewRec
Me.RMA.SetFocus
Exit_SaveRMA_Click:
Exit Sub

Err_SaveRMA_Click:

Select Case Err.Number
Case "3314"
MsgBox "Please enter a valid Part and Serial number." & vbCrLf & "These fields cannot be empty", vbOKOnly + vbInformation, "Missing data required"
Me.Part.SetFocus
Case Else
MsgBox (Err.Number & vbCrLf & Err.Description)
End Select
Resume Exit_SaveRMA_Click
End Sub


The main reason why I can't code around someone closing the form prematurely like I did with the save button is that I don't know what, or even if error numbers are being generated. I've also tried If statements on the form_close sub and the messages that I want have displayed but this doesn't suppress the system messages which still appear.

Can anyone help?


Thanks in advance, tadynn

 

[Frink]

Hallo,

This is a common issue and I think is best addressed by having a bit of structure to your form handling.

I like to have a function called SetControls in each of my forms. This updates the controls availability according to the selected data. In your case I think this would just apply to the Save button. The function would make the Save button disabled if the required fields were null or "", but enable it otherwise.

In the AfterUpdate event of all fields, put
=SetControls()
This will ensure that the form is kept up todate. The OnCurrent Event of the form should also call SetControls.

If you have some DATA which needs CHANGING after operator entry (ie. a combo box source which is dependent on another combo box value) this should be performed in an AfterUpdate Event procedure for the first combo box, followed by a call to SetControls.

This way Save is only possible when you allow the button, ie when the data is valid.
The only other time data will be saved is when the form is closed, as you suggest. To cope with this you also want a BeforeUpdate event for the record. This should check that the required fields are filled in. If they are not, raise a nice MsgBox and cancel the event. Make sure the MsgBox allows a 'Don't save' option so the form can be closed.

Other design points you may like to consider are 'Is it easy to see which fields are mandatory?' You may want to give them bold labels or outline or otherwise identify them as different to the rest of the fields on your form.

Hope that helps and is not too wordy,

- Frink