Results 1 to 2 of 2
  1. #1
    Join Date
    May 2011

    Question Unanswered: Search & edit function


    i built a reasonably simple database in access (with very limited knowledge) and i want to include a button that allows users to search for a record based on a parameter they choose.

    In other words i want to have a combo box listing the row headers of a n existing table. Then i want there to be a text box where they can enter the word/portion of the word they want to search for.

    Can any one help?

  2. #2
    Join Date
    Mar 2009
    Provided Answers: 14
    1. I suppose that the form is bound to the table (here named 'Shipments') in which the search is to be performed. If it's not the case you'll have to adapt the code.

    2. On the form, create a combo box, name it 'cbo_Columns' and assign 'Field List' to its RowSourceType property and the name of the table ('Shipments') to its RowSource property.

    3. On the same form, also create a text box (name: 'txt_Search') and a command button (name: 'cmd_Search').

    4. You can use this code to perform the search:
    Private Sub cmd_Search_Click()
        Dim dbs As DAO.Database
        Dim tdf As DAO.TableDef
        Dim rst As DAO.Recordset
        Dim strCriteria As String
        Dim strOperator As String
        Dim strArgument As String
        If (Len(Nz(Me.cbo_Columns.Value, "")) > 0) And (Len(Nz(Me.txt_Search.Value, "")) > 0) Then
            Set dbs = CurrentDb
            Set tdf = dbs.TableDefs("Shipments")
            Select Case tdf.Fields(Me.cbo_Columns.Value).Type
                Case dbText, dbMemo
                    strArgument = "'" & Me.txt_Search.Value & "'"
                    strOperator = " Like "
                Case dbDate
                    strArgument = "#" & Format(Me.txt_Search.Value, "mm/dd/yyyy") & "#"
                    strOperator = " = "
                Case Else
                    strArgument = Me.txt_Search.Value
                    strOperator = " = "
            End Select
            Set tdf = Nothing
            strCriteria = Me.cbo_Columns.Value & strOperator & strArgument
            Set rst = Me.RecordsetClone
            rst.FindFirst strCriteria
            If rst.NoMatch = False Then
                Me.Bookmark = rst.Bookmark
                MsgBox "No match: " & strCriteria, vbInformation, "Not Found"
            End If
            Set rst = Nothing
        End If
    End Sub
    Have a nice day!

Tags for this Thread

Posting Permissions

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