Data validation

[spizotfl]

Hi all I just have a question about how people manage Data Validation. I currently have some very ugly code (a series of if-then-else statements for a very large number of fields) in a function that is called by the BeforeUpdate event, but I am looking for some insight into how other people handle checking things. I can't set everything at the table level because the vast majority of the fields are of the type "If field1 = val1, then fields 2-10 can be 1-5 and are mandatory; if field1 = val2, then fields 2-10 can be 1-5 or optional." I am definitely planning a rewrite once I have some better options, so know that any suggestions will be duly considered and greatly appreciated. And to clarify, I am much more interested in solutions that others employ to handle validation rather than wanting someone to rewrite my mess. I want to learn best practices from those who know. If I can't figure out how to apply what I learn, I will ask further questions.
Thanks,
Mike

"Maturity is a bitter disappointment for which no remedy exists, unless laughter can be said to remedy anything."
-Vonnegut

 

[SoCalAccessPro]

Maybe look in to select case and for next ?

This is just psuedo code, but might give you an idea (I don't even think te allowedvalues property exsists):

Select Case Me.field1
Case cva1
    For i = 1 To 4
        With Me.Controls
            .Item(i).Visible = True
            .Item(i).AllowedValues = "Something"
        End With
    Next i
Case cva2
    For i = 1 To 4
        With Me.Controls
            .Item(i).Visible = True
            .Item(i).AllowedValues = "SomethingElse"
        End With
    Next i
Case whateverelse
    'More code here
End Select

Tyrone Lumley
SoCalAccessPro

http://www.socalaccesspro.com

 

[fneily]

Could you post your table structure? It just doesn't seem to be normalized as presented.

 

[spizotfl]

the tables mirror a state mandated data collection scheme for the non-profit that i work for. for the different tables, for instance a substance abuse evaluation, there are different reasons for the evaluation (ie. admission to the agency, one time assessment, normal discharge from the agency, special discharge due to other circumstances). depending on the reason, some fields may not need to be collected. part of what i am working on is the situation where the if one purpose code is chosen, those fields that are required for that purpose code are verified/validated.

"Maturity is a bitter disappointment for which no remedy exists, unless laughter can be said to remedy anything."
-Vonnegut

 

[Remou]

It would be possible to set up a validation table. I have not though this through carefully, but you seem to have something akin to a survey, so:

tblValidation
FormName
KeyFieldName
KeyFieldValue
DependantFieldName
DependantFieldValidation
ValidationFailNote

In the before update event, you could select each dependant field, using tag perhaps, with KeyFieldValue and check the against DependantFieldValidation (Eval may be appropriate). This would allow the validation rules to be updated at a later stage without tampering with the code.

 

[fneily]

For future reference, posting your table structure doesn't mean an explanation. It's like the above post where you show your tablename, fields, designated primary key(s).
That's the only way we can tell if the database is designed correctly.

 

[spizotfl]

sorry for the delay
the structure is:
pk social
pk purposeOfEval
pk providerID
pk evalDate
employmentStatus
primaryIncomeSource
drugOfChoice
ageOfFirstUse
primaryDiagnosis

and many more fields along the same lines.

"Maturity is a bitter disappointment for which no remedy exists, unless laughter can be said to remedy anything."
-Vonnegut

 

[TheAceMan1]

How are ya spizotfl . . .

[blue] . . . but I am looking for some insight into how other people handle checking things.[/blue]

Have a look at my post here: thread702-1490566

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997
Also faq181-2886