befroe update query

[nevstic]

G'day all
would anybody have an example which shows how to test a form for validation in the before update section.
I am trying (without much success) to check to see if all the controls / fileds have been completed, before closing the form.
I would like to do this with a VB yesno if I could.

If I could just get the "check and closure" bit correct
then I could (hopefully) do the rest. !
many thanks
nick

 

[Frink]

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:

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

 

[nevstic]

ok many thanks for your help
i am going to try this one, it does'nt look that simple but I like a challenge

thanks again
Nick