How to build a combo box with 1300+ options?

[davisjr6]

Hi All!

I'm a returning novice working on building a database to house Hospital Acquired Infections. I'm seeking advice on how to build a combo box with a list of over 1,300 pathogens. There are three fields on a form requesting Pathogen identification and as it stands I have 1,300 + pathogens to chose from. Is a combo box an appropriate way to do this? I have the pathogens in an excel spread sheet and in a word document.

How do the experts suggest to do this?

Many thanks!

[patjones]

Hi,

I have used combo boxes with this many items, but in those situations the user will start typing in what they want and have Access pick out the value as it is being typed. Scrolling through the actual list with this many items is possible but not practical.

You can import the Excel sheet into the database, and have a table that holds the pathogen list. Then you'll make the table the combo box record source.

Ideally how you would set this up is to have an ID number for each pathogen, and then the pathogen name. When you bind the combo box to the table, you'll have two columns (one for ID and one for name)...but set the column widths to 0" and 2" for instance, so as to make the ID column disappear (it will still be a part of the combo box, just not visible).

By setting the bound column property to 1 (the ID), selecting something from the combo box will cause the ID number to be stored in the record rather than the actual pathogen name. This is the clean approach that will prevent pathogen names from getting duplicated record after record.

Pat

[davisjr6]

Hi,

I have used combo boxes with this many items, but in those situations the user will start typing in what they want and have Access pick out the value as it is being typed. Scrolling through the actual list with this many items is possible but not practical.

You can import the Excel sheet into the database, and have a table that holds the pathogen list. Then you'll make the table the combo box record source.

Ideally how you would set this up is to have an ID number for each pathogen, and then the pathogen name. When you bind the combo box to the table, you'll have two columns (one for ID and one for name)...but set the column widths to 0" and 2" for instance, so as to make the ID column disappear (it will still be a part of the combo box, just not visible).

By setting the bound column property to 1 (the ID), selecting something from the combo box will cause the ID number to be stored in the record rather than the actual pathogen name. This is the clean approach that will prevent pathogen names from getting duplicated record after record.

Pat

Thanks Pat!

In the interim I asked the question, I figured out how to put to use your advice and it's working minus the ID field.

Question for you: The information we will be storing concerning pathogens is intended to be exported to a CDC reporting database, where the actual pathogen name is used. Should I go ahead as you suggested and create an ID field. If so, what do you mean by ID a number or abbreviation (e.g, Adenovirus=ADV) ?

Much thanks for your help, it was crystal clear!

[NeoPa]

It's good that you're making progress without the need for hand-holding.

In answer to your specific questions :

1. Yes. Use an ID field in place of duplicating text around the place. It is better on many levels.
2. Use an AutoNumber field in place of anything else. With that number of records particularly, you don't want to be left with the job of assigning matching abbreviations to each record. If you had pre-existing, unique, abbreviations that could work, but generally AutoNumber is used for such situations.

Welcome to Bytes!

[patjones]

I'm happy that it works for you.

As NeoPa suggests you can just AutoNumber the IDs. When you go to export the records for CDC reporting purposes, you can build a simple query to look up the ID in your pathogens table and pull the actual name.

Pat

[davisjr6]

Friends,

Sorry, but I'm having troubles executing the Auto Number suggestion.

I imported the Pathogen List to TblPaths from excel and then created a RecordID (Auto Number). However, when I input the pathogen into the form, the field in the table reads the entire path name.

I typed column widths 0";2" initially, but was unable to read my path list from the drop down box in the form, so switched it to 2";0" with no luck.

What am I doing wrong?

Are these the appropriate steps to follow?

Step 1: Import Pathogens into Table

Step 2: Add RecordID field (Autonumber) to Table

Step 3: Go in design view of existing form and change pathogen control source, column widths 0"2",ensure column bound property=1, and then press save.

Step 4: Do I need to create relationship with Pathogen table and the table I want the values to be entered into?

[NeoPa]

I would start with the design of the table. If you have an AutoNumber field in there (called [ID] or such like) then any data imported will also be associated immediately with an ID.

I'm afraid I don't have a very clear understanding of what you're attempting on your form. If you simply want a bound ComboBox that shows the pathogen name but stores the associated ID then, assuming the ID field is first, use Column Count = 2, Column Widths = 0;2 and Bound Column = 1.

PS. Step 4 threw me somewhat, as it's a question.
It is not necessary, though in some circumstances it may be advisable, to create a relationship between the tables.

[patjones]

Friends,

Sorry, but I'm having troubles executing the Auto Number suggestion.

I imported the Pathogen List to TblPaths from excel and then created a RecordID (Auto Number). However, when I input the pathogen into the form, the field in the table reads the entire path name.

I typed column widths 0";2" initially, but was unable to read my path list from the drop down box in the form, so switched it to 2";0" with no luck.

What am I doing wrong?

Are these the appropriate steps to follow?

Step 1: Import Pathogens into Table

Step 2: Add RecordID field (Autonumber) to Table

Step 3: Go in design view of existing form and change pathogen control source, column widths 0"2",ensure column bound property=1, and then press save.

Step 4: Do I need to create relationship with Pathogen table and the table I want the values to be entered into?

You could just assign the IDs in Excel by inserting a column next to the pathogen names and then numbering the rows one through 1300. This way all you will need to do is the import and you'll be set.

When the table is imported, you should still set the ID column to AutoNumber so that any future additions to the table are numbered according to what is there already.

Pat

[davisjr6]

Perfect! It works now!!!

Thanks a mil to you both (NeoPa and Pat)!!!

[NeoPa]

I don't believe that will work Pat. You get the error :

Once you enter data in a table, you can't change the data type of any field to AutoNumber, even if you haven't yet added data to that field.

[patjones]

I don't believe that will work Pat. You get the error :

Yikes. I had a feeling I was jumping the gun. I very rarely use AutoNumber and shouldn't have made that assumption.

[davisjr6]

I don't believe that will work Pat. You get the error :

That's true. I just created the ID field (AutoNumber) and it worked like a dream.

Thanks again!

[davisjr6]

I don't believe that will work Pat. You get the error :

Just a quick thought:

Say the IP Nurse enters a pathogen that is not in the list, will a number be automatically assigned to it? How will the data from that field be recorded.

Given the nature of this field, these Pathogens are likely to change often. I just wanted to make sure that I've accounted for such a possibility.

THANKS!

[NeoPa]

That is not handled automatically, no.

There are ways to do so though. You may even find some discussed in this forum if you look. Otherwise, I suggest you give some thought first to exactly what you want to happen and exactly how it should present, then post a separate thread on that topic.