Skip to main content
Kofax

Combo Box that accepts a Variant array in SBL Validation Script

4949

QAID # 4949 Published 

Question / Problem:

How can I create an SBL validation script with a Combo Box that accepts a Variant array, such as the results of the SQLRequest function, instead of a string array?

Answer / Solution:

Review the following function example.

This code is specifically for a List Box, but the List Box requires a String variable also, just as a Combo Box does, whereas the SQLRequest function returns a Variant.

Function PostMyField_Number( EnteredValue As String, MaxLength As Integer ) As Integer

  On Error GoTo Failure

  EnteredValue = Trim(EnteredValue)
  If ( Len(EnteredValue) > MaxLength ) Then GoTo Failure

KfxMyField_Number = EnteredValue

REM ---------------------------------------
REM ##### Code addition for Database Update
REM =======================================

  'Only do in index mode.
  If KfxOperation = "Index" Then

     ' Variable declarations
     Dim SQL As String
     Dim Columns As Long
     ' OutputStr will hold the complete connection string
     ' used to connect to the database.

     Dim outputstr As String
     Dim num_recs As Integer
     Dim counter As Integer
     ' Set SQL statement to use value entered.
     SQL = "SELECT * FROM Database_Data WHERE MyField_Number = '"
     SQL = SQL & KfxMyField_Number & "'"

     ' Execute the query.  Result will hold the number of columns
     ' returned (for SELECT statements only).  The returned value
     ' will be negative if an error occurs.
     Columns = SQLExecQuery(Connection, SQL)

' Check for errors.

If Columns < 0 Then
   ' Put the error message and number in to the array
   SQLError destination:=errors
   MsgBox Str(errors(1,3)) & " #" & Str(errors(1,1)),, "SQLExecQuery Error"
   GoTo Failure

End If
' Redimension the array to match the SQLExecQuery statement
Redim TargArray(1 to columns, 1 to 2)
' Retrieve the data from the SQL statement, and place into the
' array TargArray().  The result will be the number of ROWS returned
' by the SQL statment, e.g. number of records.  A negative number
' denotes an error.
num_recs = SQLRetrieve(Connection, TargArray())
' Check for errors.
If num_recs < 0 Then

   ' Put the error message and number in to the array
   SQLError destination:=errors
   MsgBox Str(errors(1,3)) & " #" & Str(errors(1,1)),, "SQLRetrieve Error"
   GoTo Failure

End if
' If there is only one record found put it into the KfxField,

     ' otherwise, notify the user that there are more records than one!
     If num_recs = 1 Then

'Put database values into database variables DB_Field1 = Trim$(Str(TargArray(1,1))) DB_Field2 = Trim$(Str(TargArray(2,1))) DB_Field3 = Trim$(Str(TargArray(3,1)))
        ' Copy values to fieldnames.
        KfxField1 = DB_Field1
        KfxField2 = DB_Field2
        KfxField3 = DB_Field3

REM ================================================================
REM ##### Code addition for dialog box sample
REM ----------------------------------------------------------------

     Else
        ' We have more than one record match in the database.
        ' Display all matches and have the user select the correct one.
        ' Redimension array to match number of records returned.
        Redim CustomerArray(1 To num_recs) As String
        ' Pull each element out of the target array and store
        ' in the array that will go into the dialog box.
        For counter = 1 To num_Recs

   CustomerArray(counter) = TargArray(4,counter)
Next counter
' Definition for dialog box.
Begin Dialog newdlg 186, 92, "Choose correct customer"

   ListBox  8, 10, 115, 71, CustomerArray(), .CustomerList
   OkButton  130, 6, 50, 14
   CancelButton  130, 23, 50, 14

End Dialog
'Declare dialog box
Dim mydialog As newdlg
Dim res As integer
' Display dialog box.
res = Dialog(mydialog)
'Return Item select by user.
res = mydialog.CustomerList
' Dialog list box is zero-based, add 1 to get correct
' record
res = res + 1
' Update customer name on screen.
KfxMyField_Name = CustomerArray(res)
'Put database values into database variables
DB_Field1 = Trim$(Str(TargArray(1,Res)))

DB_Field2 = Trim$(Str(TargArray(2,Res)))
        DB_Field3 = Trim$(Str(TargArray(3,Res)))
        ' Copy values to fieldnames.
        KfxField1 = DB_Field1

        KfxField2 = DB_Field2
        KfxField3 = DB_Field3

REM ================================================================
REM ##### End of Code addition for dialog box sample
REM ----------------------------------------------------------------

End If End If
REM ##### End Database Update

Applies to:

Product Version
CAPTURE 10.0
  10.1
  10.2
  11.0