Hallo,
There are two ways you can do this, a simple, brute force way, or a complex, but elegant way.
Simple:
Put in a big set of If statements:
Code:
Private Sub Form_BeforeUpdate(Cancel As Integer)
dim strErrorMsg as String
strErrorMsg = ""
GoSub lblValidateFields
Cancel=(len(strErrorMsg)>0)
If Cancel then Msgbox strErrorMsg,vbExclamation
Exit Sub
lblValidateFields:
With Me
strErrorMsg = "RequiredStringValue is Required"
If nz(me!txtRequiredStringValue,"")="" then Return
strErrorMsg = "RequiredNonZeroNumericValue is Required and cannot be zero"
If nz(me!txtRequiredNonZeroNumericValue,0)<>0 then Return
strErrorMsg = "RequiredNumericValueGreaterThan20 is Required and must be greater than 20"
If val(nz(me!txtRequiredNumericValueGreaterThan20,0))<20 then Return
strErrorMsg = "AnotherRequiredStringValue cannot be blank"
if nz(me!txtAnotherRequiredStringValue,"")="" Then Return
strErrorMsg = "AnotherRequiredStringValue Must be Yes Or No"
if me!txtAnotherRequiredStringValue<> "Yes" And me!txtAnotherRequiredStringValue<> "No" Then Return
'etc. for all controls which are required
End with
strErrorMsg = ""
Cancel = False 'All controls are correct so don't cancel the form before update
Return
End Sub
This is easier to understand, but will require all the checks to be added in manually, which is difficult to maintain, and they must be allowed by the table validation rules too.
Complex:
I'm not going to write this one (as I am supposed to be doing proper work!) but the idea is:
Loop through all the controls on the form, if the control is bound then extract the Validation Rule for the Table Field and use that (using Eval) to check the value in the field against its control source validation rule. If it passes go on to the next one, if it fails, exit and notify the user.
You could also put a check in for the .Required attribute, complaining if it is Required, but null.
The advantage of writing code like this is that it will never need to be changed, even if you add or remove controls, and it ensures that the values in your fields are correct according to the underlying validation rules.
The disadvantage is that it is more tricky to write and may only work with some validation rules. You might also have to do use a different eval depending on whether it is a string or numeric value.
Personally, I would do the complex one as it would be more interesting to write, but the simpler one would be better if you want to get something working quickly, or if you have no table validation rules, or the form is not based on a single table, or if anyone else has to maintain your db and would find it difficult to understand.
- Frink
