Show multiple columns from combo box (97 sr2)

Topic

New Reply

My database contains info on plants. On one of the forms, I have a combo box for the user to pick the name of the plant from a drop down list. The selection (common name) is then stored in the underlying table.

Works great. BUT, the user wants information from another column of the drop down list to be displayed when he selects the item.

For instance, drop down contains PlantCommonName, PlantScientificName.

When he selects the PlantCommonName, he wants BOTH the common name and scientific name to show. But, only the common name to be entered into the field for that record in the underlying table.

He said the field guys need to reference the scientific AND common names when they enter a record, and they don’t know the scientific name, so they want it to also display when they select the common name.

Reply | Quote

Replies

Assuming you have a plant table with the following fields:

PlantID
NameCom
NameSci

In the row source either enter the following SQL statement or create a query based upon the following SQL statement and select the query as the row source.

Select tblPlant.PlantID,tblPlant.NameCom AS [Common Name], tblPlant.NameSci AS [Scientific Name]
From tblPlant
Order By tblPlant.NameCom

Make the bound column column 1 (PlantID) and make the column widths 0″;1″;1″. This will store the PlantID in the table and not the plant name. You can retrieve the plant name any time you need to in a query by linking the two tables on PlantID.

Reply | Quote

I don’t know how you built your dropdown box, but if you used the wizard it gives you the option of selecting both columns. You can choose the bound column ( the one that gets written to the table) and still display others.

You should be able to edit the combobox. In the Form design view, select the combobox. Open the Properties box (right-click and choose properties). On the data tab, locate the Row Source property and click on the Build button on the extreme right of that row. It will open the query window for you. Bring the additional field into the grid.

On the Format tab ( you are still in the Property window of the combobox), you may have to change the column count to 2, adjust the column and list lengths so that both columns can be properly seen.
HTH

Reply | Quote

This is what I have now. What I want is to have the second column display on the form AND the first column – BOTH of them. Paul’s suggestion of running a query to get the info is an option they don’t want to use because it requires extra screens. They want to see it on the same screen they enter data on. Is there some code I can put in the combo box – maybe an on click event – that will display the second column in an unbound text box on the screen?

Reply | Quote

What Paul said was to make the source (Row Source) of the Combo box a select query which would show both fields, or at least that’s what I think he said.

<>

I would use the AfterUpdate event to populate your idea of an unbound text box on the screen. If you don’t know how to do this, here it is:

UnboundTextBox = ComboBox.column(2)

where UnboundTextBox is the name of your unbound text box and the 3rd column (the column(n) where n starts at zero) is your other field required to be displayed.

HTH
Pat

Reply | Quote

make sure that the column count is correct or your column(n) statement will return nothing. I’ve been caught before.

Why not just use a second combo box bound to the same field displaying the additional data? It requires no code. It automatically updates when you change the record. It’s fast.

The combo can be locked if display only is required but has the added advantage that if someone knows the common name they can enter the common and see the scientific. If they know the scientific they can enter the scientific and then see the common name.

Stewart

Reply | Quote

To provide a useful answer would have to know how combo box is populated. If combo is bound to plant common name field in primary table (the table being updated by form), then where does plant scientific name come from? Is it a field in same table or does combo look it up in a related table? If a related table, you can include related table in query that populates form, then add text box on form next to combo, bound to scientific name field from related table. This text box should be locked. It will be updated automatically whenever value of combo box changes (including when user undoes changes).

If you use unbound text box and update based on combo box column(n) property, you have to provide for cases where user enters an Undo command. The combo After Update event will not be triggered by an Undo. And if you’re using A97 there is no form/control On Undo event procedure available to handle this in simple manner.

Reply | Quote

Do you mean that you want both columns to display after selection, not just when the list is dropped down?

Try one of these approaches

Assume you’ve got PlantID, PlantName, PlantSciName

Set the row source to:

SELECT PlantID, PlantName & “(” & PlantSciName & “)” FROM Plants

Set the BoundColumn to 1

Make the combo box wide enough to show both fields and set the column width to “0;” (without the quotes)

Or

Set the row source to

SELECT PlantID, PlantName & ” (” & PlantSciName & “)”, PlantName, PlantSciName FROM Plants

Set the BoundColumn to 1

Set the column width property to something like “0;0.001;” (again, without the quotes).

With the latter approach, when the list drops down the two fields will be displayed nicely in columns, the width of the column containing the concatenated names is so small that it won’t appear at all. After the value has been selected, the combo will show the first field which has a width of >0, i.e. the one containing the two names concatenated.

Simon

Reply | Quote

I know others have answered, but I misunderstood your question and wanted to follow-through.
Place an unbound textbox on the form. Make it’s Control Source:
=[your combobox name].Column(number of the column to display)

This will work if there is data selected in the combobox. You will need to approach it a different way if this form is used for data entry. If so, post back and your idea of using code on the combobox event seems more correct.

Reply | Quote

I tried Simon’s suggestion. His first suggestion works, but I was hoping for a separate column that I could put a label above. Could use this in a pinch.

I can’t get Simon’s second suggestion to show the scientific name in a separate column. I must be doing something wrong.

I am intrigued by your suggestion, Thomas, about creating the unbound text box. That was my first idea. The data would look like it’s in separate boxes then. I do want the user to use the form for data entry, though.

They would be selecting the Common Name from the drop down – never the scientific name – that is only for display purposes. The common name is the only thing I want stored in the main table too. We will never use the scientific name for queries or reports or anything else. Just to display on the data entry form.

Reply | Quote

In that case, create an unbound text box for the ScientificName and try this code on the OnExit event of your combobox:

    If Not IsNull(Me![yourcombobox]) Then
        Me![yourScientifictextbox] = Me![your combobox].Column(1)
    Else
        MsgBox ("Please select a CommonName from the dropdown box")
    End If

Note that I used Column(1); you need to use the Column Number that you want to return.

Reply | Quote

This sort of works. It’s more like what I’m looking for. Except: I am using this is a subform on a main form, and it is viewed as a datasheet so the user can select more than one plant (not a multi-select list box, but create a new record on the subform for each plant).

If there are 4 plants selected, all the scientific names are the same for all 4 plant records, even if all the common names are different.

Reply | Quote

I have something similar in one of my apps and what I did was to set the control source of the textbox to the column of the combobox.

=[cboRCODE].[Column](1)

where [cboRCODE] is the name of the combobox control and [column](1) refers to the second column of the combobox.

Reply | Quote

I had a similar problem with a continuous form and a combobox. Follow the thread attached to Charlotte’s answer … I was able to work it out with the helpful replies I received from people there.

Reply | Quote

to quote from Paul K

Assuming you have a plant table with the following fields:

PlantID
NameCom
NameSci

In the row source either enter the following SQL statement or create a query based upon the following SQL statement and select the query as the row source.

Select tblPlant.PlantID,tblPlant.NameCom AS [Common Name], tblPlant.NameSci AS [Scientific Name]
From tblPlant
Order By tblPlant.NameCom

Make the bound column column 1 (PlantID) and make the column widths 0″;1″;1″. This will store the PlantID in the table and not the plant name.
“end quote”

Now copy the combo box and paste it next to the first.
Make the new combo box column widths “0”;0″;1″.

When ever either of the combo boxes are changed the other will auto update as they are bound to the same field. If the scientific name is only ever for reference set the locked property of the second combo box to true. Don’t forget to set the column count of the combo boxes to three.

Stewart

Reply | Quote

You can concatenate the fields you want to view in the second column and hide the first with a 0 width. I do this a lot. For example: Combo Box is 3 columns: Employee ID but you want to see the Full Name and sort by Last Name Ascending. Columns are EmpID, FullName:[FirstName]+” ” +[LastName], LastName ascending. Column widths are 0″;2″;0″. The bound field is column 1 but the users see the name only.

Reply | Quote