mandatory fields to prompt if null

[PaulJK]

Some of my forms have fields I would like to be completed in all circumstances. In the tables concerned I had the required value set to 'yes' and I was receiving error messages as appropriate. I then placed some save/cancel code in the Before Update element of the form. This works fine but you can now save changes even with the mandatory fields are null. I have taken the save/change code from some of the forms where this option is not so crucial, and I receive no error messages where mandatory fields are null. Curious?

I figured it may be best to additionally prompt on each form to ensure mandatory fields are completed. I would like this to happen before any save takes place and in a sequence ie one null value at a time. I do not want to save details where records are incomplete.

I have found some code for the required info prompt whilst searching:

If IsNull(Me.ClientStatus) Then
MsgBox "ClientStatus has not been completed, please complete before proceeding"
DoCmd.GotoControl "ClientStatus"
End If

Should this go in BeforeUpdate, and if so before the save/cancel code.

What happens with the above is I get the three (have 3 potential null values) error messages in succession saying xxx field is incomplete & not allowing you to change the null - it moves to the next null - and then the save/cancel option. If you ignore the null messages & hit save, the records are saved. What I would like is the null messages to be completed sequentilly before the save/cancel option.

Probably missing something basic in my code routine.

Some pointers would help me.

Thanks

 

[ghudson]

You should use the tables validation rules if you really want to ensure a record can not be saved if the field is null.

In the design view of the table for each field you do not want to allow nulls...

Set the "Validation Rule" to Is Not Null

Set the "Validation Text" to The 'X' field can not be empty! Please key something in the 'X' field.

HTH

 

[PaulJK]

Thanks for getting back.

I placed appropriate validation code & text and changed the required value to 'No' in the tables concerned. I did receive the messages in line with my validation text. If I change the required value back to 'Yes', I recieve a system message that the field has to be completed rather than my customised validation messages. Is it correct to have the required value to 'No' in these circumstances?

However, the above does not work with forms I have set to DataEntry only. You can close the form without any prompts appearing. How could I resolve this? These forms have no save/cancel options.

Likewise forms which are not set to DataEntry and have save/cancel options do not receive messages about mandatory fields when the form is closed. My save/close code is on before update for forms concerned.

Do i need to consider put something withing the close code on forms?

I also found some code about the IsNull function. Would this help me & where would i place the code?

Thanks in advance for your help.


IsNull Function Example

The following example uses the IsNull function to determine whether the value of a control is Null. If it is, a message prompts the user to enter data. If the value is not Null, a message displays the value of the control.

Sub ControlValue(ctlText As Control)
Dim strMsg As String

' Check that control is text box.
If ctlText.ControlType = acTextBox Then
' If value of control is Null, prompt for data.
If IsNull(ctlText.Value) Then
strMsg = "No data in the field '" & ctlText.Name _
& "'." & vbCrLf & "Please enter data for " _
& "this field now."
If MsgBox(strMsg, vbQuestion) = vbOK Then
Exit Sub
End If
' If value is not Null, display value.
Else
MsgBox (ctlText.Value)
End If
End If
End Sub


IsNull Function Example