building a search form in Access 2010

ms-access, ms-access-2010, vba

Solution

Here is a simple example that uses a List Box:

Table: Clients

ID - AutoNumber LastName - Text(255) FirstName - Text(255) Email - Text(255)

Test data:

ID  LastName    FirstName       Email
--  ----------  --------------  ------------------
1   Thompson    Gord            gord@example.com
2   Loblaw      Bob             bob@example.com
3   Kingsley    Hank            hank@example.com
4   Thompson    Hunter S.       hunter@example.com
5   Squarepants Spongebob       ss@example.com
6   O'Rourke    P. J.           pj@example.com
7   Aldrin      Edwin "Buzz"    buzz@example.com

Form layout:

VBA module for this form:

Option Compare Database
Option Explicit

Private Sub Form_Load()
Me.lstSearchResults.RowSource = ""
End Sub

Private Sub btnSearch_Click()
Me.lstSearchResults.SetFocus
Me.lstSearchResults.Value = Null
Me.lstSearchResults.RowSource = _
        "SELECT ID, LastName, FirstName FROM Clients " & _
            "WHERE LastName LIKE ""*" & DQ(Me.txtSearchLastName.Value) & _
                "*"" AND FirstName LIKE ""*" & DQ(Me.txtSearchFirstName.Value) & "*"""
End Sub

Private Function DQ(s As Variant) As String
' double-up double quotes for SQL
DQ = Replace(Nz(s, ""), """", """""", 1, -1, vbBinaryCompare)
End Function

Private Sub btnLookupEmail_Click()
If IsNull(Me.lstSearchResults.Value) Then
    Me.txtEmail.Value = ""
Else
    Me.txtEmail.Value = DLookup("Email", "Clients", "ID=" & Me.lstSearchResults.Value)
End If
End Sub

When the form is first opened, everything is empty.

Typing "thompson" (without the quotes) and clicking `btnSearch` populates the List Box with clients `WHERE LastName LIKE "*thompson*"`. (If you look at the code you'll see that it will also match on FirstName if you supply one.)

Select one of the items in the List Box and click `btnLookupEmail` and the email address is displayed in the Text Box below.

Problem

I am trying to make search customer form for access 2010. I like to have an option group based on a query search. I made a search query looking for first name or last name. two textboxes are also present on the form to fill up the query inputs (1st and last name) I need option group so that I can select the resulted name for booking purposes. In the past I have made an indirect way of doing this using subform and a checkbox. Then loading both results on a subform and checkbox (requery) so the user only has to select on the checkbox. But this time I want the options to be the query result itself! Please help.

Original source