Looking for help with a search function

[EnglisAP]

I am working on a database in Access 2010 and i have a client information table that has multiple information i have designed a form to search and display the information. The only problem i am having is the actual search.
I want to be able to search by the clients first and last name or by the account number. Currently the Account number search works perfectly when i click on the button. The major issue i have is that the name search will work for now but once i add a new client with the same name as another it will not function properly. The code is as follows for my search button:

Private Sub searchbutton_Click()
Dim first As String
Dim last As String
Dim account As String
Dim test As String
Dim test2 As String

If Not AccountTXT.Value Then
    'find info with account number
    first = DLookup("[First Name]", "Client Information", "[Account Number] = AccountTXT.value")
    last = DLookup("[Last Name]", "Client Information", "[Account Number] = AccountTXT.value")
    account = AccountTXT.Value
    
ElseIf Not fnameTXT.Value Then
    If lnameTXT.Value = Null Then
        MsgBox "Both First and Last Name are required.", vbOKOnly, "Required Info"
    Else
        first = fnameTXT.Value
        last = lnameTXT.Value
        test = DLookup("[Account Number]", "Client Information", "[Last Name] = lnameTXT.value")
        test2 = DLookup("[Account Number]", "Client Information", "[First Name] = fnameTXT.value")
        If test = test2 Then
            account = test
        Else
            MsgBox "Account Number can not be found.", vbOKOnly, "ERROR"
        End If
    End If
Else
    MsgBox "Either first and last name or account number is required.", vbOKOnly, "Required Info"
    
End If

I guess what i am looking for is a way to have the multiple solutions pop up and ask the user which one to choose.
For example if i had 3 John Doe's it would show the three clients and their phone numbers accordingly so we can select the right one to display the rest of their information.

Any suggestions?

 

[d_profesor]

I think you're right. Dlookup function return single value, not a recordset. You could do it by creating a dialog form with a listbox control on it. Set simple SELECT query for listbox row source on the form load event. The query could be:

 Private Sub Form_Load()
     me.list0.rowsource="SELECT*FROM [tbl_Client_Name] WHERE [First Name]='" & me.OpenArgs & "'"

     me.list0.requery

end sub

Then in searchbutton_Click(), open the dialog form with variable first or last as openargs :

docmd.openform "Dialog Form",,,,,,First

To view client info detail you could do it simple by open client form on listbox double click event..again with docmd.openform method..

 

[John Big Booty]

Perhaps the Dynamic Multi search tool here will help.

 

[d_profesor]

Perhaps the Dynamic Multi search tool here will help.

It's really good John, perhaps i will use it for me..

 

[John Big Booty]

It's really good John, perhaps i will use it for me..

Glad you like it and hope you can us it to good effect.

 

[John Big Booty]

It's really good John, perhaps i will use it for me..

You can also improve it by adding the ability to change the sort order by clicking the various column headers.

 

[EnglisAP]

Thanks to both of you for the help i will look into both solutions and see which fits our form best.

 

[EnglisAP]

I think you're right. Dlookup function return single value, not a recordset. You could do it by creating a dialog form with a listbox control on it. Set simple SELECT query for listbox row source on the form load event. The query could be:

 Private Sub Form_Load()
     me.list0.rowsource="SELECT*FROM [tbl_Client_Name] WHERE [First Name]='" & me.OpenArgs & "'"

     me.list0.requery

end sub

Then in searchbutton_Click(), open the dialog form with variable first or last as openargs :

docmd.openform "Dialog Form",,,,,,First

To view client info detail you could do it simple by open client form on listbox double click event..again with docmd.openform method..

in regards to this i seem to misunderstand the coding should i put the varible that stores the first name in the location of the quotes for the [First Name] = "" segment?

 

[EnglisAP]

I have decided to go with the Dynamic Search but once i put it into my form every time the form load it asks for me to Input Parameter Value? it also does this when i change data in the search field. Im not sure what i missed

Any suggestions?

 

[John Big Booty]

I have decided to go with the Dynamic Search but once i put it into my form every time the form load it asks for me to Input Parameter Value? it also does this when i change data in the search field. Im not sure what i missed

Any suggestions?

It sounds as if you are missing a reference somewhere in your query. If you can post a copy of your DB ('03 format, for preference) I'll take a look at it.

 

[EnglisAP]

thanks for the offer i found the missing reference although i have found another issue. i need to use the dlookup function to find some of the other information that needs to populate according to the information selected from the search. the issue i have is that when i put in the code as it was working in my original button the text field says it is an invalid string and talks about the length as the issue. i have tried to use the expression in the onclick and afterupdate choices any ideas to figure this issue out

the code i need to embedded into the text field is as follows:
phone2tb.Value = DLookup("[Secondary Phone Number]", "Client Information", "[Account Number]= accounttb.value")