Search List

S

SC

I'd like to add a list to a database that I have that allows me to pull up a
list of client names in alphabetical order then click on a name to go to
that record on my main form. I have the following fields that I would like
to display on the list: ClientID, Lastname, Firstname, MI, SSN. Deos
anyone know how to do this or know of a reference that I could look at for
examples?

Thanks,

S
 
K

Ken Sheridan

Create a dialogue form with a combo box or list box control with a RowSource
property such as:

SELECT ClientID, Lastname, Firstname, MI, SSN
FROM Clients
ORDER BY LastName,FirstName;

Set the control's BoundColumn property to 1, its ColumnCount property to 5
and its ColumnWidths property to whatever dimensions are appropriate. If you
want to hide the ClientID column (normally done) and just see the names etc
set the first dimension to 0, so the ColumnWidths property might be something
like:

0cm;3cm;3cm;2cm;2cm

or equivalent in inches. If using a combo box set its ListWidth property to
the sum of the column widths, i.e. 10cm for the above example. To move to
the selected record on the bound form put code along these lines in the list
or combo box's AfterUpdate event procedure, which finds the client record in
a clone of the main form's recordset, then synchronizes the form's bookmark
with the recordset's bookmark, finally closing the dialogue form:

Dim frm As Form
Dim rst As Object
Dim strCriteria As String

strCriteria = "ClientID = " & Me.cboFindClient

Set frm = Forms("YourMainFormName")

Set rst = frm.Recordset.Clone

rst.FindFirst strCriteria
frm.Bookmark = rst.Bookmark

DoCmd.Close acForm, Me.Nme

where 'cboFindClient' is the name of the combo or list box and
'YourMainFormName' is the name of the bound form. Open the dialogue form
from a 'Search' button with code in its Click event procedure like this:

DoCmd.OpenForm "YourDialogueForm", WindowMode:=acDialog
 
J

John Vinson

I'd like to add a list to a database that I have that allows me to pull up a
list of client names in alphabetical order then click on a name to go to
that record on my main form. I have the following fields that I would like
to display on the list: ClientID, Lastname, Firstname, MI, SSN. Deos
anyone know how to do this or know of a reference that I could look at for
examples?

Thanks,

S
Click to expand...

This is simply a Combo Box on a form. The COmbo Box Wizard will help
you create this combo; use the option "use this combo to find a
record".

John W. Vinson[MVP]
 
Top