Results 1 to 5 of 5
  1. #1
    Join Date
    Aug 2006
    Posts
    52

    Unanswered: Question on searching

    Hi,
    I have a table name Doctor ID, I want to create a form , in this form I want to enter the first name of the doctor / OR / phone number and then I will get all the details about this doctor,,

    I noticed when I go to clinics they ask me about my phone number and then they search and find it, if they do not find it they ask about first name, whenever they write first name they can see a list of completed names of patients (just like in google when I write a search word I am prompted to select from a list of search keys ) I hope someone understands me...

    Can someone tell me how to do this in neatly,, where i can either search by phone number of by name,

  2. #2
    Join Date
    Oct 2009
    Posts
    340
    in the header of the form, add a combobox - the wizard will launch and select the option that you want it to find a record of the form - set it up by whatever lookup column is most typical i.e. telephone number....

    when that's done

    then make another combobox in the header, just like before - only this time set it to look up by last name (or whatever)

    and repeat as necessary.

    I've made forms where user wants to look up by Purchase Order, by Customer Name, even by Date of sale.... you can have more that one combobox.....

    hope it helps.
    www CahabaData com

  3. #3
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Here's a possible solution:
    Code:
    Private Sub Text_Search_AfterUpdate()
    
        Dim rst As DAO.Recordset
        Dim strCriteria As String
        
        If InStr(Me.Text_Search.Value, "*") > 0 Then
            strCriteria = "[Column1] LIKE '" & Me.Text_Search.Value & "' OR [Column2] LIKE '" & Me.Text_Search.Value & "'"
        Else
            strCriteria = "[Column1] = '" & Me.Text_Search.Value & "' OR [Column2] = '" & Me.Text_Search.Value & "'"
        End If
        Set rst = Me.RecordsetClone
        rst.FindFirst strCriteria
        If rst.NoMatch = True Then
            MsgBox Me.Text_Search.Value & " was not found in the database.", vbInformation, "Search"
        Else
            Me.Bookmark = rst.Bookmark
        End If
        Set rst = Nothing
            
    End Sub
    Where Text_Search is the name of the TextBox control where the wanted keyword is entered and [Column1] and [Column2] are the names of the 2 columns in which the search must be performed (first name of the doctor and phone number in your example). If a column is not defined as text, remove the single quotes "'" before and after its corresponding reference to Text_Search, e.g. if [Column1] is numeric, the first part of the expression becomes:
    Code:
     strCriteria = "[Column1] LIKE " & Me.Text_Search.Value & " OR  ...
    Have a nice day!

  4. #4
    Join Date
    Aug 2006
    Posts
    52
    Thank you so much, it works now..highly appreciate it

  5. #5
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You're welcome!
    Have a nice day!

Posting Permissions

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