Solved

Create a Form Validation Class, multipart question, part 1,

Posted on 2006-12-01
19
333 Views
Last Modified: 2016-08-29
This question is limited to requesting a functional outline for a Central Validation Handler.  The functionality needs to seperated into defined steps so that subsequent questions will be limited in scope.

I am having limited succses developing a class module that handles form validation and business logic control.  I need to intercept form and form-control Before_Update events for Combo,  List & Text box  controls and prevent users from entering invalid data.  I also need to intercept After_Update events to make sure data is enter into fields in the correct order and prevent users from skipping over combo and/or list box controls to avoid subsequent ListSource queries from failing.

Scenario: Forms with cascading text controls where subsequent ListSource queries are dependent on previous selections.   All business logic is maintained in tables but ListSource queries will return empty s if any required selections are skipped.

Example:
ListSource for Combo-2 depends on the selection made in Combo-1
ListSource for Combo-3 depends on the selections made in Combo-1 & Combo-2
ListSource for Combo-4 depends on the selections made in Combo-1, Combo-2 & Combo-3
If a user skips forward from Combo-2 to Combo-4 without making a selection in the preceding Combo Boxes, the ListSource query associated with Combo-4 will return empty.  This requires subsequent Combo Boxes to be disabled until all preceding Combo selections are made.  

If a user were to go back and change a previously entered combo selection then all previously entered combo boxes from that point forward would have to be cleared automatically.  The user would have to make new selections going forward from the place where the change occurred.

Example:  A user has made selections in Combo-1 thru Combo- 8 and then goes back and changes combo 3 then Combo 4 thru 8 would be cleared programmatically; Combo 4 would be enabled and Combo 5 thru 8 would be disabled.

The code need to allow for records to be deleted, cancellation of unsaved changes made to existing records and cancellation creation of an uncompleted new record

I have been able to get the Input Validation and Business Logic control functionality working procedures separately but have been unable to combine the processes to work collectively.  

The validation code I am using is a modified Class module downloaded from http://www.peterssoftware.com/fv.htm.  The class is only a conceptual example and is not fully functional as written. The business logic control functionality was written from scratch.

I will upload a zip-file to www.ee-stuff.com  that better illustrates the needed functionality.  The zip file will include both a Visio document and JPG version of the diagram.  Some functional details are incomplete and I realize that the concept may have to be completely reworked.  Again, all I am seeking at this point is to establish a comprehensive functional roadmap.  
0
Comment
Question by:ArtBarnwell
  • 8
  • 6
  • 5
19 Comments
 
LVL 44

Expert Comment

by:Leigh Purvis
ID: 18059362
I couldn't find a file at EE-Stuff earlier, have you put it there yet?

But that aside - you're really only after concepts for now yes?
(I'd imagine so anyway - keeping this project broken up as your rightly want to).

The example class is very specific.
(Well, the overall event wrapping is fine - the validation logic in the example is what's very specific, but isn't a part of it you'd use anyway).

Your need isn't particularly for data validation though is it?  More data presence...
It's a set of controls (possibly for searching?) where you require "previous" controls to be filled before subsequent ones.  (A bit like cascading combos - but not perhaps?  Otherwise the class seems slight overkill - they self cascade and automatically display what they should and nothing otherwise).

You'll need *some* way of identifying to the class the order of controls.
So it can determine if the "previous" controls are entered when any given one is actioned.
Some might disagree - but I'd *very* often turn to the Tag property for this.

I find it very useful in giving a form and controls certain visible properties (unlike custom properties added through code) that you can add purely through the UI at design time.
It might be there that you specified some sort of "fill" order for controls.
The validate method of the class (likely the only part you need to worry about) would be where you then walk through said controls - determining the status of each - reporting if it wasn't satisfactory.

As for methods such as Deletions, cancellations etc - they'd have to be examined one by one.  But if they're related to the status of your combos - then you should be able to Cancel whatever event you see fit based on your new validation method.
0
 

Author Comment

by:ArtBarnwell
ID: 18061335

<I couldn't find a file at EE-Stuff earlier, have you put it there yet?>

Reply: I unloaded the file right after I post this topic.  The file name is Q_22079977_ValidationClassDiagram.zip and contains a jpg and a Visio 2003 document.  I have no idea as to how to access the uploaded files an I have posted a support question.

<But that aside - you're really only after concepts for now yes?>

Reply: What I really need is a functional outline that identifies individual procedural steps needed to perform centralized before and after update validation.  I think I understand what needs to be done conceptually but I am obviously going about it the wrong way.   Hopefully, EE will come to my rescue.  Breaking the process down into baby steps will allow me to post separate and step specific questions.  I got a feeling that I will be awarding many thousands of points during this quest (along with many thanks).

<Your need isn't particularly for data validation though is it?>

Reply: Generally speaking, I have 2 needs.
 
(1) - I want handle all validation functionality from a single module.  I reuse many of the same fields on a ton of forms and managing validation at the form level is both unruly and redundant.  Peter De Baets’ code  has some functional anomalies but properly modified, it is exactly suited for my validation needs.

(2) - After data is entered and validated, I need to prevent users from skipping over  fields (particularly combo and list box controls) .  I have expanded the Petter’s Class to also intercept After_Update events and created a variety of class procedures and functions for managing this.

<You'll need *some* way of identifying to the class the order of controls. Some I'd turn to the Tag property for this.>

Reply: The code I am using relies exclusively on the tag property and the control type.  I have created a function that returns an ordinal control-name based on a passed TabIndex value (see Q_22065524.html & Q_22046786.html).

<As for methods such as Deletions, cancellations etc - …. you should be able to Cancel whatever event you see fit based on your new validation method>

Reply: This is where my code is having a problem.  Once I start entering a record I am trapped into completing it.  When I try to cancel, the validation code kicks and I am redirected to the first field that fails validation.   Once the record is entered and passes validation, I cannot move to another record.     It is important to note that Petter’s code can be circumvented and will allow records to be saved without passing associated validation rules.  I believe the difficulties I am having are related to correcting this problem and/or from combining the after_update functionality into the Class.

< A bit like cascading combos - but not perhaps?>

Reply: The post-update functionality is specially needed for managing cascading combos but the list sources are tied to some very complicated table-based business logic.  The business logic functionality is codeless and works flawlessly.  The only requirement is that the cascading selections must be entered consecutively and in a predefined order.   Changing an previously entered field selection “breaks the chain” and all data from that point forward would have to cleared and re-entered.

<Otherwise - they self cascade and automatically display what they should and nothing otherwise).>

Reply: This is not necessarily true.  
Hypothetical example:  An order form for a computer company that requires the selection of the following :

ComboBox Option....|....Availble Options
_____________________________________________________________________
Computer Type:           (Floor Model & Laptop)
Case Type:                   ( Standard Tower, Deluxe Tower & Notebook)
Hard drive Size:            (50 Gig, 100 Gig  & 10 Terabyte)
Power Supply Option:    (External, Internal - 350 Watt, Internal - 500 Watt & Internal - 1 Kilowatt)
Network Card Type:      (None, Wireless PCMCIA, Cat-5 PCI Card & Wireless PCI Card)

You can see that there are option combinations that are obviously unavailable. Also, suppose that the 1 kilowatt power supply and the 1 Terabyte hard drive are only available with the “deluxe tower”.

If you select: Floor Model, Deluxe Tower, 10 Terabyte hard drive, 1 kilowatt power supply and PCI Wireless Network card, restively;  then go back and change the computer type to Laptop, the remaining fields would all contain options that are not applicable to a laptop computer.  If the record was save in this state, the invalid data (options) would also be saved.  I have considered other ways to prevent this but simplest solution is to clear all of the fields forward from where the change occurred and prevent invalid record from being created/saved.


0
 
LVL 44

Expert Comment

by:Leigh Purvis
ID: 18061408
I'm getting no files found for this question.
I think that can happen however - and when you upload a file, you're given a URL to its location, and you take that to paste in the relevant question.
You can have a look for it - or see what Support comes back with.
0
 

Author Comment

by:ArtBarnwell
ID: 18061586
0
 
LVL 44

Expert Comment

by:Leigh Purvis
ID: 18064378
So your combos aren't necessarily cascading then - you simply stipulate that for any given one to be entered - the "preceeding" combos must have a value.
And you determine the order of this using the function you worked in a previous question (which, FWIW, you could have mentioned this class as the larger context - I wouldn't have begun it with so many questions then - and gotten into the meat of code :-S)

So let's take it on a case by case basis.
A user enters combo 1, 2 and 3.
Then feels like skipping 4 and entering directly into combo 5.

Your function lists all controls in their tab order.
So you use this exlusively to identify that before combo 5 - 1,2,3,4 must be entered because they have lower tab values?
Do you load the collection of controls in the class in the order of tabbing perhaps?

If you did - then, in theory, checking these prior controls would become relatively trivial.
0
 
LVL 39

Expert Comment

by:stevbe
ID: 18070311
I'm gonna subscribe and see if I can keep up on this ... I have approached validation so many ways ... one important question you need to know before you start mapping data validation is if you are using bound or unbound forms? If they are bound then you are going to need to capture bad data types (I typed the letter "a" in a date field) for new records in the Form_Error event.
0
 

Author Comment

by:ArtBarnwell
ID: 18071071
<So your combos aren't necessarily cascading then - you simply stipulate that for any given one to be entered - the "preceding" combos must have a value.>

The combos are expressly cascading and each successive combo’s “RowSource query” is dependent on the collective input of all preceding combos (e.g.: Combo2.RowSource = is dependent on Combo1.value,  Combo3.RowSource = is dependent on Combo1.value & Combo2.value, …, Combo10.RowSource is dependent on these selection values of Combos 1 thru 9).  

< which, FWIW, you could have mentioned this class as the larger context>.
I strive to be considerate of people’s time, especially when I asking for “free” help.  Many of the  experts at EE, who give so much and ask for nothing in return, are professionals with their own responsibilities, deadlines and commitments.   Considering the complexity the larger context, I was concerned  of the topic ballooning into a de-facto project under guise of a single question.

< Do you load the collection of controls in the class in the order of tabbing perhaps?>  Yes
           
For cnt = 0 To ctlCnt                             ' 2nd loop is used to process in TabIndex Order
     For Each ctl In .Controls                  ' Loop through the controls collection
         If ctl.ControlType = acComboBox Or _
                 ctl.ControlType = acTextBox Or _
                 ctl.ControlType = acListBox Then    ' Again, we are only interested in Text controls

        If ctl.TabIndex = cnt Then
                 Set ValidateCtl = _
                 New clsValidateFormData        ' Create new bValidateFormData Class

                 ValidateCtl.bInitialize _
                           m_Form, ctl.Name            ' bInitialize Class Instance

              m_ColValidateControls.Add _
                         ValidateCtl, ctl.Name         ' Add instance to m_ColValidateControls  Collection

              strControlName = ctl.Name         ' Get the Control's name
              Set ValidateCtl = Nothing            ' Destroy Class instance
Debug.Print ctl.Name
End If

<If you did - then, in theory, checking these prior controls would become relatively trivial.>   Yes but this is not the problem.  I have modified Peter’s code and the validation seems to work as expected.  I am also able to automatically lock and unlock combo boxes and prevent users from “skipping ahead” as well as taking corrective action when user goes back and changes a previous selection to chained combo box.

The problem is that the 2 processes are incompatible with each other and do not function correctly together.  The validation code relies on the Before_Update events.  The functionality needed to insure that only previously enter combo’s and the next combo boxes are enabled rely on the After_Update event.
0
 

Author Comment

by:ArtBarnwell
ID: 18071116
Hi Steve,
Glad your subscribing and it’s well worth it.

For developmental purposes, the forms are directly bound the forms but will ultimately be connected using ADO.  For now, I am just using simple rules (IsNull, Len =2, etc).  Any experience you can lend will be appreciated.  

Once you become a member, you may want to download the logic diagram posted at   www.ee-stuff.com (the link is posted above).    The diagram merely illustrates what I am trying to accomplish and likely not to be the correct way (or I would not be here).    All I am looking for in this post is simplified functional breakdown ("See Spot run")  Hopefully, this will allow for more concise “step” related questions in the future.
0
 
LVL 39

Expert Comment

by:stevbe
ID: 18071172
Howdy Art ... take a look to the left ... I have been a member for years :-)

but will you be binding the forms to the subsequent ADO recordsets or will you be handling the record nav, inserts, deletes and updates manually? I ask because this will make a big difference in how/ what can be captured. I have lots of experience with data / business logic validations and have learned (the hard way) how much a pita bound forms can be :-)

Steve
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 44

Expert Comment

by:Leigh Purvis
ID: 18071201
"I was concerned  of the topic ballooning"
Fair enough - and considerate of you.  Markus seemed to come along as if I wasn't understanding the requirements in that previous question - I was merely trying to understand the context.

So the issue here is that you actually have the two sides working perfectly - but they're "incompatible".
In what way?
Exactly how does one prevent the other?  Because if data validation fails - then the AfterUpdate event never fires to execute your control validation?
0
 

Author Comment

by:ArtBarnwell
ID: 18073537
Stevbe: <I have been a member for years >  My apologies.  I thought you ID seemed familiar and I misinterpreted what you meant by “subscribe”.  As far as looking to the left goes.  Its actually scroll way up and look to you left.

Stevbe:  <will you be handling the record nav, inserts, deletes and updates manually?> Forms will be unbound.

Stevbe:  <I have lots of experience with data / business logic validations>
Excellent!  F.Y.I.:  I am not sure it you have gone through the previous posts but please note that my misuse of the term “Business Logic”.  All of the business logic is actually managed in tables.  What I am actually referring to is post-validation functionality that is intended to prevent users from violating the integrity of the underlying business logic.

LPurvis <Markus seemed to come along as if I wasn't understanding the requirements in that previous question  - I was merely trying to understand the context.>  
Marcus?    I’m not sure who you are referring to but I don’t  think anyone is implying anything about you.  If you are referring to Stevbe’s comments, all he is stating is that he also has experience in this area and interested in helping as well.
0
 
LVL 44

Expert Comment

by:Leigh Purvis
ID: 18075079
Noo not Steve - I was on about the previous question still :-)

I think we're still flying slightly blind here though.  I know you want an overview - rather than precise code based instructions, but seeing the class can't hurt understand where it's going wrong.
My previous question still stands...

"...they're "incompatible".
In what way?
Exactly how does one prevent the other?  Because if data validation fails - then the AfterUpdate event never fires to execute your control validation?"
0
 

Author Comment

by:ArtBarnwell
ID: 18078439
LPurvis: < No not Steve - I was on about the previous question still>
Regarding the previous question:  My decision to close the previous question was based solely on the recommended posting approach received from EE.  I contacted EE directly regarding my concerns of my previous question “ballooning” and questioned if it was even  appropriate to post such a complicated question.   Harfang  was awarded the points simply because his solution satisfied the question as written (as stipulated in EE’s user agreement).  I meant no disrespect.

LPurvis: <   >Do you want me to paste the code as a comment or upload it as a text file?  Also, I think the diagram I uploaded previously will be a helpful  reference to  you as you review the code.

Progress update: I have be able to resolve the compatibility problems by expanding the class and process the OnCurrent Event and then running the After_UpDate code when the event fires (unfortunately, my centralized validation handler is morphing into a centralized event manager).  
I haven’t adequately tested the changes but it appears the remaining problems are:

If  I start a new record; make all of the required; then press the escape key; all of the previously entered fields are cleared (as expected) but the After_Update code is not running.  This leaves cleared fields enabled and the focus is not being redirected.   I think I can fix this.

The validation routine will not let me move to a  different record if I am in a new or existing record with invalid data unless I either cancel the update/insert or provide valid data for all required fields.  Technically,  this is functioning correctly but is not intuitive or user friendly.  It would be better if the form’s before_update event provided the user with a cancel option as well as an “OK” option.  Selecting the cancel option would automatically “undue” uncommitted changes” and bypass the validation code instead of forcing the user to manually cancel the changes.  This could be managed by passing an additional argument to the validate procedure that would allow for an expanded validation-error message box for form events verses control events.

 Even if I am able to get all of the code working I still would appreciate for you and Stevbe’s review and expert recommendations.  


0
 
LVL 39

Expert Comment

by:stevbe
ID: 18078656
<bypass the validation code instead of forcing the user to manually cancel the changes>

It looks like if you add vbOKCancel as the buttons on the message box and expand the sTemp var to include you cancel option instrtuctions then you could check the return of the msgbox for vbCancel Then .... send a frm.Undo and set the Vaidate functions return to True and exit that proc. Thinking about this a bit more you will need a higher level var to track if they "cancelled" to exit out of the form_before update along wiht the Validate. Does this sound reasonable?
0
 
LVL 44

Expert Comment

by:Leigh Purvis
ID: 18078750
I think posting the code (perhaps as another upload) would be fine yes.

As Steve says there's ways a plenty to ask something of the user as far as Canceling.

I'm not sure what part the Current Event is playing in your code - but I wouldn't worry about it becoming an event handler class.
Sink into whatever event suits your needs.
(The overhead will be minimal).
Perhaps even Keypress - and if you're wanting to establish when a user's canceling (hit escape) you should be able to get joy from the form Undo event.  (Depending on which version of Access you're using that is).

P.S.  Absolutely no offense from you in previous question at all - nor Markus (as there's nobody here I have more respect for than he - he's part of a restricted group of experts who get the "nod" from me... Steve's in it too.  I bet they're oh so pleased lol).
0
 
LVL 39

Expert Comment

by:stevbe
ID: 18079017
coming from you, Leigh, you bet I'm flattered ;-)
0
 

Author Comment

by:ArtBarnwell
ID: 18086053
LPurvis: <I wouldn't worry about it becoming an event handler class. (The overhead will be minimal).>

Your right, the reason I am creating the validation class is so I can maintain form-level code in a centralized module as well as reduce functional redundancy .  Expanding the class to manage other form-level events will increase these benefits.

In the future, I would also like to expand the class to handle control types other than text controls (i.e.: conditionally enabling & disabling option and push-button groups; conditionally hiding & un-hiding controls ; etc).  Ideally, it will develop into completely portable code that can be reused generically in other Access applications and with little or no modification.  Do you (and/or Stevbe) think something like this is worth future collaboration?

LPurvis:  < Depending on which version of Access you're using that is>
Unfortunately, the application will have to run on Ancient Access 2000.
0
 
LVL 39

Accepted Solution

by:
stevbe earned 500 total points
ID: 18086628
I have left my forms class pretty simple because I did not want to hardcode lots and lots of seperate tests and conditions. I have been trying to find a good way to make *extended* properties for forms and controls. While my original focus was strictly on data and business logic validation I can see where the same structures could be used for control flow logic. I have an AppForms collection class to hold my AppForm class instances and started expanding out to include a AppControls collection class for AppControl class and tying them all up through .Parent. On of the things that can be really cool with this is I have a function "MyOpenForm" that I instead of DoCmd.OpenForm, this functions opens and reguisters form in my classes which in turn hook various events so sometimes I have code that runs for a form but no code is in the actual form itself ... at all :-)

Public Sub MyOpenForm(FormName As String, _
                      Optional View As AcFormView = acNormal, _
                      Optional FilterName As Variant, _
                      Optional WhereCondition As Variant, _
                      Optional DataMode As AcFormOpenDataMode = acFormPropertySettings, _
                      Optional WindowMode As AcWindowMode = acWindowNormal, _
                      Optional OpenArgs As Variant)
'-------------------------------------------------------------------------------
'   enable custom error handling
    On Error GoTo ERR_MyOpenForm
'-------------------------------------------------------------------------------
   
    'finally, execute the built in OpenForm method
    DoCmd.OpenForm FormName:=FormName, _
                   View:=View, _
                   FilterName:=FilterName, _
                   WhereCondition:=WhereCondition, _
                   DataMode:=DataMode, _
                   WindowMode:=WindowMode, _
                   OpenArgs:=OpenArgs
   
    'see if a myForms object already exists
    If MyForms Is Nothing Then
        Set MyForms = New AppForms
    End If
    MyForms.Add Forms(FormName)
0
 

Author Comment

by:ArtBarnwell
ID: 18154924
Stevb, LPurvis: Sry for the reply gap -  business travel

Stevb:  
<I have left my forms class pretty simple because>
I would need to see your class code to evaluate.  WHould you be willing to uploaded it to www.ee-stuff.com and then post the link?

<I did not want to hardcode lots and lots of separate tests and conditions.>
I agree ---- Hard coding rules is not favorable.  Moving the validation rules and response logic into DB objects would better and allow for user-level management as opposed to physical code updates.

<I have been trying to find a good way to make *extended* properties for forms and controls.>
I was avoiding custom object properties because “hard-coding” concerns.  This would not be an issue if the property values are table-based.  The properties could be easily set when the class instances are created.  After-all, we are already looping through the forms and controls anyway.

<I have an AppForms collection class to hold my AppForm class instances and started expanding out to include a AppControls collection class for AppControl class and tying them all up through .Parent.>
Our approaches are similar but I have encapsulated the form-level and the control level functionality into a single class.

<I have a function "MyOpenForm"; I can see where the same structures could be used for control flow logic. >
There are functional similarities in Form initialization and flow control but form initialization is linear where as flow control involves more dynamic interdependencies.  I would like to incorporate your MyOpenForm function to better handle form initialization. handle

Please note:  I realize the code I developed and uploaded more complex and even redundant in some places.  In part, this is because I developed each piece of functionality independently and self reliant and then through everything together.  I did not have the time to clean it up before I posted it.

Again, the goal of this question is identify the best approach for centralizing validation and flow control and develop a functional breakdown.
0

Featured Post

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Join & Write a Comment

Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now