Limiting Available values in Combo box on form

[Matt]

Is there a way to limit available values in a combo box on a form based on a
previous selection from a combo box on the same form? What I want the form
to do is automatically filter the available selections in a second combo box
from the selected choice in the first. Is this possible?

 

[Maurice]

Hi Matt,

It certainly is. Search for 'cascading comboboxes' that's what you are
after. It has been explained quit a lot in the group (and very well I must
say). So go ahead and use the search option.

hth

 

[Matt]

Thank you for responding. I haven't been through all the discussions, but
using some of the help has it working (sort of). The issue now is that the
form (in datasheet view) changes the previous "cascaded" combo box when the
controlling combo box is selected differently in the next row of the
datasheet on the form. Is there a way to divorce the combo box selections on
seperate rows?

 

[justinw]

enter the following into the rowsource of the target control, or do it in VBA
on the BeforeUpdate event of the control you want to query:

Select Column1, Column2, etc FROM TableOrQuery Where ColumnX =
forms!currentForm!ControlNameThatHasTheData

on the target combo box control, you can also click into the properties
rowsource on the right end of it to open a query window that will allow you
to construct the above string. Key thing is the criteria form call:
Forms!CurrentFormName!ControlName.

 

[Douglas J. Steele]

No. While it may look like separate rows to you, in fact it's the same
control repeated multiple times so what's applied to one instance applies to
all instances.

I talked about a work-around, though, in my February, 2006 "Access Answers"
column in Pinnacle Publication's "Smart Access". You can download the column
(and sample database) for free at
http://www.accessmvp.com/djsteele/SmartAccess.html

 

[Matt]

Following the steps in your column puts the text box on the datasheet form,
but I cannot get the text box column to fall over the combo box, with the
text box instead aligned as a seperate column, and seems to disregard the
code in the Load event of the form. This is the same whether simply aligning
the text box in design view or using the code described. Is there something
I am missing?

 

[Douglas J. Steele]

Are you sure that the code in your Load event is actually firing? Event
Procedures sometimes get disassociated from the event. Make sure that the
Load property of the form is actually set to [Event Procedure], and that
clicking on the ellipsis (...) to the right of the property actually takes
you to the correct code.

Does the sample database work for you?

 

[Matt]

The load event is set to [Event Procedure], and clicking takes me to the
right place. The text box (and corresponding datasheet) are on a subform of
a main form, and the code sizing & placing the text box is in the subform
load event. The sample database in your example appears to work fine.

Are you sure that the code in your Load event is actually firing? Event
Procedures sometimes get disassociated from the event. Make sure that the
Load property of the form is actually set to [Event Procedure], and that
clicking on the ellipsis (...) to the right of the property actually takes
you to the correct code.

Does the sample database work for you?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Following the steps in your column puts the text box on the datasheet
form,
but I cannot get the text box column to fall over the combo box, with the
text box instead aligned as a seperate column, and seems to disregard the
code in the Load event of the form. This is the same whether simply
aligning
the text box in design view or using the code described. Is there
something
I am missing?

 

[Douglas J. Steele]

I would think that the code needs to be in the Load event of the subform,
not the form.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

The load event is set to [Event Procedure], and clicking takes me to the
right place. The text box (and corresponding datasheet) are on a subform
of
a main form, and the code sizing & placing the text box is in the subform
load event. The sample database in your example appears to work fine.

Are you sure that the code in your Load event is actually firing? Event
Procedures sometimes get disassociated from the event. Make sure that the
Load property of the form is actually set to [Event Procedure], and that
clicking on the ellipsis (...) to the right of the property actually
takes
you to the correct code.

Does the sample database work for you?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Following the steps in your column puts the text box on the datasheet
form,
but I cannot get the text box column to fall over the combo box, with
the
text box instead aligned as a seperate column, and seems to disregard
the
code in the Load event of the form. This is the same whether simply
aligning
the text box in design view or using the code described. Is there
something
I am missing?

:

No. While it may look like separate rows to you, in fact it's the same
control repeated multiple times so what's applied to one instance
applies
to
all instances.

I talked about a work-around, though, in my February, 2006 "Access
Answers"
column in Pinnacle Publication's "Smart Access". You can download the
column
(and sample database) for free at
http://www.accessmvp.com/djsteele/SmartAccess.html

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Thank you for responding. I haven't been through all the
discussions,
but
using some of the help has it working (sort of). The issue now is
that
the
form (in datasheet view) changes the previous "cascaded" combo box
when
the
controlling combo box is selected differently in the next row of the
datasheet on the form. Is there a way to divorce the combo box
selections
on
seperate rows?

:

Hi Matt,

It certainly is. Search for 'cascading comboboxes' that's what you
are
after. It has been explained quit a lot in the group (and very well
I
must
say). So go ahead and use the search option.

hth
--
Maurice Ausum


:

Is there a way to limit available values in a combo box on a form
based
on a
previous selection from a combo box on the same form? What I
want
the
form
to do is automatically filter the available selections in a
second
combo box
from the selected choice in the first. Is this possible?

 

[Matt]

Thank you for responding to my questions. I do have the code in the load
event of the subform to size the text box which will cover the combo box, and
it does not work. I went back to your example of this method, which is in a
continuous form format, and changed the view to datasheet, and got the same
issue I am attempting to deal with at this time (all bound control boxes
appearing as columns aligned horizontally across the screen). So, since it
isn't working in the example, and I cannot get it to work, should I give up &
try formatting the form differently? I am not that familiar with continuous
forms, but maybe I should go that route as well.

I would think that the code needs to be in the Load event of the subform,
not the form.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

The load event is set to [Event Procedure], and clicking takes me to the
right place. The text box (and corresponding datasheet) are on a subform
of
a main form, and the code sizing & placing the text box is in the subform
load event. The sample database in your example appears to work fine.

Are you sure that the code in your Load event is actually firing? Event
Procedures sometimes get disassociated from the event. Make sure that the
Load property of the form is actually set to [Event Procedure], and that
clicking on the ellipsis (...) to the right of the property actually
takes
you to the correct code.

Does the sample database work for you?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



Following the steps in your column puts the text box on the datasheet
form,
but I cannot get the text box column to fall over the combo box, with
the
text box instead aligned as a seperate column, and seems to disregard
the
code in the Load event of the form. This is the same whether simply
aligning
the text box in design view or using the code described. Is there
something
I am missing?

:

No. While it may look like separate rows to you, in fact it's the same
control repeated multiple times so what's applied to one instance
applies
to
all instances.

I talked about a work-around, though, in my February, 2006 "Access
Answers"
column in Pinnacle Publication's "Smart Access". You can download the
column
(and sample database) for free at
http://www.accessmvp.com/djsteele/SmartAccess.html

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Thank you for responding. I haven't been through all the
discussions,
but
using some of the help has it working (sort of). The issue now is
that
the
form (in datasheet view) changes the previous "cascaded" combo box
when
the
controlling combo box is selected differently in the next row of the
datasheet on the form. Is there a way to divorce the combo box
selections
on
seperate rows?

:

Hi Matt,

It certainly is. Search for 'cascading comboboxes' that's what you
are
after. It has been explained quit a lot in the group (and very well
I
must
say). So go ahead and use the search option.

hth
--
Maurice Ausum


:

Is there a way to limit available values in a combo box on a form
based
on a
previous selection from a combo box on the same form? What I
want
the
form
to do is automatically filter the available selections in a
second
combo box
from the selected choice in the first. Is this possible?

 

[Douglas J. Steele]

It doesn't really surprise me that it doesn't work with datasheets: there
are differences in how you resize fields in datasheets (and I'm afraid I
don't have time to rework the database to use a datasheet).

If you want to stay with a datasheet, then yes, I think you'll have to give
up and try formatting the form differently.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Thank you for responding to my questions. I do have the code in the load
event of the subform to size the text box which will cover the combo box,
and
it does not work. I went back to your example of this method, which is in
a
continuous form format, and changed the view to datasheet, and got the
same
issue I am attempting to deal with at this time (all bound control boxes
appearing as columns aligned horizontally across the screen). So, since
it
isn't working in the example, and I cannot get it to work, should I give
up &
try formatting the form differently? I am not that familiar with
continuous
forms, but maybe I should go that route as well.

I would think that the code needs to be in the Load event of the subform,
not the form.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

The load event is set to [Event Procedure], and clicking takes me to
the
right place. The text box (and corresponding datasheet) are on a
subform
of
a main form, and the code sizing & placing the text box is in the
subform
load event. The sample database in your example appears to work fine.

:

Are you sure that the code in your Load event is actually firing?
Event
Procedures sometimes get disassociated from the event. Make sure that
the
Load property of the form is actually set to [Event Procedure], and
that
clicking on the ellipsis (...) to the right of the property actually
takes
you to the correct code.

Does the sample database work for you?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



Following the steps in your column puts the text box on the
datasheet
form,
but I cannot get the text box column to fall over the combo box,
with
the
text box instead aligned as a seperate column, and seems to
disregard
the
code in the Load event of the form. This is the same whether simply
aligning
the text box in design view or using the code described. Is there
something
I am missing?

:

No. While it may look like separate rows to you, in fact it's the
same
control repeated multiple times so what's applied to one instance
applies
to
all instances.

I talked about a work-around, though, in my February, 2006 "Access
Answers"
column in Pinnacle Publication's "Smart Access". You can download
the
column
(and sample database) for free at
http://www.accessmvp.com/djsteele/SmartAccess.html

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Thank you for responding. I haven't been through all the
discussions,
but
using some of the help has it working (sort of). The issue now
is
that
the
form (in datasheet view) changes the previous "cascaded" combo
box
when
the
controlling combo box is selected differently in the next row of
the
datasheet on the form. Is there a way to divorce the combo box
selections
on
seperate rows?

:

Hi Matt,

It certainly is. Search for 'cascading comboboxes' that's what
you
are
after. It has been explained quit a lot in the group (and very
well
I
must
say). So go ahead and use the search option.

hth
--
Maurice Ausum


:

Is there a way to limit available values in a combo box on a
form
based
on a
previous selection from a combo box on the same form? What I
want
the
form
to do is automatically filter the available selections in a
second
combo box
from the selected choice in the first. Is this possible?