Results 1 to 9 of 9

Thread: search box

  1. #1
    Join Date
    Oct 2008
    Posts
    33

    Unanswered: search box

    hey guys,
    i have this issue i would like to get resolved. i am building an inventory database and would like to insert a search box on the stock item form to see if an item exist in the database. i do not want to use a combo box or a list. i hope the explanation was good enough. an assistance granted would be appreciated.
    thanks

  2. #2
    Join Date
    May 2005
    Posts
    1,191
    I at least need more info to help you; What is the table structure look like? What do you mean by "search box"? What field are you searching? What do you want to do with the results? etc..
    Me.Geek = True

  3. #3
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Place a textbox on your form. AfterUpdateing that textbox, you can run some code to apply a filter or adjust your RecordSource to fetch only the records that match:

    Me.RecordSource = "SELECT * FROM <Table> WHERE <SearchField> = """ & Me.SearchBoxName & """"

    HTH
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  4. #4
    Join Date
    Aug 2003
    Location
    Bulgaria, Plovdiv
    Posts
    36
    Private Sub cmdSearch_Click()
    lblInfo.Caption = ""
    lblInfo.Visible = False
    Static bcmdSearch_Click As Boolean
    If bcmdSearch_Click Then
    DoCmd.ShowAllRecords
    Me.cmdSearch.Caption = Msg("Search")
    Me.cmdSearch.ControlTipText = Msg("Click to perform operation") & vbCrLf & Me.cmdSearch.Caption
    Else
    If Not ISNULL(Me!txtFind) Then
    If Not StringSearch("MaterialName", Me!txtFind, Me.cmdSearch, Me.lblInfo) Then Exit Sub
    Else
    bcmdSearch_Click = Not bcmdSearch_Click
    End If
    End If
    bcmdSearch_Click = Not bcmdSearch_Click ' Change Search/View All
    End Sub

    '------------------

    Public Function StringSearch(SearchField As String, _
    SearchStringValue As String, _
    ByRef cmdSearch As CommandButton, _
    ByRef lblInfo As Label) As Boolean
    On Error GoTo StringSearch_Err

    StringSearch = False

    If Not ISNULL(SearchStringValue) And Not IsEmpty(SearchStringValue) Then

    With CodeContextObject
    DoCmd.ApplyFilter "", "[" & SearchField & "] Like ""*" & SearchStringValue & "*"""
    If (.RecordsetClone.RecordCount > 0) Then
    ' If records are returned for the selected letter, go to the SearchField control.
    DoCmd.GoToControl SearchField
    cmdSearch.Caption = Msg("View All")
    cmdSearch.ControlTipText = Msg("Click to perform operation") & vbCrLf & cmdSearch.Caption
    StringSearch = True

    lblInfo.Visible = True
    lblInfo.Caption = Msg("Search successfull") & "."
    lblInfo.ForeColor = rgb(0, 0, 255)

    Call fPlayStuff(FilePathStripFileName(CurrentDb.Name) & "sounds\menuok.wav", 1)

    Else

    lblInfo.Visible = True
    lblInfo.Caption = Msg("No records found") & "!"
    lblInfo.ForeColor = rgb(255, 0, 0)
    Beep
    cmdSearch.Caption = Msg("Search")
    cmdSearch.ControlTipText = Msg("Click to perform operation") & vbCrLf & cmdSearch.Caption
    DoCmd.ShowAllRecords
    End If
    End With

    End If

    StringSearch_Exit:
    Exit Function

    StringSearch_Err:
    Call LogMsgError(Err.Description, Err.Number, ModuleName$, "StringSearch")
    Resume StringSearch_Exit
    End Function

  5. #5
    Join Date
    Oct 2008
    Posts
    33
    thanks for the input guys..i will try out your solutions and let you know how is goes.

  6. #6
    Join Date
    Oct 2008
    Posts
    33
    hey guys,
    i got this solution online and to some extent it works. Please see the code below.

    Private Sub search_button_Click()
    If IsNull(search_text) = False Then
    Me.Recordset.FindFirst "[Itemcode]=" & search_text
    Me!search_text = Null
    If Me.Recordset.NoMatch Then
    MsgBox "No record found", vbOKOnly + vbInformation, "Sorry"
    Me!search_text = Null
    End If
    End If
    End Sub

    there is on problem how ever. if i were to change " itemcode" to " itemname" i get an error.
    " syntax error ( missing operator) in expression"

    Private Sub search_button_Click()
    If IsNull(search_text) = False Then
    Me.Recordset.FindFirst "[Itemname]=" & search_text
    Me!search_text = Null
    If Me.Recordset.NoMatch Then
    MsgBox "No record found", vbOKOnly + vbInformation, "Sorry"
    Me!search_text = Null
    End If
    End If
    End Sub

    i am confused as to why this error is coming up.
    do youll have any suggestions as to what could be causing this and how it could be fixed?
    your input is welcomed
    regards
    WK

  7. #7
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    No doubt because itemname is text and item number is numeric. Try replacing the yellow line (which I almost didn't notice) with this:

    Me.Recordset.FindFirst "[Itemname]=""" & search_text & """"
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  8. #8
    Join Date
    Oct 2008
    Posts
    33
    Quote Originally Posted by StarTrekker
    No doubt because itemname is text and item number is numeric. Try replacing the yellow line (which I almost didn't notice) with this:

    Me.Recordset.FindFirst "[Itemname]=""" & search_text & """"
    wow...thanks man..works like a charm...i really appreciate your help...

  9. #9
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    You're most welcome
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

Posting Permissions

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