combobox from table

[awake2424]

In access 2003 is it possible to create a table of names:

Steve Smith
Jim Jones
Maggie Miller

Then on a form create a combobox that is populated by that table? I have the form created already but can not seem to create the combobox.

 

[someboddy]

What do you mean by "can not seem to create the combobox"? Is access not letting you draw it on the form? Or is the problem in populating it?

 

[awake2424]

The problem is that it is not being populated. It appears on the form but the drop down box is empty. Thanks.

 

[someboddy]

Well, you just need to set up it's "RowSource" property. It can be a quary, or simply the name of the source table.

Also, shouldn't a wizard pop when you create a new combobox?

 

[awake2424]

So it now populates the combobox, but it only shows the primary key.

For example,

Table:

1 Steve Smith
2 Jim Jones

Form display 1 with no name

How can I format it so that the names shows as well?

 

[missinglinq]

Assuming that the first name and last name fields were included when you created the combobox, my guess would be that you've apparently updated to the SP3 service pack and it's buggier than the Great Dismal Swamp! One of the most common bug reported involves comboboxes with disappearing data! The field(s) in the cbo, in the underlying table, have some kind of formatting in place. This is often the simple > used to force UpperCase. Remove any formatting from the fields and things should work short term, but the definitive solution is to install the hotfix for SP3. Here’s the link for the hotfix:

http://support.microsoft.com/kb/945674

 

[someboddy]

There are two methods of doing it.

The first one is to use a quary, and make the name the first field in the quary. The default is to display the first field, so if you make the name the first field, it will be displayed instead of the pk.
Also, after you do that you can set the "BoundColumn" property to 2, so the "Value" property of the combobox will be the pk - assuming you put it as the second field in the quary

The other method is using the "ColumnCount" and "ColumnWidths" properties of the combobox. "ColumnCount" defines the number of columns from the RowSource that are displayed in the dropdown menu. So if you set it to 2, the first 2 fields will be displayed - the pk and the name.
Now, since you only want to display the name, you need to make the pk disappear. This is done with the "ColumnWidths" property. "ColumnWidths" defines the width of every column(dahh). You can put several values separated by semicolons. If you put 0, the field will not be displayed. If you don't put a width for a field, access will do it for you. So all you need to do is to set "ColumnWidths" to 0. That will make the first field - which is the pk - disappear. Since the second field - the name - does not have a defined width, access will set it's width automatically.

 

[awake2424]

I installed the hotfix and still only one column is displayed though the column format in access is set to 3. When I look in the dropdown box there are 3 columns, however when I select an entry only the first column is displayed. Is there a way to have all columns displayed?

 

[Scooterbug]

I installed the hotfix and still only one column is displayed though the column format in access is set to 3. When I look in the dropdown box there are 3 columns, however when I select an entry only the first column is displayed. Is there a way to have all columns displayed?

After the selection is made, only the first column with a width greater than 0 is displayed. You can setup additional text boxes and have those text boxes display the other columns. In the After Update Event of the combo box you would put the following:

me.TxtBox1 = me.cboComboBoxName.Column(1)
me.txtBox2 = me.cboComboBoxName.Column(2)

Remember that when referencing combo or list box columns, the numbering starts at 0. So in the example above, the first text box would be populated with the value of the second column.

 

[boblarson]

After the selection is made, only the first column with a width greater than 0 is displayed. You can setup additional text boxes and have those text boxes display the other columns. In the After Update Event of the combo box you would put the following:

me.TxtBox1 = me.cboComboBoxName.Column(1)
me.txtBox2 = me.cboComboBoxName.Column(2)

Remember that when referencing combo or list box columns, the numbering starts at 0. So in the example above, the first text box would be populated with the value of the second column.

And ACTUALLY you don't need to use code - you can DISPLAY the information in another text box by simply setting the control source of the text box to

=[comboBoxNameHere].[Column](1)
=[comboBoxNameHere].[Column](2)

and then they will change whenever the combo box is updated.

 

[awake2424]

They still will not display correctly. The dropdown box has all three displayed but after selection only the first box appears. I would like to have all three appear but it does not seem to be working. Thanks.

 

[boblarson]

They still will not display correctly. The dropdown box has all three displayed but after selection only the first box appears. I would like to have all three appear but it does not seem to be working. Thanks.

Remember that the fields are ZERO-BASED so

Column(0) means column 1
Column(1) means column 2

etc.

 

[awake2424]

Thats it, thank you very much.

 

[Scooterbug]

Perhaps an example will help.

 

[boblarson]

Thats it, thank you very much.

Glad Scooterbug, Missinglinq and I could help.

Edited: forgot to include missinglinq

 

[awake2424]

If the same name is used twice the later of the two is replaced bu the first.

For example, if Steve Smith appears twice in the table all records contain the first entries info.

Is there away to prevent this?

 

[boblarson]

If the same name is used twice the later of the two is replaced bu the first.

For example, if Steve Smith appears twice in the table all records contain the first entries info.

Is there away to prevent this?

1. You probably should have a table for the people.

2. Then you store the ID of the person in your table instead of storing the name and pulling previous entries to populate the name list.

That is the way I would have set it up.

 

[awake2424]

Ok, so the records are filtering correctly now.

So the table looks like:

1 Steve Smith 212 Acorn Lane
2 Jim Jones 526 Powers Street

The form is a combobox that selects a uniuqe record. 1
there is a text box with the code in it to get the name. Steve Smith
another text box with code gets the address. 212 Acorn Lane

Is there a way to repeat that same process but the resulting data will be different? In this case for Jim Jones and have the data be saved?

 

[boblarson]

Is there a way to repeat that same process but the resulting data will be different? In this case for Jim Jones and have the data be saved?

If it is stored in one location you should not be storing the name in another location. You should store the personID for the person in the second table, not their full information. You then use a query later for getting the data out and pulled together. Note - you can use a query in 99.9% of the places you can use a table.

 

[awake2424]

The information is stored in one table and the number is the primary key. If I use the table name as a control source it replaces all the values to be the same. But if I do not define a control souce the data does not get saved. Thanks.