MS Access validation rules not firing in subform

Question

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

Can't you open the database which contains the source table which the link points to and set Required=Yes for the FromDate field? — HansUp
@HansUp I could but I inherited this project which is hooked up to other projects and would be a disaster. — rlb.usa
Your second paragraph confused me. So the deal is not really that you're unable to change Required for the source field; you actually don't want to. Do I have that right now? — HansUp
@HansUp I'm unable to because the table is a Linked Table - it's linked to a MSSQL database. I can do whatever I want to with this Access project, but I can't with the MSSQL database, as it will affect other projects. — rlb.usa
I think you need to clarify exactly what you want. Your text box Validation Rule should prevent the user from deleting the stored FromDate value from an existing record. Does it work like that (yes or no, please)? If yes, what else do you want? If no ... I can't help you unless you share a copy of your database. — HansUp

HansUp HansUp · Accepted Answer · 2015-06-09T21:07:45

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.

MS Access validation rules not firing in subform

1 Answers