INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Data Access Pages

How do I make a Find box in a DAP? by SMerrill
Posted: 15 Jan 03

I had some fun with this one. I made a very Access-looking Find box which pops up into a Div when you click the Find button.

This example was written for a Change Management application, so you'll have to customize the red text for your own uses.

I'm assuming that you've already thrown together a DAP containing many fields that you wish to search through to find some text.  Simply customize cboWhichField (which is bolded below) and enumerate your field names along with the label caption for each field.

HOW IT WORKS:
The Find button calls cmdFind_OnClick which makes a big HTML string in variable s that is placed into the InnerHTML of the Div.
This provides several controls like Find First, Find Next, and a combo box called cboWhichField that allows you to choose what field to search in.

The FindFirst and FindNext event handlers call the Finder routine.

HERE'S THE CODE:
Insert the following button and div:


<BUTTON id=cmdFind
      title="Show Find Controls"
      style="Z-INDEX: 1; LEFT: 0px; WIDTH: 0.635in; TOP: 0px; HEIGHT: 0.216in; TEXT-ALIGN: center">Find</BUTTON>

<br>

<DIV id=FindDiv style="VISIBILITY: hidden; WIDTH: 5.966in; HEIGHT: 1px"></DIV>


Then, insert the following code in your vb script block:

private sub Finder(FindNext)
  Dim strFindWhat
  dim strFieldName
  dim Found
  dim rst
  dim Criteria
  dim SearchDirection
  dim sErrMsg
  dim FindTitle

  FindTitle="Find First"
  If FindNext=True Then
    FindTitle = "Find Next"
  End If

  Criteria = ""
  strFieldName=cboWhichField.Value
  strFindWhat = txtFindWhat.value
  if not IsNull(strFindWhat) then
    on error resume next
    Found = True
    With MSODSC.DefaultRecordset
                Set rst = .Clone

                Select Case cboMatch.value
                Case 1 'Any part of field
                  Criteria = strFieldName & " like '*" & strFindWhat & "*'"
                Case 2 'Whole Field
                  Criteria = strFieldName & "='" & strFindWhat & "'"
                Case 3 'Start of Field
                  Criteria = strFieldName & " like '" & strFindWhat & "*'"
                End Select

                Select Case cboSearch.value
                Case 1 ' Up
                  SearchDirection = -1
                Case 2 ' Down
                  SearchDirection = 1
                Case 3 ' All
                  SearchDirection = 1
                  rst.movefirst
                End Select


                If FindNext Then
          rst.Find Criteria,1,SearchDirection, .Bookmark
                Else
          rst.Find Criteria,0,SearchDirection
                End If


        If err.number<>0 then
          Found = False
                  sErrMsg =  "Error: " & Err.Description
        Else
          If not (rst.BOF or rst.EOF) then
            .Bookmark = rst.Bookmark
          Else
            Found = False
          End if
        End if
    End with  
    set rst = nothing
    
    If Not Found Then
      msgbox " Criteria { " & Criteria & " } not found." & vbcrlf & sErrMsg,,FindTitle
    End if
  end if
end sub

Private Sub cmdFindNext_OnClick()
  Finder True
End Sub

Private Sub cmdFindFirst_OnClick()
  Finder False
end sub

Private Sub cmdCloseFind_OnClick()
  finddiv.style.height = "1px"
  finddiv.style.visibility="hidden"
  finddiv.innerHTML = ""
end sub

Private Sub cmdFind_OnClick()
dim s

s = "<TABLE id=tblFind " _
& " style=""FONT-WEIGHT: 700; WIDTH: 352px; " _
& " FONT-FAMILY: Trebuchet MS; BACKGROUND-COLOR: #99ccff"" " _
& " tabIndex=42 cellPadding=0 width=352 border=0>" _
& " <TBODY><TR><TD><FONT size=2>Which Field:</FONT></TD><TD>" _
& " <SELECT id=cboWhichField style=""Z-INDEX: 1; WIDTH: 213px"" tabIndex=43 " _
& "       size=1 name=cboWhichField>"

s = s _
& "<OPTION value=ChangeNumber SELECTED>Change Number</OPTION>" _
& "<OPTION value=Description          >Change Title</OPTION>" _
& " <OPTION value=Comments            >Comments</OPTION>" _
& " <OPTION value=last_mod_user       >Last Modified By</OPTION>" _
& " <OPTION value=last_mod_date       >Time Stamp</OPTION>" _
& " </SELECT></TD>" _
& " <TD>"

s = s _
& "<BUTTON id=cmdFindFirst title=""Find First Match"" style="" WIDTH: 0.843in; HEIGHT: 0.184in"" " _
& " tabIndex=50 MsoTextAlign=""General"">Find First</BUTTON></TD></TR>" _
& "<TR>" _
& "<TD><FONT size=2>Find What:</FONT></TD>" _
& "<TD><INPUT  id=txtFindWhat style=""WIDTH: 212px"" tabIndex=45 size=36></TD>" _
& "<TD><BUTTON id=cmdFindNext title=""Find Next Match"" style=""WIDTH: 0.843in; HEIGHT: 0.184in"" tabIndex=51 " _
& "     MsoTextAlign=""General"">Find Next</BUTTON></TD>" _
& "</TR>"

s = s _
& "<TR>" _
& "<TD><FONT size=2>Search:</FONT></TD>" _
& "<TD><SELECT id=cboSearch style=""WIDTH: 212px"" tabIndex=47 size=1>" _
& "<OPTION value=1          >Up</OPTION>" _
& "<OPTION value=2          >Down</OPTION>" _
& "<OPTION value=3 selected >All</OPTION>" _
& "</SELECT></TD>" _
& "<TD><BUTTON id=cmdCloseFind title=""Hide Find Controls"" style="" WIDTH: 0.843in; HEIGHT: 0.184in"" " _
& "tabIndex=52 MsoTextAlign=General>Close</BUTTON></TD>" _
& "</TR>"

s = s _
& "<TR>" _
& "<TD><FONT size=2>Match:</FONT></TD>" _
& "<TD><SELECT id=cboMatch style=""WIDTH: 212px"" tabIndex=49 size=1>" _
& "<OPTION value=1>Any Part of Field</OPTION>" _
& "<OPTION value=2 selected>Whole Field</OPTION>" _
& "<OPTION value=3>Start Of Field</OPTION>" _
& "</SELECT></TD>" _
& "<TD> </TD>" _
& "</TR></TBODY></TABLE>"

finddiv.style.visibility="inherit"
finddiv.style.height="102px"
finddiv.innerHTML = s
end sub


Look carefully -- I may have missed a few red spots.
--Shaun Merrill
  Seattle, WA

Back to Microsoft: Access Forms FAQ Index
Back to Microsoft: Access Forms Forum

My Archive

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close