Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Ephrata, Washington, USA
    Posts
    683
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Show multiple columns from combo box (97 sr2)

    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.

  2. #2
    3 Star Lounger
    Join Date
    Feb 2001
    Location
    NYC,USA,Earth
    Posts
    273
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Show multiple columns from combo box (97 sr2)

    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.

  3. #3
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    USA
    Posts
    379
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Show multiple columns from combo box (97 sr2)

    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

  4. #4
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Ephrata, Washington, USA
    Posts
    683
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Show multiple columns from combo box (97 sr2)

    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?

  5. #5
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Show multiple columns from combo box (97 sr2)

    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.

    <<
    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? >>

    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 <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  6. #6
    2 Star Lounger
    Join Date
    Mar 2001
    Location
    Emerald Beach, New South Wales, Australia
    Posts
    126
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Show multiple columns from combo box (97 sr2)

    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

  7. #7
    2 Star Lounger
    Join Date
    Mar 2001
    Location
    Emerald Beach, New South Wales, Australia
    Posts
    126
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Show multiple columns from combo box (97 sr2)

    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

  8. #8
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Show multiple columns from combo box (97 sr2)

    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.

  9. #9
    Star Lounger
    Join Date
    Mar 2001
    Location
    Dudley, Midlands, England
    Posts
    55
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Show multiple columns from combo box (97 sr2)

    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

  10. #10
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    USA
    Posts
    379
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Show multiple columns from combo box (97 sr2)

    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.

  11. #11
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Ephrata, Washington, USA
    Posts
    683
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Show multiple columns from combo box (97 sr2)

    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.

  12. #12
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    USA
    Posts
    379
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Show multiple columns from combo box (97 sr2)

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

    <pre> 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
    </pre>


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

  13. #13
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Ephrata, Washington, USA
    Posts
    683
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Show multiple columns from combo box (97 sr2)

    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.

  14. #14
    3 Star Lounger
    Join Date
    Feb 2001
    Location
    NYC,USA,Earth
    Posts
    273
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Show multiple columns from combo box (97 sr2)

    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.

  15. #15
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    USA
    Posts
    379
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Show multiple columns from combo box (97 sr2)

    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.

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •