Data validation in form or table?

[CJ_London]

I did say - it depends. It depends on the validation required. I also try to prevent users committing a validation offence in the first place - less irritating to the user so as others have said, better on a form



For example if a string is restricted to 10, 25, 50 chars, whatever I set that in the table - but I also include code in a form to prevent users from entering more than the maximum number of characters rather than validation saying 'you've entered too many characters'. I use the change event

Same applies to the other data types

Passwords may be set for a minimum number of chars - and perhaps other criteria such as number of upper/lower/numeric chars. This could be done in a table as a validation rule but easier to do in a form - I sometimes use formatting - simplistically background starts off red and gets lighter as each condition is met - again triggered by the change event

I can handle dates by using my own calendar form where I can restrict the date range that can be chosen - but I also include the validation in the table if appropriate.

Rather than looping through the controls, you can loop through the form recordset to look at the field properties (dao access only) - me.recordset.fields("myField").validationrule

[GinaWhipp]

Unbound controls have no value only bound ones, at least, that has been my experience.

[rpeare]

I'm with Gina and Moke I use my own validation via code.

I've got modules that cycle through controls using the TAG property to determine what needs to be done with the control

DEN_REQ_EXC_DUP for instance would be

DEN (Data entry number field, DET would be data entry text, DED would be data entry date)
REQ (Required element)
EXC (Exclude from resets of the form, i.e. prepopulated fields I do not change)
DUP (Duplicate check the field/field combinations)

I'm loading meaning into the .tag property then evaluating various steps of validation by parsing the .tag string.

Then it's easy to add/remove fields from being required elements just by changing the tag property rather than having to navigate the validation on the table.

[GinaWhipp]

I'm loading meaning into the .tag property then evaluating various steps of validation by parsing the .tag string.

That's my next rendition, parsing the tag values as I am finding I need to have various items on the TAG property.

[rpeare]

If you want an example of my functions I can put one up here

[GinaWhipp]

If you want an example of my functions I can put one up here

Now, where is the fun in that! Only kidding, that would be great, less work!

[Micron]

Unbound controls have no value only bound ones, at least, that has been my experience.

That can't mean what is sounds like to me. An unbound textbox has no value or value property? It certainly does.

[GinaWhipp]

No, not exactly what I meant, value was the wrong word.

[GinaWhipp]

Sorry for the short reply, had to take a call...

Now, I could have done...

If ctl.ControlSource = ""

OR

If (Left(ctl.ControlSource, 1) <> "="

OR

If Len(ctl.Properties("ControlSource") = 0

...to make it more obviuos, however, the one I use accomplishes the same thing. It skips if no Control Source found it moves along. *Value* was the wrong word.

[rpeare]

Functions.zip

Simple example.

All my public functions are in the GLOBALFUNCTIONS module

Some conventions I use
1. All tables, forms, queries will be named identically with just a prefix change (tblEmployee, frmEmployee, qryEmployee, lstEmployee on the data entry form)
As a side note, almost all my forms tend to have list boxes as pickers my users prefer to see data this way, the list box could be substituted for a combo box etc
2. All fields on the form will be named identically to how they appear in table
3. All data entry field TAG properties will start with DE for the main data entry
4. All data types will be represented by the third character (DEN is numeric, DET is text, DED is date)
5. Any required element will have _REQ somewhere in the TAG
6. Any unique element will have _DUP somewhere in the TAG (in this case the employee first name, last name and birth date form the unique element)
7. Though no example in this database I use _EXC to indicate an excluded element, for instance if I do not want to clear a field on clicking "CLEAR"
8. Any child table will follow the same rules except will have a SUB<X> prefix (i.e. Sub1_DEN, Sub1_DET if I were going to add the employee phones to the main data entry form)
9. Any buttons doing the add/update, clear, undo for the child elements will contain SUB<X> in their TAG property
10. The form itself has a tag property I only use for my error messages
11. The code is as generic as possible so I can just copy almost any form and with a minimum of modification create a fully functioning new form.
12. the REFRESHLISTS function will refresh all combo boxes on a specific form. I typically use this in the ON CLOSE event of tables supporting combo boxes.

I included some of my child functions (SUBADD, SUBUPDATE, SUBPOPULATE, SUBCLEAR) you can play around with these adding the employee phones on the main form.

I would be interested to see how other folks are doing similar things as well.

[GinaWhipp]

@rpeare

Mighty big thanks!

Something for me to *play* with. I also see we use the similar naming conventions!

[Micron]

It skips if no Control Source found it moves along.

Well, your point is lost on me I'm afraid. I thought the idea was to validate if required controls had values in them or not, in which case Control Source is of no use on an unbound form. If the form and the required control is bound then knowing the control source property value is of no use for telling you if the control itself has a value. I just don't see the relevance of checking for a control source property value to determine if the control contains an entry (value). Sorry - just my opinion.

[GinaWhipp]

@Micron

Hmm, you make an interesting point, thanks for that. I am going to update my page to specifically point that out.

I don't use unbound Forms, I just back it out if the User selects Cancel. My Controls that are unbound are used for filtering, searching or drilling down not for validation so no worries for me there either.

[Micron]

Hmm, you make an interesting point, thanks for that. I am going to update my page to specifically point that out.

I'm flattered

My Controls that are unbound are used for filtering, searching or drilling down

Then they don't have the Tag property value (like 'reqd') that you're testing for, yes? Thus they aren't even polled.
I recall you (?) posted here or elsewhere about needing to sort through multiple tag property values. That could be as simple as Instr function, but there was a case where I posted what might be an overly complicated solution for that (user wanted the tag property but also a custom message for each control). It was to create an array based on split tag values; arry(0) being the tag value and arry(1) being the message string. I used commas as separators, but in hindsight would use an uncommon character such as pipe or tilde.

[GinaWhipp]

Then they don't have the Tag property value (like 'reqd') that you're testing for, yes? Thus they aren't even polled.

No, they don't and they aren't. No reason to ever look at those.

I recall you (?) posted here or elsewhere about needing to sort through multiple tag property values.

Don't recall that nor could I find the post.

The Function I provide actually gives you the name of the Control, as well as, highlighting it in the message. That is as close as I get for a custom message per control. Once of the reason I shared that Function is because it does make it sort of easy for people to use.

I do like the idea splitting the Tag values as the more I use them the more I want to have more than value. Rpeare was kind enough to provide his Function so I could have a peek at it. I think I would stick with the pipe as I use that in other places for separation, might as well remain consistent.