MS Access validation rules not firing in subform

1.2k views Asked by At

I have a datagrid that is a subform inside another form. I need to validate items inputted into this datagrid subform, but I'm having an excruciatingly hard time figuring out how.

Whenever I read about this on forums and on Microsoft's help site, they recommend you use Required and Validation rules on the table level. But because these are all linked tables, Access tells me these can't be saved / aren't applicable to my situation. When I save those settings, they are reset to defaults when I re-open table design view.

The other recommendation that seems to be the heaviest is to put in validation rules on the actual datagrid. Seems simple enough, but I can't get them to fire.

On my FromDate column, I put Validation Rule Is Not Null and Validation Text Can't be blank. I have also tried Not IsNull([FromDate]) and IsNull([FromDate])=0 but all of these had the same result of nothing happening. MS Access lets me skip over the fields, enter nothing, and continue on.

Simply put, the validation rules do not appear to be firing.

1- Should I be validating a datagrid through its datagrid property sheet validation rules?

2- If so, how do I get validation to actually fire and do the validating?

3- If I should be doing all the validiation programatically , how do I go about getting the contents of the datagrid / iterating through each new row ?

Pictures below: left: the subform / right: properties of my column in the datasheet

The subform enter image description here

Pictures below: left: linked table messages saying I can't edit table properties

enter image description here

1

There are 1 answers

1
HansUp On BEST ANSWER

Use the form's Before Update event to check whether FromDate is Null.

When it is Null, notify the user and cancel the update (Cancel = True).

Keep your existing Validation Rule for the text box. That will give the user immediate feedback if they attempt to delete a value from the text box (instead of deferring the notice until Access tries to save the entire record).

Private Sub Form_BeforeUpdate(Cancel As Integer)
    Dim strPrompt As String
    If IsNull(Me!FromDate.Value) Then
        strPrompt = "FromDate can't be blank"
        MsgBox strPrompt, vbInformation + vbOKOnly, "Missing Value"
        Me!FromDate.SetFocus
        Cancel = True
    End If
End Sub

Test to see whether you also need the same strategy at the form's Before Insert event. If you do, move that code to a separate procedure and call it from both Before Insert and Before Update.