Adding search facility on forms 1

[Only1Abhi]

Hi.
I tried to get my head around this but it simply wouldn't work.

My Table: "tblCustomer"
My Form: "frmCustomer"

In Form View, I want to include a search engine facility. It needs to be designed in such a way that I type a surname of a person (in a text box) that is already in the table and all the people with that surname should appear on the form for me to flick through. Does any1 know how to do this?

Thanks a lot in advance.

But those people who think this is a piece of cake, this isn't the whole problem. Infact, I was thinking of some way where I could get the search results to appear in a box like in a website. (I.E. When I type a typical surname such as Smith in the text box, I want all the Smiths to appear in a box like a list along with their first names so that I can just click on one of them and view their details).

Does any1 have a solution to the first and/or second part of my problem? If so, plz post it here because I've been asking all programmers I know like mad and know1 seems to know the answer to this.

Thanks a lot in advance... any1 who solve the 2nd problem is a true genius!!!

Waiting urgently for replies.
Regards,
Abhi

 

[FancyPrairie]

For the 1st part, in the AfterUpdate event of the text box, add code something like the following. It will filter your form so only those records that match are shown. To Remove the filter, simply select the Remove Filter button on the toolbar.

Private Sub Text2_AfterUpdate()

Me.Filter = "strSurName = '" & Text2.Value & "'"
Me.FilterOn = True

End Sub

As far as the 2nd part goes, create a list box whose RowSource property is set to nothing in design view. In the after update event of the text box, insert code something like this:

Private Sub Text2_AfterUpdate()

lstBox.RowSource = "Select strSurName From YourTable Where strSurName = '" & Text2.Value & "';"

End Sub

 

[FancyPrairie]

Ooops! I hit the Submit button rather than the Edit button. The 3rd part of your problem was to select a name from the list box and display the results. In the OnClick event of the list box, add code something like the following:

Me.RecordSetClone.FindFirst "strSurName = '" & Text2.Value & "'"
If (Not Me.RecordsetClone.NoMatch) Then
Me.Recordset.Bookmark = Me.RecordsetClone.Bookmark
else
Msgbox "Couldn't find name. Shouldn't have got here"
End if

I would have done things a little differently (and you may have). Each customer would be assigned a unique ID (i.e. autonumber). I would include the ID in the list box and do my searches on ID rather than SurName. For Example,

Private Sub Text2_AfterUpdate()

lstBox.RowSource = "Select lngCustomerID, strSurName From YourTable Where strSurName = '" & Text2.Value & "';"

End Sub

The ListBox properties would be set to:

BoundColumn ... 1
ColumnCount ... 2
ColumnWidth .... 0;1 (ID not shown, Surname shown)

In the Onclick event of the List Box, the search would look like this:

Me.RecordSetClone.FindFirst "lngCustomerID = " & lstBox.Column(0)
If (Not Me.RecordsetClone.NoMatch) Then
Me.Recordset.Bookmark = Me.RecordsetClone.Bookmark
else
Msgbox "Couldn't find name. Shouldn't have got here"
End if

 

[FancyPrairie]

I forgot one other thing. You will need to include an OrderBy clause in the Select statements. For Example,

Select lngCustomerID, strSurName From YourTable Where strSurName = '" & Text2.Value & "' OrderBy strSurName;"

I just reread your post and you wanted it to include first and last name. Assuming you want last name first, your select statement would look like this:

Select lngCustomerID, strLastName & ", " & strFirstName From YourTable Where strLastName = '" & Text2.Value & "' OrderBy strLastName;"

 

[Only1Abhi]

Thanks a lot for your reply FancyPrairie.

A lot of helpful information. But I was thinking of making this system without much VB coding that I don't understand.

I'm thinking of giving tuition lessons to others so is there an easier solution?

Any1else with solutions, plz post them here.

Thanks again FancyPrairie and any1else in advance.