A Search Form Template

 <  Day Day Up  >  

I didn't include any navigation in the FlatFileForm template because Next /Previous/First/Last record is an artifact of the xBASE world. We could provide those four features with a single line of code each (not counting checking for BOF() / EOF() conditions). But users typically don't care about the previous or next record. They want to see a candidate list, point to the one they want, and click. Besides, it's nearly impossible if you use SQL Server, and that's what we're going to do next.

I've included a class called EasySearch . It allows you to add a search form with up to 4 searchable fields (and it's easy to extend that to 8 or 10 if you need them), to let users filter records and pick one, and to return the key value, from a DBF, SQL, or a Web service, with absolutely no coding in the form itself. You simply fill in three or four properties, name the input fields that you put on the search form with names SEARCH1 , SEARCH2 , SEARCH3, and SEARCH4 , set the form's tab order to Row order, and you're done. The code for this class is shown in Listing 3.6.

Listing 3.6. The EasySearch Class
 DEFINE CLASS EasySearch AS modalform tablename = ([])   && Table name to search colwidths = ([])   && Comma-delimited list of the relative widths colnames  = ([])   && Comma-delimited list of field names orderby   = ([])   && "Order by" column name colheadings = ([]) && Comma-delimited list if you don't want to use *                     field names as headings keyfield  = ([])   && Name of key field value to return PROCEDURE Init WITH THISFORM .Caption = [Search form - ] + .Name + [ (Main Table: ] ;          + TRIM(.TableName)+[)  Data access: ] + .Access NumWords = GETWORDCOUNT(.ColNames,[,]) IF NumWords > 4    MESSAGEBOX( [This class only supports a maximum of 4 fields, sorry], ;                 16, _VFP.Caption )    RETURN .F. ENDIF FOR I = 1 TO NumWords     .Field(I)   = GETWORDNUM(.ColNames,   I,[,])     .Heading(I) = GETWORDNUM(.ColHeadings,I,[,])     .ColWidth(I)= GETWORDNUM(.ColWidths,  I,[,]) ENDFOR WITH .Grid1 .ColumnCount        =  NumWords .RecordSource        = THISFORM.ViewName .RecordSourceType    = 1 GridWidth = 0 FOR I = 1 TO NumWords     .Columns(I).Header1.Caption    =       THISFORM.Heading (I)      GridWidth = GridWidth         +  VAL( THISFORM.ColWidth(I) )      FldName   = THISFORM.ViewName + [.] + THISFORM.Field   (I)     .Columns(I).ControlSource      = FldName ENDFOR Multiplier = ( THIS.Width / GridWidth ) * .90      && "Fudge" factor FOR I = 1 TO NumWords     .Columns(I).Width = VAL( THISFORM.ColWidth(I) ) * Multiplier ENDFOR .Refresh ENDWITH * Look for any controls named SEARCHn (n = 1, 2, ... ) FOR I = 1 TO .ControlCount     Ctrl = .Controls(I)     IF  UPPER(Ctrl.Name) = [MYLABEL]  && That is, if it starts with "MyLabel"         Sub = RIGHT(Ctrl.Name,1)            && Determine the index         IF TYPE([THISFORM.Search]+Sub)=[O]  && A search field #"Sub" exists            Ctrl.Visible = .T.            Ctrl.Enabled = .T.            Ctrl.Caption = .Heading(VAL(Sub))            .SearchFieldCount = MAX ( VAL(Sub), .SearchFieldCount )         ENDIF     ENDIF ENDFOR .SetAll ( "Enabled", .T. ) ENDWITH ENDPROC PROCEDURE  Load  WITH THISFORM IF EMPTY ( .TableName )    MESSAGEBOX( [Table name not entered], 16, _VFP.Caption )    RETURN .F. ENDIF IF EMPTY ( .ColNames )    Msg = [ColNames property not filled in.]    MESSAGEBOX( Msg, 16, _VFP.Caption )    RETURN .F. ENDIF IF EMPTY ( .ColWidths )    .ColWidths = [1,1,1,1,1] ENDIF IF EMPTY ( .ColHeadings )    .ColHeadings = .ColNames ENDIF .Access = oDataTier.AccessMethod .ViewName = [View]  +  .TableName oDataTier.CreateView ( .TableName ) ENDWITH ENDPROC PROCEDURE  Unload  WITH THISFORM IF USED   ( .ViewName )    USE IN ( .ViewName ) ENDIF RETURN .ReturnValue ENDWITH ENDPROC PROCEDURE  cmdShowMatches.Click  WITH THISFORM Fuzzy = IIF ( THISFORM.Fuzzy.Value = .T., [%], [] ) STORE [] TO Expr1,Expr2,Expr3,Expr4 FOR I = 1 TO .SearchFieldCount     Fld = [THISFORM.Search] + TRANSFORM(I) + [.Value]     IF NOT EMPTY ( &Fld )         LDD = IIF ( VARTYPE( &Fld) = [D],       ;               IIF ( .Access = [DBF],[{],['] ),  ;               IIF(VARTYPE( &Fld) = [C], ['],[]) )         RDD = IIF ( VARTYPE( &Fld) = [D],       ;               IIF ( .Access = [DBF],[}],['] ),  ;               IIF(VARTYPE( &Fld) = [C], ['],[]) )         Cmp = IIF ( VARTYPE( &Fld) = [C], [ LIKE ],[ = ] )         Pfx = IIF ( VARTYPE( &Fld) = [C], Fuzzy,   []    )         Sfx = IIF ( VARTYPE( &Fld) = [C], [%],     []    )         Exp = [Expr]  + TRANSFORM(I)        &Exp = [ AND UPPER(] + .Field(I) + [)] + Cmp ;             + LDD + Pfx + UPPER(ALLTRIM(TRANSFORM(EVALUATE(Fld)))) + Sfx + RDD     ENDIF ENDFOR lcExpr = Expr1 + Expr2 + Expr3 + Expr4 IF NOT EMPTY ( lcExpr )    lcExpr = [ WHERE ] + SUBSTR ( lcExpr, 6 ) ENDIF lcOrder = IIF(EMPTY(.OrderBy),[],[ ORDER BY ] ;         + ALLTRIM(STRTRAN(.OrderBy,[ORDER BY],[]))) Cmd     = [SELECT * FROM ] + .TableName + lcExpr + lcOrder oDataTier.SelectCmdToSQLResult ( Cmd ) SELECT ( .ViewName ) ZAP APPEND FROM DBF([SQLResult]) GO TOP .Grid1.Refresh IF RECCOUNT() > 0    .cmdSelect.Enabled = .T.    .Grid1.Visible     = .T.    .Grid1.Column1.Alignment = 0    .Caption = [Search Form - ] + PROPER(.Name)    ;             + [  (] + TRANSFORM(RECCOUNT()) + [ matches)]   ELSE    .Caption = [Search Form - ] + PROPER(.Name)     MESSAGEBOX( "No records matched" )    .cmdSelect.Enabled = .F. ENDIF KEYBOARD [{BackTab}{BackTab}{BackTab}{BackTab}{BackTab}] ENDWITH ENDPROC PROCEDURE  cmdClear.Click  WITH THISFORM FOR I = 1 TO .SearchFieldCount     Fld = [THISFORM.Search] + TRANSFORM(I) + [.Value]     IF VARTYPE ( &Fld ) <> [U]         lVal = IIF ( VARTYPE( &Fld) = [C], [],   ;                IIF ( VARTYPE( &Fld) = [D], {//}, ;                IIF ( VARTYPE( &Fld) = [L], .F.,  ;                IIF ( VARTYPE( &Fld) $ [IN], 0, [?]))))        &Fld  = lVal     ENDIF ENDFOR ENDWITH ENDPROC PROCEDURE  cmdSelect.Click  WITH THISFORM lcStrValue = TRANSFORM(EVALUATE(.KeyField)) .ReturnValue = lcStrValue .Release ENDWITH ENDPROC PROCEDURE  cmdCancel.Click  WITH THISFORM .ReturnValue = [] .Release ENDWITH ENDPROC ENDDEFINE 

How to Use the EasySearch Template

Here's an example of how to use this template in five easy steps:

  1. Type this line in the command window:

     

     CREATE FORM FindCust AS EasySearch FROM Pinter 

  2. Add two text boxes and a StatesList combo box. Name these three controls Search1 , Search2 , and Search3 .

  3. Set the Tab Order to Rows on the search form.

  4. Set the MainTable property to Customers , the KeyField property to CustomerID , and the ColumnList property to [CompanyName, ContactName, Phone] .

  5. Enter FindCust as the SearchForm property value in your Customers form.

Figure 3.3 shows the screen for the FindCust form.

Figure 3.3. The FindCust form.

graphics/03fig03.jpg


 <  Day Day Up  >  


Visual Fox Pro to Visual Basic.NET
Visual FoxPro to Visual Basic .NET
ISBN: 0672326493
EAN: 2147483647
Year: 2004
Pages: 130
Authors: Les Pinter

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net