?
Solved

Defining contents of Combo box

Posted on 2016-08-08
4
Medium Priority
?
90 Views
Last Modified: 2016-08-09
I have an old application that I would like to do a minor mod on.

Currently I have a form with a passenger selection combo box.
It appears as below.  I am saving the Passenger ID after selection.  
But I don’t necessarily have to view that in the combo box – just the initials and surname for selection.

 Combo box
Anyway, the problem is that the list has got rather long and the users would like to be able to start to type in the combo box surname and see options appear for selection.
I cannot quite figure out how to do that. Currently, of course, that will only work for typing in the ID.

The properties of the Combo box include the following….
Name = cboPassengerID
Control source = tblJourneys.Passenger ID
Row source = SELECT tblPassengers.[Passenger ID], tblPassengers.Initials, tblPassengers.Surname, tblPassengers.Inactive FROM tblPassengers WHERE (((tblPassengers.Inactive)=False)) ORDER BY tblPassengers.Surname;

Any guidance would be most welcome

Regards
Richard
0
Comment
Question by:rltomalin
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
4 Comments
 
LVL 48

Assisted Solution

by:Dale Fye
Dale Fye earned 500 total points
ID: 41747363
Change the RowSource of the combo box to something like:

SELECT tblPassengers.Surname,
tblPassengers.Initials,
tblPassengers.Inactive,
tblPassengers.[Passenger ID]
FROM tblPassengers
WHERE (((tblPassengers.Inactive)=False)) ORDER BY tblPassengers.Surname;

Then change the BoundColumn property of the combo box to 3 (zero based)
Then change the Column Widths field of the combo box so that the last column (Passenger ID) has a width of zero.
1.  To hide the [Passenger ID] field, change the column widths property of the combo box so that the first field has a width of zero (0).

2.
0
 
LVL 31

Assisted Solution

by:Helen Feddema
Helen Feddema earned 500 total points
ID: 41747779
A little enhancement to Dale's suggestion:  Make a concatenated LastNameFirst field to display the passenger name as (for example) "Almy, M A",
0
 
LVL 38

Accepted Solution

by:
PatHartman earned 1000 total points
ID: 41747906
I prefer to have the bound column first in the query as you have it.  All you have to do is to change the column widths to make the first column width "0".  That will hide the ID field.

I would take Helen's suggestion one step further and order the combo by the lastname/first name field.

@Dale, although the RowSource is a zero based array when you work with it in VBA, for purposes of the interface, it is 1 based.  So, in your example, the bound column would be 4 rather than 3.
0
 

Author Closing Comment

by:rltomalin
ID: 41748680
Thanks for the help - just the job.  I actually used a combination of all three responses and it's fine.
0

Featured Post

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…

800 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question