Searching

[swedish_mania]

Hi,

I have a form based on a table of buyers, i also have a copy of this form which includes a search button, so you can type in a surname and it displays the records above. I have got it to work, but when it finds the record it still says "Record not found for: [Surname].

I think its just a minor adjustment to the code that is need.

Once i have it i want to advance on this by instead of only allowing the user to search via surname, i want a combo box so you can search by forename, surname, postcode etc. Any suggestions will be helpful thanks.

Heres the code so far.

Private Sub Search_Click()
    Dim BuyerID As String
    Dim strSearch As String
    
If IsNull(Me![txtSearch]) Or (Me![txtSearch]) = "" Then
        MsgBox "Please enter a valid Surname!", vbOKOnly, "Invalid Search Criteria!"
        Me![txtSearch].SetFocus
    Exit Sub
End If
    
    DoCmd.ShowAllRecords
    DoCmd.GoToControl ("Surname")
    DoCmd.FindRecord Me!txtSearch
    
    Surname.SetFocus
    BuyerID = Surname.Text
    txtSearch.SetFocus
    strSearch = txtSearch.Text
    
    If BuyerID = strSearch Then
        MsgBox "Record Found For: " & strSearch, vbOKOnly, "Search Results."
        Surname.SetFocus
        txtSearch = ""
    
    Else
        MsgBox "No Records Found For: " & strSearch & " - Please Try Again.", vbOKOnly, "Invalid Search Criteria!"
        txtSearch.SetFocus
    End If
End Sub

 

[agehoops]

just a quick question on this, is this only ever going to return one result? Say for instance you search for the name Bob and you have 5 Bob's stored in the database, is it not going to return just the first one it finds? I used to use almost this exact code and had to get rid of it for this very reason.

 

[swedish_mania]

I know what you mean, one result will be returned, then its a case of tabbing across for the other 4 bob's. So its not the best peice of code, but its somewhat serving it's purpose to saome extent. If you can offer any alternatives i'll greatly appreciate them.

Thanks

 

[agehoops]

ok i've just created a little database which should be more or less what you're looking for as far as i can tell. Takes more to setup but works brilliantly, i'll up load it later cos i've gotta run.

 

[agehoops]

Ok here you go, this is the search function i now use, but i've modified it for your situation so there is a drop down box to select what you search, if you want a full explaination on how it all works just let me know, no problem at all

View attachment SearchDB.zip


Hope this helps, sorry it took so long

 

[swedish_mania]

Thank you agehoops. Works better than what i was coming up with, much more than i could of asked for, thanks again.

 

[agehoops]

Anytime, glad i could help, hope it wasn't too confusing

 

[swedish_mania]

Not confusing at all, nicely laid out. Added to it to include being able to search via telephone, mobile, post code etc. Works a treat. Thanks