Combo Box Fun

[Christopher Robin]

This is probably something easy to do, but my brain seems to be sleeping at
the moment. I've added a combo box to a form, and the selection from this
combo box will drive the data in 4 other fields. Because these items are
linked, I created a view on the back end, and then removed the old text box
and replaced it with the new and improved combo box. There is data for this
field, and I would like that data to be populated, if it exists. I've set
the Default Value for the combo box to be the appropriate column from the
db, but it doesn't show any data.

Any suggestions would be greatly appreciated.

TIA,
Chris

 

[Wolfgang Kais]

Christopher Robin.

Christopher Robinwrote:

This is probably something easy to do, but my brain seems to be
sleeping at the moment. I've added a combo box to a form, and the
selection from this combo box will drive the data in 4 other fields.
Because these items are linked, I created a view on the back end,
and then removed the old text box and replaced it with the new and
improved combo box. There is data for this field, and I would like
that data to be populated, if it exists. I've set the Default Value
for the combo box to be the appropriate column from the db, but it
doesn't show any data.

Any suggestions would be greatly appreciated.

Since your ComboBox will populate 4 other fields, the view/query used
as rowsource for the combo should have 5 or 6 columns. There are some
properties of the ComboBox that have to be set:
ColumnCount (the number of columns of the rowsource)
ColumnWidths (separated by semicolons)
BoundColumn (the 1-based number of the column that returns the value
that is stored in the ComboBox or in the underlying table of the
form). If the value of the bound column is to be stores in the
database, you will also have to set the ControlSource property.
The "default value" will not show data from the dataase.

 

[Christopher Robin]

My Default Value is simply a country name, but that's not showing up. I
think I have the rest of it set up. My control source is displayed on my
form, but if there is data in that field (this data was inserted before
adding the combo box), nothing shows up on my form for that field or the
combo box it drives.

Doh.....as I was responding, I remembered that I should probably add my base
table to the query and use the ID from the table instead of from the view I
created for the combo box. Now it works like I wanted.

Thanks for waking my brain up. =D

 

[Christopher Robin]

Apparently, I spoke too soon. I can either have the data in the table for
those 4 fields populated from the DB and a non-functioning combo box, or I
can have a functioning combo box and re-enter the data from the combo box to
feed the 4 fields.

 

[John W. Vinson]

Apparently, I spoke too soon. I can either have the data in the table for
those 4 fields populated from the DB and a non-functioning combo box, or I
can have a functioning combo box and re-enter the data from the combo box to
feed the 4 fields.

Why are you (apparently) trying to copy the data from one table - the combo's
rowsource - and store it redundantly in a different table? Redundancy is
EEEvil and its avoidance is one of the primary reasons to use relational
design!

Please explain the context. Where is the combo getting its data, and where do
you want the data to end up... and why??

 

[Wolfgang Kais]

Hello Christopher Robin.

Apparently, I spoke too soon. I can either have the data in the table
for those 4 fields populated from the DB and a non-functioning combo
box, or I can have a functioning combo box and re-enter the data from
the combo box to feed the 4 fields.

The rowsource of the combo box can be your view, the control source of
the combo box should be an ID column, mostly a foreign key where you
want to store the ID of the row you select in the combo box.
For this to work, the view in the rowsource of the combo box must
contain an appropriate ID column. If this was the first column then the
BoundColumn property of the combo box has to be 1. The text that will
be displayed in the combo box is the first column that is visible.
Be sure that all columns needed for the list are returned from the
query in the rowsource property, including that ID column. Don't try to
hide them by un-checking the display box in query design view. You have
to control the visibility using the columnwidths property of the combo
box. See online help for that property. If that ID column was the first
column of the query/view, you probably want that first column not to
display, so you will probably set the columnwidths property to
something starting with 0".
When you select a row in the combo box, the value of the BoundColumn
(here the first column) of that row (which will not be visible in most
cases) is taken and stored in the column of the table the form is based
on whose name appears in the ControlSource of the combo box.
On the other hand:
When a record becomes the current record of the form, the value of the
combo box changes to the value of the column whose name appears on the
ControlSource of the combo box. Access will the try to find that value
in the "BoundColumn-st" column of the query/view of the rowsource of
the combo box and display the value of the first visible column of that
row according to the ColumnWidths property of the combo box.
I hope this wasn't too much.
Once understanding this and getting it to work, you can think of
accessing the other columns of the selected row and display their
values in different controls on the form.
How are you trying to populate the other controls?

 

[Christopher Robin]

Well, I don't want to store redundant data. Simply put, I wanted to pull
Country and Region information from a view I created, and store this data
(the IDs for the country and region) associated with a new event record. My
main problem stems from the fact that I didn't populate the ID fields, when I
originally created my event table, and I was somewhat confused that I wasn't
getting the expected results. Once I resolved my data issue, and fixed my
combo box, it's working as expected.

 

[Christopher Robin]

Thank you for the detailed explanation. My primary issue was missing data,
which caused a little confusion because I wasn't getting the expected
behavior.

My other controls are being populated by an After Update event:

Private Sub Country_AfterUpdate()

Me.ClassRegionID = Me.Country.Column(1)
Me.Class_Region = Me.Country.Column(2)
Me.Class_Country = Me.Country.Column(0)

End Sub