Results 1 to 3 of 3
  1. #1
    2 Star Lounger
    Join Date
    Mar 2003
    Location
    Canton, Ohio, USA
    Posts
    103
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Search/Filter a dbgrid (Access 2000 / VB6)

    <P ID="edit" class=small>(Edited by WendellB on 12-Jan-04 08:37. Used [pre] tag to make code more readable)</P>I have a form that, among other things, has a dbgrid on it. The user has the choice to search or filter the dbgrid by either "Last Name" or "ID Number". They make this choice from a combo box called "cmbSortby". They type their search text or number into a text box called "txtNameSearch".

    The name search works great without a problem but the numeric search doesn't. I get no errors and as you add numbers to the text box, it starts to sort the dbgrid, but the ID Number it sorts to has nothing to do with the number that was typed.

    Could anyone look at my code and tell me what I have wrong because I don't see it.
    <pre>Private Sub txtNameSearch_Change()
    Dim vBookMark As Variant
    Dim strFind As String
    On Error GoTo errSearch
    'Set a bookmark, try the find, if eof, return to bookmark
    vBookMark = rsSearch.Bookmark
    If Trim(Me.txtNameSearch.Text) <> "" Then
    Select Case Me.CmbSortby.Text
    Case "LastName"
    strFind = " Like '" & Me.txtNameSearch.Text & "*'"
    Case "iMISNumber"
    strFind = ">=" & Me.txtNameSearch.Text
    If Not IsNumeric(Me.txtNameSearch.Text) Then
    MsgBox "Enter a valid iMIS Number!", vbExclamation
    Me.txtNameSearch.Text = ""
    Me.txtNameSearch.SetFocus
    Exit Sub
    End If

    Case Else
    strFind = " Like '" & Me.txtNameSearch.Text & "*'"
    End Select
    'search for the selected criteria
    rsSearch.MoveFirst
    rsSearch.Find Trim(Me.CmbSortby.Text) & strFind
    If rsSearch.EOF Then
    rsSearch.Bookmark = vBookMark
    End If
    Set Me.dgDecSearch.DataSource = rsSearch
    Me.dgDecSearch.Refresh
    End If
    Exit Sub
    errSearch:
    MsgBox Err.Description
    End Sub
    </pre>


    Thanks in advance for your help!

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Search/Filter a dbgrid (Access 2000 / VB6)

    Just to clarify, I presume you are using Visual Basic as a front-end rather than an Access .MDB file. In either case, I believe the problem is that you need to convert the numeric value from text to numeric. In Access there is a function to do that (CLng() among others), and I presume that VB has a similar function.
    Wendell

  3. #3
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Search/Filter a dbgrid (Access 2000 / VB6)

    In VB, you generally use Val() to convert a string.
    Charlotte

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •