Create a Query-By-Example Form Sample

File: ...\Samples\Solution\Forms\Qbf.scx

This sample enables users to search and filter records in the same interface that they view the records.

When a user chooses Enter QBF, the code associated with the Click event of cmdQBFMode begins a transaction and appends a blank record to the table to store the user's query text.

* extract from cmdQBFMode.Click
BEGIN TRANSACTION
APPEND BLANK
THISFORM.Refresh

When the user chooses Query, code associated with the Click event of cmdExecuteQBF rolls back the transaction, throwing away the new record.

You can use transactions only with tables contained in a database. If you want to include QBF capabilities for a table that is not in a database, you can loop through all the controls on the form, save the old ControlSource property setting and set the ControlSource property of the controls to an empty string. After getting the user query string, you can loop back through the controls and reset the ControlSource properties.

Code associated with the Click event of cmdExecuteQBF also loops through the controls on the form, checks for user-entered values, and calls the ParseCondition method to assemble the filter string.

A user can enter a single value to match or an expression. For example, a user can enter either of the values below in the Title text box to set a filter:

Sales Manager

!= "Sales Manager"

If the user doesn't enter an expression, quotation marks aren't required.

The ParseCondition Method

LPARAMETERS cCondition, cControlSource
LOCAL lcRetCondition, lcFieldName
IF TYPE('cCondition') = 'C'
   cCondition = ALLTRIM(cCondition)
ENDIF

lcFieldName = SUBSTRC(cControlSource,(RATC(".",cControlSource)+1))


IF !EMPTY(cCondition) THEN
   IF TYPE('cCondition')$ "CM" 
      IF ("<"       $ cCondition OR ;
         "=="    $ cCondition OR ;
         "LIKE"    $ cCondition OR ;
         "<>"    $ cCondition OR ;
         "!="    $ cCondition OR ;
         "#"    $ cCondition OR ;
         "="    $ cCondition OR ;
         ">"    $ cCondition)
           lcRetCondition = lcFieldName + cCondition
      ENDIF
   ENDIF

   IF EMPTY(lcRetCondition)
      DO CASE
         * put quotes around character expressions
         CASE TYPE(cControlSource) $ "CM"
            lcRetCondition = lcFieldName + " = " + CHR(34) + cCondition + CHR(34)

         * put braces around date expressions
         CASE TYPE(cControlSource) $ "DT"
            lcRetCondition = lcFieldName + " = {" + DTOC(cCondition) + "}"

         OTHERWISE
            lcRetCondition = lcFieldName + " = " + STR(cCondition)
      ENDCASE   
   ENDIF
ELSE
   lcRetCondition = ""
ENDIF

RETURN lcRetCondition

See Also

Tasks

Create a One-To-Many Data Entry Form Sample

Solution Samples

Reference

Visual FoxPro Foundation Classes A-Z

Other Resources

Forms Solution Samples