Choosing More than one Bound Column?

[Guest]

Howdy,
I have a list box with three columns. Column 1 is a per hour fee, column 2
is a facility and column 3 is a complex. The bound column is column 1 with
the $ amount. However, on queries or reports, I would like all three columns
to appear. I do use column 1 in a formula as well. I have been searching
through the forms and maybe think this has something to do with a one to many
relationship but not sure. Also, the "Facility Cost" is its one table with
three columns. Each facility/complex has its own fee so I can't do a
many-to-many relationship. Not sure if relationships even have anything to
do with this... Please help. Thanks.

 

[John W. Vinson]

Howdy,
I have a list box with three columns. Column 1 is a per hour fee, column 2
is a facility and column 3 is a complex. The bound column is column 1 with
the $ amount. However, on queries or reports, I would like all three columns
to appear. I do use column 1 in a formula as well. I have been searching
through the forms and maybe think this has something to do with a one to many
relationship but not sure. Also, the "Facility Cost" is its one table with
three columns. Each facility/complex has its own fee so I can't do a
many-to-many relationship. Not sure if relationships even have anything to
do with this... Please help. Thanks.

I take it that "Facility Cost" is the rowsource of this listbox? Of course
each facility has its own cost; I don't understand why this precludes a
relationship! What's the Primary Key of FacilityCost? What kind of data is in
the Facility and Complex fields? Do the two of them together uniquely identify
a facility? or is the Facility field itself unique within the table?

For Queries or Reports, just remember that the data *is in the Table* - it's
not in the listbox. The listbox is just a display and editing tool, not a data
repository. If you store the unique facility ID (perhaps the Facility field,
perhaps a new Autonumber field added to the facility table) in your other
table(s), you can join those tables to the Facility Cost table by that field;
and base reports on that joined Query. The listbox plays no role whatsoever
for the report or the query.

John W. Vinson [MVP]

 

[Guest]

John,

Thanks for the reply. I should explain what I am trying to do better... The
idea is if someone chooses a "complex", then only the "facilities" for that
complex display. Then, only the "fee" for that Faciilty is displayed. I
then want to use that "fee" in a formula. I have it now where there is a
combo box that only "displays" the fee but when they choose the drop-down, it
shows the facility and complex as well. The problem is when I want to do a
report, only the fee displays and not the facility or complex. So, I was
thinkning that I need to separate the table of complex/facility/fee into
three separate tables that are linked.

 

[John W. Vinson]

John,

Thanks for the reply. I should explain what I am trying to do better... The
idea is if someone chooses a "complex", then only the "facilities" for that
complex display. Then, only the "fee" for that Faciilty is displayed. I
then want to use that "fee" in a formula. I have it now where there is a
combo box that only "displays" the fee but when they choose the drop-down, it
shows the facility and complex as well. The problem is when I want to do a
report, only the fee displays and not the facility or complex. So, I was
thinkning that I need to separate the table of complex/facility/fee into
three separate tables that are linked.

Not if all three fields are in the same table, no. You can *DISPLAY* other
fields in a combo's rowsource by setting the control source of a textbox to

=comboboxname.Column(n)

where n is the zero based index of the field. So if the first visible column
in the combo cboFacility is the facility, the combo will show it; to display
the second column have a textbox with a control source

=cboFacility.Column(1)

John W. Vinson [MVP]

 

[Guest]

Thanks you so much... I love this site!

Not if all three fields are in the same table, no. You can *DISPLAY* other
fields in a combo's rowsource by setting the control source of a textbox to

=comboboxname.Column(n)

where n is the zero based index of the field. So if the first visible column
in the combo cboFacility is the facility, the combo will show it; to display
the second column have a textbox with a control source

=cboFacility.Column(1)

John W. Vinson [MVP]