Query/List Box on Forms

  • Thread starter Thread starter Michelle
  • Start date Start date
M

Michelle

I have a contact database I'm trying to create. I created
a query to filter contacts with the last name beginning
with "a". I want to be able to create either a command
button or toggle button that when clicked, the form I
created for the ContactID information opens up. The form
contains a list box that will show the contacts with last
names beginning with "a" and when a person names is
clicked on, it populates the contact info above on the
form.

Thanks,
Michelle
 
-----Original Message-----
I have a contact database I'm trying to create. I created
a query to filter contacts with the last name beginning
with "a". I want to be able to create either a command
button or toggle button that when clicked, the form I
created for the ContactID information opens up. The form
contains a list box that will show the contacts with last
names beginning with "a" and when a person names is
clicked on, it populates the contact info above on the
form.

Thanks,
Michelle
.
Click to expand...
Hi Michelle,
1. set up search form with the following controls:
textbox (txtCriteria)

listbox (lstSearch) has row source a query (qrySearch)
with the fields, ContactID, First Name, Last Name. Set
column widths to 0cm;3cm;3cm (or equivalant)

command button (cmdDisplay)

2. insert the following code in the textbox afterupdate
event:

private sub txtCriteria_AfterUpdate()
dim strSQL as string
const BASE_SQL as string = "qrySearch"

if len(txtCriteria)>0 then
strSQL="SELECT " & BASE_SQL & ".* FROM " & BASE_SQL " _
& "WHERE ((Last Name) LIKE '" & txtCriteria & *');"
else
strSQL=BASE_SQL
end if

lstSearch.rowsource=strSQL
end sub

3. insert the following code in the command button click
event:

private sub cmdDisplay_Click()
dim strWhere as string

if isnull(lstSearch) then
msgbox "please select a contact"
else
strWhere="(ContactID)=" & lstSearch

docmd.openform "frmContact",,strWhere
end if
end sub

Hope the above helps
Luck
Jonathan
 
Back
Top