CODEWITHSUNDEEP
ms-accessvbams-access-2010
user2217671
user2217671

Reputation:

building a search form in Access 2010

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.

Upvotes: 3

Views: 16296

Answers (1)

Gord Thompson
Gord Thompson

Reputation: 123549

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            [email protected]
2   Loblaw      Bob             [email protected]
3   Kingsley    Hank            [email protected]
4   Thompson    Hunter S.       [email protected]
5   Squarepants Spongebob       [email protected]
6   O'Rourke    P. J.           [email protected]
7   Aldrin      Edwin "Buzz"    [email protected]

Form layout:

DesignView

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.

FirstOpened

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.)

SearchResults

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

EmailFound

Upvotes: 9

Related Questions