Data validation in form or table?

[TrainingExcellence]

If I have a form linked to a given table, should I add data validation to the form or the table? What difference does it make?

Thanks!

[CJ_London]

depends on the validation - if added to the table you get a 'system' message, on a form you can provide a more helpful message.

personally I would do both, then the table validation will catch any potential issues missed by the form or some other basis for entry

[GinaWhipp]

Well, I guess it's personal preference. I only use it on the From using....
https://www.access-diva.com/vba13.html

Gives me a little more control.

[moke123]

I'm in Gina's corner on this one. I use a similiar procedure as hers. One difference is I use the controls label caption to concatenate the missing fields\controls and fire a single message box. The label can be hidden if neccessary.

Public Function IsfrmValid(frm As Form) As Boolean


    Dim ctl As Variant
    Dim flg As Boolean
    Dim strMsg As String


    flg = True


    For Each ctl In frm.Controls


        If InStr(1, ctl.Tag, "V8") Then


            If Nz(ctl, "") = "" Then


                flg = False


                ctl.BorderColor = vbRed


                strMsg = strMsg & Space(20) & "* " & ctl.Controls.Item(0).Caption & vbNewLine


            Else


                ctl.BorderColor = vbBlack


            End If


        End If


    Next ctl


    IsfrmValid = flg


    If flg = False Then


        MsgBox "The following item(s) are required:" & vbNewLine & vbNewLine & strMsg


    End If


End Function

[GinaWhipp]

My reasoning...

I have a Model Database filled with Tables I use over and over again. Depending on which Project I am working on the required fields wanted will change. Well, I don't want to have to keep messing with the Tables. By moving my validation to the Form it's less work for me.

I will also add gives me much more control and when upsizing that validation property does not always play nice.

[Micron]

depends on the validation - if added to the table you get a 'system' message, on a form you can provide a more helpful message.

But there is a validation text property at the table level, no? I agree though, and seldom use table level validation as it's quite restricted, but admittedly, for those who are not adept with code, it is a decent option. Personally, that would be my answer to "what difference does it make?"

This is how I prefer to code it (note that it's only for labels that are attached to/associated with a data control). It's air code, so hopefully I didn't forget something. As for me, I don't see the point in passing every control to examine its tag property because that includes everything else (subform controls, buttons, labels, image controls, etc, etc.) which seldom has any bearing on the validation at hand. Not sure I get the need for boolean variables either, but then again I didn't study the other suggestions.

Dim ctl As Control
Dim strMsg As String

For Each ctl in Me.Controls
 If ctl.ControlType = acTextbox Or ctl.ControlType = acComboBox Then
  If ctl.Tag = "reqd" And Nz(ctl,"") = "" Then strMsg = strMsg & "- " & ctl.Controls(0).Caption & vbCrLf
 End If
Next

If strMsg <> "" Then 
 strMsg = "Please enter a value for" & vbCrLf & strMsg
 msgBox strMsg
End If

I suppose the Instr function is for those cases where the tag might hold more than 1 value, which I almost never need, but it's a good solution for those situations of course.

[GinaWhipp]

@Micron,

I don't look at every control on the ones that have a Control Source, cuts down on looping every Control. That sad, I thought about using Labels but became an issue when (1) not all Labels were attached, (2) not all Labels were Labels, some where Text Boxes coded to changes depending on what was in another Control and (3) not all Controls had Labels.

[Micron]

@Micron,
I don't look at every control on the ones that have a Control Source, cuts down on looping every Control.

I didn't direct that statement to your or anyone else in particular. Not sure where control source enters into this discussion as it seems that what I said I don't do is what's done in the code at the link you provided and I don't recall the control source being dealt with. Since you brought it up, I'll point out that the linked code doesn't consider the control type until it gets way down into setting visual formats, so a lot is going on with each control in frm.Controls regardless of what they are.

This isn't a "you're wrong and I'm right" thing. I only offered my take on the approach with caveats so there's no need for either of us to defend anything.

[GinaWhipp]

@Micron,

I was not *directing* towards you as much as the comment about looping thru all the Controls, not meaning to *point you out*. Should have quoted the comment, my bad.

That said, I'll agree that it does *pass by*, however, when I pushed it to only look at check boxes, text controls and combo boxes I only gained one second, maybe two so I guess I didn't consider that a game changer.

I NEVER take these discussions as a *you're wrong and I'm right* but more like learning to skin the cat another way. I'm willing to be we get another developer in here we'll find and yet another way of doing ti and we may both learn something different\new.

[Micron]

Fair enough. Then in the spirit of learning, I'll tell you why I started the practice: many years ago, there was a control that had no tag property, so an error was raised when I allowed it to loop through all controls. I cannot recall for sure what that was but have found that now all controls seem to have the Tag property - even those which I can imagine no use for. So if M$ ever introduces another control and doesn't give it the Tag property, y'all will fall into the same trap. Maybe someone who reads this and has much older versions available will take a look and see if they can spot what it is. Maybe 2003 or earlier; maybe it was the command button. I just know that once bitten...

[GinaWhipp]

Hmm, now that is interesting. I only started using the Tag property when 2003 came out and as far as a know\remember every control had a Tag property at that time. But it could have been the Rectangle Control, no reason for it to have it but it does. That is one of the reason I first check for a Control Source before checking the Tag property. It can be empty but not non-existent. I hope someone passed by that knows which Control that was would just be nice to know.

[Micron]

I first check for a Control Source

I can't find any evidence of this (but it's getting late here) and have looked at the function code there too. What am I missing?

[GinaWhipp]

This part...

If Nz(ctl, "") = "" Then

[GinaWhipp]

It is getting late because I wrote "First, I check the Control Source...". I don't check that first. I first see if there is anything on the .Tag property, if blank ignore and only read the ones that have *require* (because some have ADMIN) and then only the ones that have a Control Source validate. Then I only highlight specific controls because you can't highlight all Controls, i.e. check boxes. Though now that I'm looking I could add list boxes, hmm.

[Micron]

This part...

If Nz(ctl, "") = "" Then

Umm, OK. I would call that validation (checking if the control has a value). To me, Control Source is a property with values such as table field name, query field name or an expression.