Check Entry – Dealing with Field Level Exceptions
Posted by: Craig Hatmaker
database, development, excel, Microsoft Excel, ms query, odbc, sql, tutorial, vba
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 bUsePopUp. bUsePopUp 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
RSS Feed
Email a friend
