closed comboboxes only showing the first of two fields being pulled to identify an FK

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

dan.neely@gmail.com - 11 Jun 2007 15:06 GMT

I have some FKs configured to pull two fields of a table (item name,
and item version). Both the name and version fields display in the
dropdown but only the first is shown for the selection when the
combobox is closed.

Reply to this Message

Douglas J. Steele - 11 Jun 2007 15:48 GMT

That's correct: combo boxes only show a single column when closed.

To refer to any specific column of the selected row of a combo box, use the
Column collection. For example, to refer to the value in the second column
of the currently selected row, use Forms!NameOfForm!NameOfComboBox.Column(1)

(The Column collection starts numbering at 0)

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

>I have some FKs configured to pull two fields of a table (item name,
> and item version). Both the name and version fields display in the
> dropdown but only the first is shown for the selection when the
> combobox is closed.

Reply to this Message

dan.neely@gmail.com - 11 Jun 2007 16:04 GMT

On Jun 11, 10:48 am, "Douglas J. Steele"
<NOSPAM_djsteele@NOSPAM_canada.com> wrote:

> That's correct: combo boxes only show a single column when closed.

Is it possible to override the default behavior or to have the form
load custom text in place of the column value that would otherwise be
displayed? I really need to be able to show the data in both and
would prefer not to add a DisplayData column to the table to do so.

For an example of the sort of situation I'm dealing with Word03 and
Word07 both save "Word Documents", but the version (2003 vs 2007) is
very relevant to what the file is. (The fact that 07 saves using
the .docx extension is a limitation of the example I choose that
doesn't apply to my actual data/)

Reply to this Message

Douglas J. Steele - 11 Jun 2007 16:22 GMT

You can put logic in the control's AfterUpdate event to populate text boxes
on your form. You can't, however, change what's displayed in the combo box.

Another option, of course, is to added a computed field to the query that
makes up the RowSource for the combo box. Concatenate the two fields of
interest, and make that computed field the visible field in your combo box.

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

> On Jun 11, 10:48 am, "Douglas J. Steele"
> <NOSPAM_djsteele@NOSPAM_canada.com> wrote:
[quoted text clipped - 10 lines]
> the .docx extension is a limitation of the example I choose that
> doesn't apply to my actual data/)

Reply to this Message

Dan Neely - 11 Jun 2007 16:44 GMT

On Jun 11, 11:22 am, "Douglas J. Steele"
<NOSPAM_djsteele@NOSPAM_canada.com> wrote:

> Another option, of course, is to added a computed field to the query that
> makes up the RowSource for the combo box. Concatenate the two fields of
> interest, and make that computed field the visible field in your combo box.

Ok, this is better than what I had before, but is there a way to do
this and keep the text of what was the second column for each row
left justified when the length of the string from the first column
varies in length?

Reply to this Message

Douglas J. Steele - 11 Jun 2007 17:09 GMT

> On Jun 11, 11:22 am, "Douglas J. Steele"
> <NOSPAM_djsteele@NOSPAM_canada.com> wrote:
[quoted text clipped - 7 lines]
> left justified when the length of the string from the first column
> varies in length?

I don't believe so.

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

Reply to this Message

George Nicholson - 11 Jun 2007 16:29 GMT

-Create & save a query that includes your 2 fields.
-Add a 3rd field that is a concatenation of field 1 & field 2. (e.g.,
[ProdID] & " - " & [ProdName])
-Base your combo box on this query.
-Display only the new "combined" field in your combo.

(in other words, don't add a DisplayData field to your table <shiver>, just
toss it into a query)

HTH

Reply to this Message

Marshall Barton - 11 Jun 2007 16:10 GMT

>I have some FKs configured to pull two fields of a table (item name,
>and item version). Both the name and version fields display in the
>dropdown but only the first is shown for the selection when the
>combobox is closed.

Combo boxes only display the first "visible" column. It's
also the column that's used with the AutoExpand feature so
displaying multiple values would be ambiguous.

Normally, if you want to display another field, you can add
a text box near the combo box and set its ControlSource to
this kind of expression =combo1.Column(N)
where N is the 0 based column number.

Signature

Marsh
MVP [MS Access]

Reply to this Message