Add or Remove Choices from a Pull Down

Many times programs have a need to change the values in a pull down box. These changes usually fall into one of three different categories.

1) Add a New Value

2) Delete an Old Value

3) Change the Wording on an Old Value

How you do this depends upon how the pull down (combo box) is set up on your input screen.

Pull Downs (Combo Boxes)

Pull downs have what is known as a record source. That's where they get their values. There are three ways they can get their values (Record Source Type):

1) Use the contents of a table (Table/Query)

2) Have the values typed into a list (Value List)

3) Pull the values from the system (Field Names)

Here we are concerned with only the first two, since the third is automatic (pulling the list of fields from a table).

Use the contents of a Table (Table/Query)

This doesn’t have to be done from the Master front end

There is a technical way of finding how a pulldown gets its values and a practical way. What I’m going to show you is how to use the practical way. This requires one or two steps.

The first step is to see if there is already a shortcut to adding values to the pull down list you want to change. To do this go to the Admin choice on the Ribbon bar and then either choose:

The Setup Table Icon

Or

Click on Setup Functions and then choose Setup Tables

This gives you a List Box filled with the pull downs that you can change. You can scroll through this list and see if the pull down you want to change is listed, e.g., Offices. Click on that row to open the table and modify it. (More about this later)

If you don’t find the table you want to change listed, we have to go to a second step. This is looking to see if there is a table supporting the pull down that isn’t listed.

(a) Press F11 to open the Navigation Pane. Type sub into the search field. Then look through the list of tables to see if one fits what you’re looking for. For Example: subOffice holds the list of Offices, subFunds holds the list of Funding Codes, subRace2 holds the list of races used for the second race field, etc. You can double click on the one you want and the table will open up and show you the values.

(b) Warning: If there isn’t a table listed in the Setup Tables above, but you find a table using this method, contact us before you modify it. Some values are so important to how the program works we suggest you don’t change them without talking to us. SubPcode is an example of such a table.

Second Step Modifying the table - The next step is to make the change that you want.

Add A Value

The easiest thing to do is to add a new value. Go to the end of the list to find a blank row. Type in the value you want to add and the description of the value (if the table has two fields). For example:

       In the subLive table you would type in a

            C          Circus

            Since this table stores a code representing a value.

            In the subCCounty table you would just type in the value

            Washington

            Since this table stores the complete value.

Certain common sense rules apply such as you can’t have duplicates, if the code is three letters long try and use three letters, if it uses numbers use a number, etc.

Modify a Value

You should be careful here. If you are merely cleaning up a meaning OR there hasn’t been any data entered using that code, it is safe to modify the name of a code. For example:

Changing Domestic Violence to Dom. Viol. Married/Unmarried doesn’t really change things and shouldn’t harm existing data.

What you don’t want to do is to change:

            How many days’ notice?

            To

            How many children do you have?

This would make some of your old clients appear to have 30 children when they actually had 30 days’ notice under the old meaning for the field.

Delete a Value

Extreme caution is advised here. There are several ways to get rid of a value in a table and we will explore each.

Delete the Row – This is dangerous and sometimes impossible if there is already data in it. If you have a code 22 which means Ford Foundation and you delete it, a year from now no one will remember what code 22 means in your old data. Where there is referential integrity, the program won’t even allow you to delete a value that has data entered in.

CurrentR – Many tables have a CurrentR (current record) field. This is a field that allows you to shut off a value so users don’t see it, but leaves the value in the table for reasons of report writing and knowing what the old data meant. An example would be funding codes. If you aren’t getting United Way funding anymore, you don’t want people to enter that value on a new client intake. However, you want to know which of your old cases were funded that way. If you put a 0 (zero = false) in the CurrentR field it will not show up on any funding code pull down. However, you can still do reports and look directly at the table to know what it meant.

Modify the Row – You could change the value in the table from:

7          United Way

To

7          Don’t Use United Way

This can work, but isn’t the best way to do things, CurrentR being much better.

Value List

This does have to be done on the Master front end If you can’t find a matching table for the pull down you want to change, it is possible that the values are hard-coded in. The following steps allow you to make changes to these pull downs.

1) Go to your Navigation Pane to find the form that the pull down is on.

2) Press F11 to Open the Navigation Pane

3) Type in the form name in the search box, e.g., inpClients

4) Right click on the name of the form when it appears in the list and choose Design View.

5) When the form opens in the design view, go to the pull down you want to change and click it (the white entry area).

6) If the property box is not open on the right, right click and choose Properties. Otherwise, skip this step.

7) Click on the Data Tab. If there is no supporting table, the Record Source Type will say Value List. Otherwise it will give you the name of the table to use as described above.

8) If the Record Source Type is a Value List, the field will give a list of typed in values. Right click on that row and choose Zoom to see the list.

Example: Assume the sex field has

“M”;”Male”;”F”;”Female”;”G”;”Organization”;”T”;”Transgender”

And you need to add Z, Martian. Merely type that value in, wherever you want it to appear in the list (if you put it at the end, it will be at the bottom of the pull down list.) For example:

“M”;”Male”;”F”;”Female”;”G”;”Organization”;”T”;”Transgender”;”Z”;”Martian”

9) Save your Master front end and distribute.

10) This assumes that there is no validation on the values put into that field. That protection could be on the form, on the table or in the code. The Validation rules would have to be modified to allow the new value.