Don't like Combo Box

[Guest]

what other methods are available in access to search for records (such as a
client name) on a form besides a combo box?
what i would like to do, but having problems with the coreect code is to do
the following:

1. have a form1 with a text box and command button to search
2. subform2 (unrelated to form 1) with client information in datasheet view.
3 want that when user type a name in text box and click command button the
corresponding name(s) are highlighed in datasheet and text box.

need some help

derrick

 

[Jeff Boyce]

Derrick

As it happens, the combo box is a tool well-suited to what you are
describing wanting to do. What is it about the combo box that isn't working
well for you?

Jeff Boyce
<Office/Access MVP>

 

[Guest]

i expect the database to grow with surnames repeating. i therefore would want
to see about 10 instances of the same surname. as i understand that is
difficult with a combo box

 

[Jeff Boyce]

Derrick

Without actual data, it's a little difficult to imagine, but let me try an
example...

I have a table with thousands of names. Some of those persons have the same
last name. To pick the person they want to work with, I have the user
select a name from a combo box.

I build the combo box to hold the rowID, and a concatenated field containing
[LastName] & ", " & [FirstName], sorted in alphabetical order. And since
there can be more than one "Smith, John" in my table, I also include a bit
more information in the combo box ... say, DOB, street address, etc.

I keep adding other bits of info until each person can have their own unique
combination of data displayed when the user tries to select a single
individual.

Does any of this match up with what you are trying to do?

 

[Vincent Johns]

i expect the database to grow with surnames repeating. i therefore would want
to see about 10 instances of the same surname. as i understand that is
difficult with a combo box

Like Jeff Boyce, I don't see any problem with using a combo box, but
perhaps a list box would do as good a job, if you don't need to enter
new data into the field. You can adjust the size of either type of
control, and your user can use a vertical scroll bar to navigate. For
example, in a list box, the scroll bar appears automatically if there
are more names than will fit.

It wasn't obvious exactly what you want to do, but I set up an example
database to do some of it; perhaps this will help.

I set up one Table with the following contents:

[People]
PeopleID Name Birthday
----------- ---------- ---------
980846942 Valerie 12/1/1989
-1527915023 Jolene 6/12/1979
-1416070224 Jim 5/13/1986
-943679097 Murgatroyd 7/7/1987

Based on this Table, I wrote a Query to display people's names (for use
in the list box on the Form):

[Q_Birthdays] SQL:
SELECT People.PeopleID, People.Name,
Format$([People]![Birthday],"mmmm") AS [Month],
Format$([People]![Birthday],"d") AS [Day]
FROM People
ORDER BY People.Name;

[Q_Birthdays] Datasheet View:
PeopleID Name Month Day
----------- ---------- -------- ---
-1416070224 Jim May 13
-1527915023 Jolene June 12
-943679097 Murgatroyd July 7
980846942 Valerie December 1

In the list box on the Form, I set the properties so that only the
values in the [Name] field appear.

I also set up a Query for the subform:

[Q_Message] SQL:
SELECT Q_Birthdays.PeopleID,
[Q_Birthdays]![Name]
& "'s birthday is on "
& [Q_Birthdays]![Month] & " "
& [Q_Birthdays]![Day] & "." AS Message
FROM Q_Birthdays;

[Q_Message] Datasheet View:
PeopleID Message
----------- ------------------------------------
-1527915023 Jolene's birthday is on June 12.
-1416070224 Jim's birthday is on May 13.
-943679097 Murgatroyd's birthday is on July 7.
980846942 Valerie's birthday is on December 1.

(I omitted the ORDER BY clause, but it doesn't matter in this case, as
only one [Message] will appear at a time in the subform.)

On the Form, I placed a list box and a subform containing a text box.

The following settings cause the list box to display the [Name] field in
the second column but to match the values of the [PeopleID] field in the
first column, and the names appear in alphabetical order:

Form: F_Birthdays
List Box: lbxPeopleID
BoundColumn: 1
ColumnCount: 2
ColumnWidths: 0;1
Enabled: True
Locked: False
RowSource: Q_Birthdays

The subform is linked to the list box via the [PeopleID] field:

Form: F_Birthdays
Subform/Subreport: F_Birthdays subform
Enabled: True
LinkChildFields: PeopleID
LinkMasterFields: lbxPeopleID
Locked: True
SourceObject: Query.Q_Message

Form: F_Birthdays subform
Text Box: Message
ControlSource=[Q_Message]![Message]
Enabled: True
Locked: True

Clicking on "Jim" in the list box causes the subform to display the
message "Jim's birthday is on May 13."

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.

 

[Larry Linson]

i expect the database to grow with
surnames repeating. i therefore would
want to see about 10 instances of the
same surname. as i understand that is
difficult with a combo box

Basing the Combo Box on a Unique Values Query (SELECT DISTINCT in SQL) will
eliminate the duplicates. You can then use some other Field (or Fields)
shown in a second Combo Box (or Boxes) to choose which record to use.

Or, you can display mutliple columns, along with the surname, to allow the
user to choose the correct record...

Larry Linson
Microsoft Access MVP