use pop up form to display search results

[Adish]

Hi All. I have 2 search boxes in my main form where users can enter the customers name and search. I want to open another pop up window/form which displays all the names of the customer which are same e.g display all john smith and then the user can select the correct one from the pop up window seeing further details like dob and ph#'s. I don't know hoe to do this in access and VBA. any help would be appreciated.

Thanks

[June7]

Options:

1. VBA to build filter criteria and apply to form when it opens, review http://www.allenbrowne.com/ser-62.html

2. dynamic parameterized query as form RecordSource (I never do this)

[Adish]

My Code is

If Me.UserInput = "" Then
    MsgBox "Enter Surname to Search", vbInformation, "Surname Required"
    Me.UserInput.SetFocus
Exit Sub
ElseIf Me.FirstName = "" Then
    MsgBox "Enter First Name to Search", vbInformation, "First Name Required"
    Me.FirstName.SetFocus
Exit Sub
Else
    
    On Error GoTo myError
    Dim strNAMESearch As String
        strNAMESearch = "dbo_NUCNAME.GIVEN_NAMES like '" * " & Me.FirstName & " * "'" & " AND " & "dbo_NUCNAME.NAME like '" * " & Me.UserInput & " * "'"
        
        strSQL = "SELECT dbo_NUCADDRESS.*, dbo_NUCSTREET.STREET_NAME, dbo_NUCMEMO.text1, dbo_NUCMEMO.memo_type, dbo_NUCMEMO.Status " _
            & "FROM ((dbo_NUCADDRESS INNER JOIN dbo_NUCSTREET ON dbo_NUCADDRESS.STREET_NO = dbo_NUCSTREET.STREET_NO) INNER JOIN dbo_NUCMEMO ON dbo_NUCADDRESS.PROPERTY_NO = dbo_NUCMEMO.TABLE_NO)" _
            & "INNER JOIN (dbo_NUCNAME INNER JOIN dbo_NUCASSOCIATION ON dbo_NUCNAME.NAME_CTR = dbo_NUCASSOCIATION.KEY2) ON dbo_NUCADDRESS.PROPERTY_NO = dbo_NUCASSOCIATION.KEY1 " _
            & " WHERE " & strNAMESearch ' & " AND " & MEMO_TYPE & " AND " & Memo_Status
        
        Debug.Print strSQL
        DoCmd.OpenForm "Owner Details", , , strNAMESearch
        Forms.Item("Owner Details").RecordSource = strSQL
leave:
        Exit Sub
myError:
        MsgBox Error$
    Resume Next

I do get the form to open up but no records are displayed. not sure what's wrong in my code. I do get this in my immediate window which is correct but does not display in pop up form.

SELECT dbo_NUCADDRESS.*, dbo_NUCSTREET.STREET_NAME, dbo_NUCMEMO.text1, dbo_NUCMEMO.memo_type, dbo_NUCMEMO.Status FROM ((dbo_NUCADDRESS INNER JOIN dbo_NUCSTREET ON dbo_NUCADDRESS.STREET_NO = dbo_NUCSTREET.STREET_NO) INNER JOIN dbo_NUCMEMO ON dbo_NUCADDRESS.PROPERTY_NO = dbo_NUCMEMO.TABLE_NO)INNER JOIN (dbo_NUCNAME INNER JOIN dbo_NUCASSOCIATION ON dbo_NUCNAME.NAME_CTR = dbo_NUCASSOCIATION.KEY2) ON dbo_NUCADDRESS.PROPERTY_NO = dbo_NUCASSOCIATION.KEY1 WHERE dbo_NUCNAME.GIVEN_NAMES like 'John' AND dbo_NUCNAME.NAME like 'Smith'

[June7]

Why are you setting form RecordSource?

Why would you use strNAMESearch in both the constructed SQL statement and in the OpenForm argument?

How many customers are there? Why not just a combobox listing customers on the main form?

I have never seen Item property used.