Setting a ListBox value Programmatically?

[Gary Schuldt]

I have a lstboxControl based on the first 3 columns of a table tForm. The
listbox displays only the 2nd column (the name; the ID is the first column).

Normally the user clicks on the desired value. However, there's a situation
in which I can logically determine the proper value from some other coding
in the form (CBF).

Is it possible to set the value for the lstboxControl in that way? Which
Column value do I set . . . the ID, or the one that's displayed? I don't
have a good sense for what to do here.

Thanks for the help, as always.

Gary

 

[Kelvin]

Combo boxes and list boxes are all controlled by the bound column regardless
of what data is shown. So if you want to set the selected item in the list,
set the value of the list to the value of the bound column. In your case it
would be the ID.

Kelvin

 

[Gary Schuldt]

Thanks, Kelvin. I now am clear that it's the bound column that controls the
display.

The listboxControl's row source is a query:

SELECT tForm.FormID, tForm.FormName, tForm.DisplayFormName FROM tForm

The bound column is 1, displayed is 2

So if I execute the statement

Me.lstboxControl.Column(0) = 7

from somehwhere else in the main form code, what actually is happening
behind the scenes of the form? Does the current record in tForm become the
one whose ID = 7 and then lstboxControl is populated with that row, so that
Column(1) contains tForm.FormName and that's what is displayed and
highlighted?

That's definitely what I want the result to be!

Gary

 

[Kelvin]

See below.

Thanks, Kelvin. I now am clear that it's the bound column that controls the
display.

Not quite. The bound column controls what is stored in the table. The rest
is looked up based on what is stored. For example, if Bob Smith's ID is 515
and the bound column is his ID. The table will actually store 515, but
based on how you define the rest of the select statement it may show 515,
Bob, or Mr. Smith. The important thing is 515.

The listboxControl's row source is a query:

SELECT tForm.FormID, tForm.FormName, tForm.DisplayFormName FROM tForm

The bound column is 1, displayed is 2

So if I execute the statement

Me.lstboxControl.Column(0) = 7

No. The .Column(#) is a read only used to find the rest of the data that is
not shown for the chosen item in the list. In your example you have 3
fields: FormID, FormName, and DisplayFormName. .Column(0) refers to FormID,
..Column(1) refers to FormName and .Column(2) refers to DisplayFormName. You
can set other things to equal this value but not the other way around. So
if you had an unbound text box on your form called "Name Of Form" you can
set the contents of this text box to equal any of the items from the combo
box.

[Name Of Form] = listboxControl.Column(0) will set the content of Name Of
Form to equal FormID of the selected item.
[Name Of Form] = listboxContro.Column(1) will set to FormName
[Name Of Form] = listboxControl.Column(2) will set to DisplayFormName

from somehwhere else in the main form code, what actually is happening
behind the scenes of the form? Does the current record in tForm become the
one whose ID = 7 and then lstboxControl is populated with that row, so that
Column(1) contains tForm.FormName and that's what is displayed and
highlighted?

That's definitely what I want the result to be!

Gary

If you want to set the chosen selection to something you need to change the
value of the combo box, not one of the fields from the current selection.

Me.listboxControl = 7

will set listboxControl to 7. If there is a FormID 7 then this will be the
current selection for the combo box and FormName will be shown.

Kelvin

 

[Gary Schuldt]

Kelvin,

sometimes, after reading a response, I think it's just a wonder that ANY of
my Access code works!

Not quite. The bound column controls what is stored in the table.

Would it be accurate to say that the bound column controls what is stored in
the ControlSource (which could be a table field)?

No. The .Column(#) is a read only used to find the rest of the data

Thanks. I have used the .Column() notation to retrieve values; I wasn't sure
whether it could be used to store values.

If you want to set the chosen selection to something you need to change the
value of the combo box, . . .
Me.listboxControl = 7

So listboxControl itself is a single-valued control, which is NOT the same
as the ControlSource nor the RowSource bound column, right? But they are
all interconnected by various events that happen to the Listbox. Is that
fair to say?

Me.listboxControl = 7
will set listboxControl to 7. If there is a FormID 7 then this will be the
current selection for the combo box and FormName will be shown.

Does doing this also update the ControlSource? What happens if there is no
FormID = 7?

I'm feeling dense on this topic . . .

Gary

See below.

Thanks, Kelvin. I now am clear that it's the bound column that controls the
display.

Not quite. The bound column controls what is stored in the table. The rest
is looked up based on what is stored. For example, if Bob Smith's ID is 515
and the bound column is his ID. The table will actually store 515, but
based on how you define the rest of the select statement it may show 515,
Bob, or Mr. Smith. The important thing is 515.

The listboxControl's row source is a query:

SELECT tForm.FormID, tForm.FormName, tForm.DisplayFormName FROM tForm

The bound column is 1, displayed is 2

So if I execute the statement

Me.lstboxControl.Column(0) = 7

No. The .Column(#) is a read only used to find the rest of the data that is
not shown for the chosen item in the list. In your example you have 3
fields: FormID, FormName, and DisplayFormName. .Column(0) refers to FormID,
.Column(1) refers to FormName and .Column(2) refers to DisplayFormName. You
can set other things to equal this value but not the other way around. So
if you had an unbound text box on your form called "Name Of Form" you can
set the contents of this text box to equal any of the items from the combo
box.

[Name Of Form] = listboxControl.Column(0) will set the content of Name Of
Form to equal FormID of the selected item.
[Name Of Form] = listboxContro.Column(1) will set to FormName
[Name Of Form] = listboxControl.Column(2) will set to DisplayFormName

from somehwhere else in the main form code, what actually is happening
behind the scenes of the form? Does the current record in tForm become the
one whose ID = 7 and then lstboxControl is populated with that row, so that
Column(1) contains tForm.FormName and that's what is displayed and
highlighted?

That's definitely what I want the result to be!

Gary

If you want to set the chosen selection to something you need to change the
value of the combo box, not one of the fields from the current selection.

Me.listboxControl = 7

will set listboxControl to 7. If there is a FormID 7 then this will be the
current selection for the combo box and FormName will be shown.

Kelvin

 

[Kelvin]

Gary,

The rowsource property for a combo box or list box tells access where to get
the data to fill the list. If the rowsource is a table/query it will
display what is in that table or query. The query can be either a saved
query or an SQL statement that you create just for that combo box or list
box. All the fields that are shown in the table/query will be available for
use by the combo box. The width property tells access how to show each
field. If you set the field to 0, then it won't show, but the data is still
there, like a hiden column in excel.

The word bound means whether or not an object is linked to data, normally to
a table or query. This is the control source property. If an object has a
control source, then that object is bound to that source. The control
source for a form or a report would be either a table, a query, or an SQL
statement. The control source for an object like a text box or combo box in
the form or report would be a field from the table/query/SQL. For example,
you have a combo box named cmbName that has a control source of txtName from
a table. Whatever value is chosen for the combo box, that value will get
stored into the table. Objects do not have to be bound. If you leave the
control source blank, then it is not linked to anything. When you pick an
item in the combo box, nothing gets stored in the table. These objetcs are
called unbound objects.

The bound column of a combo box determines what value is actually stored by
the combo box. Regardless of what is being shown. You can have the combo
box show 5 different fields, but it can only store one value. Lets say your
combo box has 3 fields; ID, NameFirst, and NameLast. If you set the width
property to 0,0,1 then only the last name will be shown in the list. If you
have 0,1,1 it will show the first name then the last name. Setting the
bound column to 1 will set the value of the combo box to ID, a 2 will store
NameFirst, and 3 will store NameLast. If the control source is set to a
field from a table, then either ID, NameFirst, or NameLast will be stored in
the table. If the control source is blank, nothing gets stored in the
table, but the combo box still has a value of the bound column. Lets say
the bound column is 1, the ID and the width is 0,1,1 so it will show first
name and last name. If you wanted to change the value of the combo box you
need to change the bound data. You will need to set the combo box to an ID.
The combo box has another property "limit to list" which limits if the combo
bo can be set to a value not in its row source. If you only have employees
with ID's from 100 to 199, then setting the combo box to 200 will generate
an error message. If this property is not on then setting the combo box to
200 will result in a blank being shown in the combo box since there are no
names associated with ID 200. If you mistakenly try to set the combo box to
Bob, the name not the ID, then the combo box will contain the value Bob.
Since there is no ID called Bob, nothing will be shown, but the combo box
will contain the value Bob.

Hope this helps clear things up a bit more.

Kelvin

 

[Gary Schuldt]

Hi, Kelvin,

thanks for the detailed reply; maybe someday I can pass it on to another
"newbie"!

I have three follow-on questions:

1. You dealt with the "flow of data" from the list box into the control
source, the field in the query or table. But it appears to me as though the
flow can be the opposite direction as well. For example, when the form is
opened, if the control source has a value, that value will result in an item
in the list box list being highlighted (if the value matches the value in
the bound column).

And, it appears to me, at that point (form completely opened but no other
event activity), the control source field, the list box, and the bound
column from the highlighted item in the list box list all three have the
same value, right?

2. Now, if from some other code I set the list box to a value (e.g.,
listbox = 7), as you suggest, that change in value results in two other
changes--i.e., the item in the list having its bound column value = 7 is
highlighted, and the control source field value is updated to 7, correct?

3. If, while the form is open, the control source field value in the table
is somehow changed, say to 4, will the listbox value automatically change to
4, and therefore the highlighted list item change, also automatically?

Gary

 

[Kelvin]

Gary,

Hi, Kelvin,

thanks for the detailed reply; maybe someday I can pass it on to another
"newbie"!

I have three follow-on questions:

1. You dealt with the "flow of data" from the list box into the control
source, the field in the query or table. But it appears to me as though the
flow can be the opposite direction as well. For example, when the form is
opened, if the control source has a value, that value will result in an item
in the list box list being highlighted (if the value matches the value in
the bound column).

And, it appears to me, at that point (form completely opened but no other
event activity), the control source field, the list box, and the bound
column from the highlighted item in the list box list all three have the
same value, right?

Yes. What is in the table/query will match the bound column of the list box.
The list will then show the coorsponding data. If you change the content of
the list box, the bound column gets changed and the field in the table/query
will also get changed. This is only true if the list box has a control
source. If the list box is unbound (no control source), then there is no
link between the table/query to the list box so nothing. Picking from the
list will not result in a change in the table since there is no link.

2. Now, if from some other code I set the list box to a value (e.g.,
listbox = 7), as you suggest, that change in value results in two other
changes--i.e., the item in the list having its bound column value = 7 is
highlighted, and the control source field value is updated to 7, correct?
Yes

3. If, while the form is open, the control source field value in the table
is somehow changed, say to 4, will the listbox value automatically change to
4, and therefore the highlighted list item change, also automatically?

Gary

Yes. You can either change the data in the original table/query or the list
box. If you change the data in the table/query, you will need to refresh
the form before it will show the new data.

Kelvin

 

[Kelvin]

I know I responded to this before but it doesn't show in my reader so I'm
reposting it.

See below.

Hi, Kelvin,

thanks for the detailed reply; maybe someday I can pass it on to another
"newbie"!

I have three follow-on questions:

1. You dealt with the "flow of data" from the list box into the control
source, the field in the query or table. But it appears to me as though the
flow can be the opposite direction as well. For example, when the form is
opened, if the control source has a value, that value will result in an item
in the list box list being highlighted (if the value matches the value in
the bound column).

Yes. When the text box has a control source, the same informaiton is kept
in both places. Changing either will change the other as well.

And, it appears to me, at that point (form completely opened but no other
event activity), the control source field, the list box, and the bound
column from the highlighted item in the list box list all three have the
same value, right?

The list box can show any information. It is the bound column that is
linked to the table/query.

2. Now, if from some other code I set the list box to a value (e.g.,
listbox = 7), as you suggest, that change in value results in two other
changes--i.e., the item in the list having its bound column value = 7 is
highlighted, and the control source field value is updated to 7, correct?
Yup.

3. If, while the form is open, the control source field value in the table
is somehow changed, say to 4, will the listbox value automatically change to
4, and therefore the highlighted list item change, also automatically?

Gary

If the data in table is changed while the form is showing the same
information, you need to refresh the form before the new data will be shown
on the screen. Its the same if you have a table open while you change the
data through a query or some other action. The current record will either
show the old information or blank until you move away from that record and
back to it. In a form you can accomplish the same by using DoCmd.Refresh.

Kelvin