Mandatory fields to fill

[paultje_bos]

All,

I am working in a form and I trying to make some fields mandatory. The data in the form is based on a table, I know I can change the field setting to Required of the specific fields, but since this have consequences to other forms with the same table I rather limit myself to this form.
I tried to do it in the Validation rule, but this doesn't work (or I am doing it wrong).

What I want is that if a new record is started the fields Quanty, Unit, Weight and Value are filled. Basically you can't click away until these fields are filled.

Looking forward to your response. Thank you!

 

[Micron]

You can simply set validation rules and text in form design if you don't want to do it at the table level, which I what I think you said. More robust solutions require code. A common method is to set each required field's tag property to something like Reqd then iterate through the form controls collection looking for those that have that tag, then checking them for Nulls or empty strings. You can then list all the missing ones in a message. However, if there are only a few you need to worry about, it might not be worth the effort. The upside is that you'd probably learn something about the controls collection, enumerating, looping, tag property, control types...

 

[paultje_bos]

Hi Micron,

I tried multiple things now. First I tried to set the Validation rule "Is Not Null" on the control box, but this doesn't seem to trigger anything, I actually tried other rules as well, but nothing seems to work, like if all validation rules on this form are disabled.
Secondly I tried to put a validation via the AfterUpdate event. I put this code in:

Private Sub QUANTITY_AfterUpdate()
If [QUANTITY] Is Null Then

MsgBox("Please fill in quantity")
Else
End If


End Sub

But this only gives me errors. I am sort of at my limitations of Access knowledge and VBA, so some more detailed help would be perfect.

 

[Micron]

I would use If IsNull (Quantity) or (Quantity) ="" then. Sorry, can't elaborate more right now.

 

[xenou]

Some ideas on validating all fields on a form here:
Validate Text Box Entries | Database Solutions for Microsoft Access | databasedev.co.uk

 

[paultje_bos]

I think I managed to solve it, I found on a different website a code that works.

Private Sub Form_BeforeUpdate(Cancel As Integer)

Dim strMsg As String

If IsNull(Me.UNIT) Then
Cancel = True

strMsg = strMsg & "Please fill in Unit" & vbCrLf
End If

If IsNull(Me.QUANTITY) Then
Cancel = True

strMsg = strMsg & "Please fill in Quantity" & vbCrLf
End If

If IsNull(Me.[WEIGHT(KG)]) Then

Cancel = True

strMsg = strMsg & "Please fill in Weight" & vbCrLf
End If

If IsNull(Me.[VALUE(EUR)]) Then
Cancel = True

strMsg = strMsg & "Please fill in Value" & vbCrLf
End If

If Cancel Then
 strMsg = strMsg & "Correct the entry, or press Esc to undo."
 MsgBox strMsg, vbExclamation, "Invalid data"
 End If
 End Sub

Thank you all for you input anyway, much appreciated!