Sorting via a surname

[partthethird]

As part of my uni coursework, I have to create a database that contains members and bookings for a court (squash, tennis, etc). The basic layout and everything is done, and I need to know how to search, for example, a surname field without the user having to type in a name first.

So: say the user types 'S' into a text field linked to the surname field in a table, all surnames beginning with 'S' should be displayed. Every subsequently entered character should narrow the search and display the closest match in the text field. ('S' = Stanswick, Schofield. 'Sc' = Schofield, and so on).

I have tried to make it as clear as I can. No code, because it's not done yet =)

 

[PHV]

Have a look at the Like operator.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[partthethird]

That's more or less what I want. But I want it to be an automatic thing, so that there's no dialogue pops up when the text field is clicked. I managed to get the Like query working OK, and now that is the next step. Also, it needs to update all related details on the form, so if the name changes, the member number changes respectively.

Now that I know how to make the actual query, the second step shouldn't be too hard.

Thankyou very much for your post, PHV. It has proven invaluable =)

 

[ssatech]

One way is to create a form say for Members and have the record source based on a query like so:

-RecordSource
SELECT Members.*, Members!Surname AS [Last]
FROM Members
ORDER BY Members!Surname DESC;

-Make the form continous

-Maybe on the form footer of this form create an unbound textbox called txtCustomFilter. On the afterupdate event of this field something like:

Private Sub txtCustomFilter_AfterUpdate()
On Error Resume Next
Dim strFilter As String
strFilter = Me!txtCustomFilter

Me.Filter = "Surname Like '*" & strFilter & "*'"

End Sub