ComboBox is for looking up records

[TizTIz]

I have a form that want to add a combo box that will show my list of clients
(client names located in a table), when you select a client it will show all
records for the particular client.

This is most likely a very simple process, but I'm having a heck of a time
figuring out this out. How would I do this?

 

[missinglinq via AccessMonster.com]

First you must have your form set up to display the record you want to
retrieve, i.e. you must have fields set up with the appropriate Control
Sources. Then simply:

Add a combo box to your form. The Combobox Wizard will pop up
Select "Find a record based on the value I selected in my combobox."
Hit Next.
Click on the field you're searching by (from the Record Source of the form)
to move it to the right side.
Hit Next.
Size the column appropriately.
Hit Next.
Name the combobox.
Hit Finish.

Now you can drop the combobox down and scroll down to the item to search by,
or you can start to enter the item, and the combobox will "autofill" as you
type.
Hit <Enter> and the record will be retrieved.

 

[Douglas J. Steele]

I believe that needs to be

Dim rst As DAO.Recordset

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

The combo box should be an unbound field used only for searching.

Create a Select statment for the row source property of the combo.
Something like:
SELECT ClientID From ClientTable;

This will give you a drop down list of all the client's in the table.

Now use the combo's After Update event to locate and display the selected
client:

Dim rst As Recordset

Set rst = Me.RecordsetClone
rst.FindFirst "[ClientID] = " & Me.cboClientSearch
If Not rst.NoMatch Then
Me.Bookmark = rst.Bookmark
End If
Set rst = Nothing

That will take care of looking up existing clients. Once you get that
working, you may want to explore how to handle adding new clients when the
client doesn't exist in the table, but get this working, first.

Now, another thing that will probably pop up is that something like
ClientID
may not be meaningful to the user - it may even be an Autonumber field.
In
this case, you will still need the field to do the search, but you will
want
to display the client name. Add the name to the combo's row source query:

SELECT ClientID, ClientName From ClientTable;

Set the following properties of the combo:
Bound Column 1
Column Count 2
Column Width 0";1.5" (This will make the ID invisible and show only the
name)

This will not change The After Update event code.

Post back if you have further questions.

I have a form that want to add a combo box that will show my list of
clients
(client names located in a table), when you select a client it will show
all
records for the particular client.

This is most likely a very simple process, but I'm having a heck of a
time
figuring out this out. How would I do this?