Beyond Excel: VBA and Database Manipulation

Jul 6 2010   9:44PM GMT

Check Entry – Dealing with Field Level Exceptions

Craig Hatmaker Craig Hatmaker Profile: Craig Hatmaker

We’re just about finished dealing with the Check_For_Normal_Entry_Errors.  It uses one routine we haven’t discussed, Cust_Edit

Cust_Edit handles any field level validation that Check_For_Normal_Entry_Errors can’t.  Usually, this is a database lookup, but it can encompass any non-’standard’ validation – and that’s the problem with it.

Cust_Edit is really only a shell.  It is a place for you to write your own validation routines.  Many times I don’t need to modify Cust_Edit at all because the ‘standard’ edits are just fine.  Other times I have to plug in a simple database lookup for which I provide a template in the comments.  On very rare occasions, I have to write something truly unique.   Because Cust_Edit is something you modify, I can’t really explain much about it.  I can only give some guidance on how to plug your code in.  And that’s the beauty of it.

Cust_Edit is one of the few places where modifications are likely at all.  By having this shell prepared, you know exactly where to modify code, and best of all, where not to. Leave Check_For_Normal_Entry_Errors alone.  Ignore it.  Never think about it.  Put your custom validation rules in Cust_Edit only and nowhere else. 

Cust_Edit is called when V.Type (validation type) in Fields Definition Table equals “CUST” (custom) for a particular field.  The specific validation rule is held in the V.Table column.  That value is placed into sRoutine.  Plug your code after the Select Case sRoutine line.  To code the rule, create a Case Is = “<rule name>” line where “<rule name>” is the same as the value in V.Table for that field.  What you code after that is whatever is required for your particular rule.

Cust_Edit returns a comma delimited string with each value surrounded by double quotes like: “”VA”,”Virginia”” where “VA” is the code for “Virginia”.  In the templates and examples, it is assumed you want the description to display next to the code.  If you don’t, make sure only the code (surrounded by double quotes) is returned by Cust_Edit.

Since the most often case for custom validations is a database lookup, I have provided a template for that in the comments and two examples after the routine.  Copy the code for Cust_Edit into your worksheet where entries are made.  Don’t copy the examples. 

There’s one last thing I want to cover with you on this routine, and that is the odd parameter bUsePopUpbUsePopUp is a boolean (flag) variable.  If set to TRUE, Cust_Edit allows the user to select a value from the database using frmSelect.  When Cust_Edit is called from Check_For_Normal_Entry_Errors, bUsePopUp is always set to FALSE.  But Cust_Edit is also called by Set_Normal_Entry_Defaults.  We haven’t discussed Set_Normal_Entry_Defaults yet.  It is very similar to Check_For_Normal_Entry_Errors in that it can validate entries.  It is very different from Check_For_Normal_Entry_Errors in that it allows errors to be fixed.  Each routine has its place.  One cannot replace the other.   bUsePopUp allows Cust_Edit to serve them both.

Here is the code:

‘ ** Heavy modifications are likely here

 

Function Cust_Edit(sRoutine As String, rngCell As Range, _

                   bUsePopUp As Boolean) As Variant

 

   Description:Custom Validation Routine – Handles validations _

                that can’t be handled by default methods

 

   Parameters: sRoutine    Identifies the section of code to use

               rngCell     Cell being validated

               bUsePopUp   If True, user can select values from a list

 

   Example:    bResult = Cust_Edit(“Products”, Cell(lRow, 2), True)

 

     Date   Init Modification

   01/01/01 CWH  Initial Programming

 

    On Error GoTo ErrHandler   

    Cust_Edit = Null            ‘Assume the worst

       

    Dim v As Variant            ‘Generic Variant

    Dim l As Long               ‘Generic Numeric Variable

    Dim sSQL As String          ‘SQL String

    Dim sSQLCode As String      ‘SQL string for Codes/IDs

    Dim sSQLDesc As String      ‘SQL string for Descriptions/Names

    Dim sCode As String         ‘Code/ID field name

    Dim sDesc As String         ‘Desc/Name field name

    Dim sFile As String         ‘Table/File to search

   

   ‘Open the default connection

    If cn Is Nothing Then SQLConnection cn, sConnect

   

    Select Case sRoutine

       

        ‘Looking for a match in a database table/file

       

        ‘Step 1 – Look for Code’s Description in validated entries _

                  (it could only get there if code is good)

                 XL_Lookup(Range, _

                            Column in Range to return, _

                            Column to Range look in, _

                            Value to look for)

       

        ‘Step 2 – If not in worksheets, look in database

                 DB_Lookup(Value to look for, _

                            Table to look in, _

                            SQL to select Codes/IDs by, _

                            SQL to select Desc./Names by, _

                            Label for Code in frmPrompt, _

                            Label for Description in frmPrompt, _

                            Connection string, _

                            Connection Object, _

                            Popup(frmPrompt) is allowed flag)

               

                    

    End Select

 

ErrHandler:

   

    If Err.Number <> 0 Then MsgBox _

        “Cust_Edit – Error#” & Err.Number & vbCrLf & _

        Err.Description, vbCritical, “Error”, Err.HelpFile, Err.HelpContext

    On Error GoTo 0

 

End Function

 

 

 

 

 

Case Is = “Products”    ‘Access Example

    ‘Step 1 – Look for Code’s Description in validated entries

     v = XL_Lookup(Range(“Data”), “Name”, “Code”, rngCell)

     If v > “” Then v = “””” & rngCell.Text & “””, “”” & v & “”””

   

   ‘Step 2 – If not in worksheets, look in database

    If Left(rngCell.Text, 1) = “?” Or IsNull(v) Or IsEmpty(v) Then

        sCode = “[Product Code]“

        sDesc = “[Product Name]“

        sTable = “Products”

        sSQL = _

            “Select  Top 100 ” & sCode & “, ” & sDesc & ” ” & vbCr & _

            “From    ” & sTable & ” ” & vbCr

        sSQLCode = sSQL & _

            “Where    uCase(” & sCode & “) like ‘?%’ ” & vbCr & _

            “Order by ” & sCode & ” “

        sSQLDesc = sSQL & _

            “Where    uCase(” & sDesc & “) like ‘%?%’ ” & vbCr & _

            “Order by ” & sDesc & ” “

        v = DB_Lookup(rngCell.Text, sSQLCode, sSQLDesc, _

                      “Code”, “Name”, bUsePopUp, sConnect, cn)

    End If

    If Not IsNull(v) And Not IsEmpty(v) Then Cust_Edit = v

       

Case Is = “CUSTMAST”    ‘DB2 Example

    ‘Step 1 – Look for Code’s Description in validated entries

     v = XL_Lookup(Range(“Data”), “Name”, “Code”, rngCell)

     If v > “” Then v = “””” & rngCell.Text & “””, “”” & v & “”””

   

   ‘Step 2 – If not in worksheets, look in database

    If Left(rngCell.Text, 1) = “?” Or IsNull(v) Or IsEmpty(v) Then

        sCode = “CUCODE”

        sDesc = “CUNAME”

        sTable = “CUSTMAST”

        sSQL = _

            “Select  ” & sCode & “, ” & sDesc & ” ” & vbCr & _

            “From    ” & sTable & ” ” & vbCr

        sSQLCode = sSQL & _

            “Where    uCase(” & sCode & “) like ‘?%’ ” & vbCr & _

            “Order by ” & sCode & ” ” & vbCr & _

            “Fetch First 100 Rows Only “

        sSQLDesc = sSQL & _

            “Where    uCase(” & sDesc & “) like ‘%?%’ ” & vbCr & _

            “Order by ” & sDesc & ” ” & vbCr & _

            “Fetch First 100 Rows Only “

        v = DB_Lookup(rngCell.Text, sSQLCode, sSQLDesc, _

                      “Code”, “Name”, bUsePopUp, sConnect, cn)

    End If

    If Not IsNull(v) And Not IsEmpty(v) Then Cust_Edit = v

 

 

 

 

 

 

 

 

 

 

 

 Comment on this Post

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when other members comment.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to: