Results 1 to 3 of 3
  1. #1
    Join Date
    May 2004

    Unanswered: Newbie: put search result to another form

    I'm new to VBA in Microsoft Access and I have following problem:
    I want to search people with lastname "Smith*" in main form and if the result return 2 people or more, it will automaticcaly open "search result form" and display the result in listbox created in "searchadvanced".

    I did as follow:
    In main form, I created a following code:
    Sub cuong(locate)
    Dim rs As New Recordset
    Dim hanh As DAO.Recordset
    rs.Open _
    "select customerid from customers where " & _
    "lastname like '" & locate & "%'", _
    CurrentProject.Connection, adOpenKeyset, adLockOptimistic
    If rs.EOF Then
    MsgBox "khong tim thay " & locate & "."
    If rs.RecordCount > 1 Then
    DoCmd.OpenForm _
    FormName:="searchadvanced", _
    OpenArgs:="lastname like '" & locate & "*'"
    Set hanh = Me.RecordsetClone
    hanh.FindFirst "lastname like '" & locate & "*'"
    Me.Bookmark = hanh.Bookmark
    End If
    End If
    End Sub

    then I created button "Find Smith" in mainform and put following code in event click:
    cuong "smith"

    then I created a form named "searchadvanced" and created a listbox named "names" in it, and in load event of "searchadvanced" I put following code:
    Private Sub Form_Load()
    With names
    .RowSource = _
    "select customerid, " & _
    "lastname & ', ' & firstname as name, " & _
    "city & ', ' & state as location" & _
    "from customers where " & Me.OpenArgs
    .ColumnCount = 3
    .ColumnHeads = True
    .ColumnWidths = "1;1.5 in;1.5;"
    End With
    End Sub

    And when I click "Find Smith" in main form, it opened "searchadvanced" form, but no result appeared. It don't work for me. I attached a exampled file for you to see is something wrong there.
    many thanks.
    you tell
    Attached Files Attached Files

  2. #2
    Join Date
    Sep 2004
    Raleigh, NC
    Take a look at the included DB and let me know if thats what you're after.
    Attached Files Attached Files

  3. #3
    Join Date
    May 2004
    Hi, smacdonaldnc
    thank you very much for your support. your attached file works for me.
    thank you. (i will learn you code in your posted file)

Posting Permissions

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