referencing columns from list box?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Access03/WinXP

I have a form with a combo box (cboMemberYear) with a data source of SELECT
MYear, Type, StartDate, EndDate FROM tblMembershipYears. "Type" is a field
from the table and is a list box with values
"C";"Corporate";"I";"Individual";"J";"Joint";"F";"Family".

On my form, I have text boxes to serve only as reference information for the
user (they are locked).

First question is how do I set a text box to reference a column/value of the
combo box? In the control source, Me.cboMemberYear.[column(1)] and
Forms!frmMemberYearInfo.cboMemberYear.[column(1)] both yield a #Name? error.

The second question is that I want one of those text boxes to display the
Description of the list box, not the single character abbreviation. How do
you get to the "sublevel", if you will, to display the correct data?

Thanks as always.
 
Pendragon said:
Access03/WinXP

I have a form with a combo box (cboMemberYear) with a data source of
SELECT
MYear, Type, StartDate, EndDate FROM tblMembershipYears. "Type" is a
field
from the table and is a list box with values
"C";"Corporate";"I";"Individual";"J";"Joint";"F";"Family".

On my form, I have text boxes to serve only as reference information for
the
user (they are locked).

First question is how do I set a text box to reference a column/value of
the
combo box? In the control source, Me.cboMemberYear.[column(1)] and
Forms!frmMemberYearInfo.cboMemberYear.[column(1)] both yield a #Name?
error.
Click to expand...

The control source would need to have an equal sign in front of it, but I'm
not actually sure it'll work even with the equal sign there.

The normal approach is to put code in the combo box's AfterUpdate event:

Private Sub cboMemberYear_AfterUpdate()

Me.MyTextbox = Me.cboMemberYear.Column(1)

End Sub
The second question is that I want one of those text boxes to display the
Description of the list box, not the single character abbreviation. How
do
you get to the "sublevel", if you will, to display the correct data?
Click to expand...

Not sure I understand this question. By refering to Column(1) above, you'll
get the full description. The Column collection starts counting at 0, so 1
is the 2nd column.
 
After I posted, I thought about the AfterUpdate - it's what I've done
elsewhere. Not sure why that didn't come to mind.

Regarding the second part: If I use Me.MyTextbox =
Me.cboMemberYear.Column(1), that will give me C, I, J or F depending upon
other selections. Is there a way to get Me.MyTextbox to display Corporate,
Individual, Joint or Family instead of the letters? Column(1) is a field
that is a list box in the table. I could always do an IIF statement to
display the correct text, but I was curious if there was a way to reference
that info without doing an IIF statement.

The simple version:

MyTable has a field called Forest. It's a list box with the following values:

1;"Tree1";2;"Tree2";3;"Tree3"

Now I have a form with a combo box. The control source is Forest. The
values displayed are 1, 2 or 3. Is there a way to get the combo box to
display "Tree1", "Tree2","Tree3" without having to do IIF([Forest] = 1,
"Tree1", IIF([Forest] = 2, etc etc etc)?


Douglas J. Steele said:
Pendragon said:
Access03/WinXP

I have a form with a combo box (cboMemberYear) with a data source of
SELECT
MYear, Type, StartDate, EndDate FROM tblMembershipYears. "Type" is a
field
from the table and is a list box with values
"C";"Corporate";"I";"Individual";"J";"Joint";"F";"Family".

On my form, I have text boxes to serve only as reference information for
the
user (they are locked).

First question is how do I set a text box to reference a column/value of
the
combo box? In the control source, Me.cboMemberYear.[column(1)] and
Forms!frmMemberYearInfo.cboMemberYear.[column(1)] both yield a #Name?
error.
Click to expand...

The control source would need to have an equal sign in front of it, but I'm
not actually sure it'll work even with the equal sign there.

The normal approach is to put code in the combo box's AfterUpdate event:

Private Sub cboMemberYear_AfterUpdate()

Me.MyTextbox = Me.cboMemberYear.Column(1)

End Sub
The second question is that I want one of those text boxes to display the
Description of the list box, not the single character abbreviation. How
do
you get to the "sublevel", if you will, to display the correct data?
Click to expand...

Not sure I understand this question. By refering to Column(1) above, you'll
get the full description. The Column collection starts counting at 0, so 1
is the 2nd column.
Click to expand...
 
Something is wrong with your application if Me.cboMemberYear.Column(1) is
returning C, I, J or F. You should only get those values if you're using
Me.cboMemberYear.Column(0) (or strictly referring to Me.cboMemberYear,
without the reference to Column)

Make sure your combo box has the correct number of columns defined.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Pendragon said:
After I posted, I thought about the AfterUpdate - it's what I've done
elsewhere. Not sure why that didn't come to mind.

Regarding the second part: If I use Me.MyTextbox =
Me.cboMemberYear.Column(1), that will give me C, I, J or F depending upon
other selections. Is there a way to get Me.MyTextbox to display
Corporate,
Individual, Joint or Family instead of the letters? Column(1) is a field
that is a list box in the table. I could always do an IIF statement to
display the correct text, but I was curious if there was a way to
reference
that info without doing an IIF statement.

The simple version:

MyTable has a field called Forest. It's a list box with the following
values:

1;"Tree1";2;"Tree2";3;"Tree3"

Now I have a form with a combo box. The control source is Forest. The
values displayed are 1, 2 or 3. Is there a way to get the combo box to
display "Tree1", "Tree2","Tree3" without having to do IIF([Forest] = 1,
"Tree1", IIF([Forest] = 2, etc etc etc)?


Douglas J. Steele said:
Pendragon said:
Access03/WinXP

I have a form with a combo box (cboMemberYear) with a data source of
SELECT
MYear, Type, StartDate, EndDate FROM tblMembershipYears. "Type" is a
field
from the table and is a list box with values
"C";"Corporate";"I";"Individual";"J";"Joint";"F";"Family".

On my form, I have text boxes to serve only as reference information
for
the
user (they are locked).

First question is how do I set a text box to reference a column/value
of
the
combo box? In the control source, Me.cboMemberYear.[column(1)] and
Forms!frmMemberYearInfo.cboMemberYear.[column(1)] both yield a #Name?
error.
Click to expand...

The control source would need to have an equal sign in front of it, but
I'm
not actually sure it'll work even with the equal sign there.

The normal approach is to put code in the combo box's AfterUpdate event:

Private Sub cboMemberYear_AfterUpdate()

Me.MyTextbox = Me.cboMemberYear.Column(1)

End Sub
The second question is that I want one of those text boxes to display
the
Description of the list box, not the single character abbreviation.
How
do
you get to the "sublevel", if you will, to display the correct data?
Click to expand...

Not sure I understand this question. By refering to Column(1) above,
you'll
get the full description. The Column collection starts counting at 0, so
1
is the 2nd column.
Click to expand...
Click to expand...
 
If you change the query that is the source for your combo and
have a table that says F , Family etc.
Have the query link the major source with this other table linked by
the J,F etc column but select the Family, etc for the query.Then the
rest of your code should work just fine.

Ron
 
Back
Top