Modify ComboBox in Continuous Form?

[Allen Browne]

Larry, there is only one actual combo on the form at any time. There is not
really a complete combo with its own RowSource on every row.

If you disable the combo, it is visibly disabled on every row.
If you restrict the RowSource of the combo to only the building in the city
of the current record, the combo will not have the other buildings loaded.
That means that if the combo's bound column is set to zero-width so it shows
another column, the data it needs for the other column will not be
available, so the combo will just look blank.

If you do not set the width of the bound column to zero, the combo will
still display its value on each row, and you may be able to use conditional
formatting to make it look the way you want. You can use the form's Current
event to set the combo's RowSource, and everything will be fine when the
combo is dropped down. Since it's not possible to drop down the combo
without moving to the row, that works without problem.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - [url]http://allenbrowne.com/tips.html[/url]
Reply to the newsgroup. (Email address has spurious "_SpamTrap")

"Larry" <lljo40@netscape.net> wrote in message
news:%23u62ra7SDHA.2256@TK2MSFTNGP11.phx.gbl...

> I need to enable or disable a ComboBox on a continous form, based on the
> value of another control on the same row, as well as make the values in

the

> ComboBox be based on the selection of the other control.
>
> What I have is a ComboBox for City and a second one for Building. For some
> cities, there is no building option, so I want to disable the Building
> ComboBox for those cities. But for other cities, I need the ComboBox
> enabled, and the choice of buildings it shows based on the city chosen in
> the first ComboBox.
>
> This is a piece of cake, if done on a single record form, but I can't

figure

> it out on a continuous form. I was hoping Conditional Formatting would

help,

> but I can't figure out how to reference the other control, to be able to

set

> the Enable value. I also doubt it can do anything for filtering or
> rowsource.

[Larry]

Maybe I should have stated I am using Access XP.

With conditional formatting, you can make a control enabled/disabled, based
on the contents of that control, and it does not effect every control on the
form (like it use to in previous versions).

I was hoping there was a way to make it reference other controls. Seems like
there should be, but I haven't figured it out yet and I'm just using this
for the first time.


"Allen Browne" <abrowne1_SpamTrap@bigpond.net.au> wrote in message
news:OZVvAk7SDHA.1920@TK2MSFTNGP11.phx.gbl...

> Larry, there is only one actual combo on the form at any time. There is

not

> really a complete combo with its own RowSource on every row.
>
> If you disable the combo, it is visibly disabled on every row.
> If you restrict the RowSource of the combo to only the building in the

city

> of the current record, the combo will not have the other buildings loaded.
> That means that if the combo's bound column is set to zero-width so it

shows

> another column, the data it needs for the other column will not be
> available, so the combo will just look blank.
>
> If you do not set the width of the bound column to zero, the combo will
> still display its value on each row, and you may be able to use

conditional

> formatting to make it look the way you want. You can use the form's

Current

> event to set the combo's RowSource, and everything will be fine when the
> combo is dropped down. Since it's not possible to drop down the combo
> without moving to the row, that works without problem.
>
> --
> Allen Browne - Microsoft MVP. Perth, Western Australia.
> Tips for Access users - [url]http://allenbrowne.com/tips.html[/url]
> Reply to the newsgroup. (Email address has spurious "_SpamTrap")
>
> "Larry" <lljo40@netscape.net> wrote in message
> news:%23u62ra7SDHA.2256@TK2MSFTNGP11.phx.gbl...

> > I need to enable or disable a ComboBox on a continous form, based on the
> > value of another control on the same row, as well as make the values in

> the

> > ComboBox be based on the selection of the other control.
> >
> > What I have is a ComboBox for City and a second one for Building. For

some

> > cities, there is no building option, so I want to disable the Building
> > ComboBox for those cities. But for other cities, I need the ComboBox
> > enabled, and the choice of buildings it shows based on the city chosen

in

> > the first ComboBox.
> >
> > This is a piece of cake, if done on a single record form, but I can't

> figure

> > it out on a continuous form. I was hoping Conditional Formatting would

> help,

> > but I can't figure out how to reference the other control, to be able to

> set

> > the Enable value. I also doubt it can do anything for filtering or
> > rowsource.

>
>

[chriske911]

you could instead of disabling the control set a value of n/a (not assigned)
or something
this will allow you to distinct these records as well
when a user sees the value is already filled in they can just skip the input
for that control
educate them to do that and the problem is solved

grtz
"Larry" <lljo40@netscape.net> wrote in message
news:%23kKOot7SDHA.2316@tk2msftngp13.phx.gbl...

> Maybe I should have stated I am using Access XP.
>
> With conditional formatting, you can make a control enabled/disabled,

based

> on the contents of that control, and it does not effect every control on

the

> form (like it use to in previous versions).
>
> I was hoping there was a way to make it reference other controls. Seems

like

> there should be, but I haven't figured it out yet and I'm just using this
> for the first time.
>
>
> "Allen Browne" <abrowne1_SpamTrap@bigpond.net.au> wrote in message
> news:OZVvAk7SDHA.1920@TK2MSFTNGP11.phx.gbl...

> > Larry, there is only one actual combo on the form at any time. There is

> not

> > really a complete combo with its own RowSource on every row.
> >
> > If you disable the combo, it is visibly disabled on every row.
> > If you restrict the RowSource of the combo to only the building in the

> city

> > of the current record, the combo will not have the other buildings

loaded.

> > That means that if the combo's bound column is set to zero-width so it

> shows

> > another column, the data it needs for the other column will not be
> > available, so the combo will just look blank.
> >
> > If you do not set the width of the bound column to zero, the combo will
> > still display its value on each row, and you may be able to use

> conditional

> > formatting to make it look the way you want. You can use the form's

> Current

> > event to set the combo's RowSource, and everything will be fine when the
> > combo is dropped down. Since it's not possible to drop down the combo
> > without moving to the row, that works without problem.
> >
> > --
> > Allen Browne - Microsoft MVP. Perth, Western Australia.
> > Tips for Access users - [url]http://allenbrowne.com/tips.html[/url]
> > Reply to the newsgroup. (Email address has spurious "_SpamTrap")
> >
> > "Larry" <lljo40@netscape.net> wrote in message
> > news:%23u62ra7SDHA.2256@TK2MSFTNGP11.phx.gbl...

> > > I need to enable or disable a ComboBox on a continous form, based on

the

> > > value of another control on the same row, as well as make the values

in

> > the

> > > ComboBox be based on the selection of the other control.
> > >
> > > What I have is a ComboBox for City and a second one for Building. For

> some

> > > cities, there is no building option, so I want to disable the Building
> > > ComboBox for those cities. But for other cities, I need the ComboBox
> > > enabled, and the choice of buildings it shows based on the city chosen

> in

> > > the first ComboBox.
> > >
> > > This is a piece of cake, if done on a single record form, but I can't

> > figure

> > > it out on a continuous form. I was hoping Conditional Formatting would

> > help,

> > > but I can't figure out how to reference the other control, to be able

to

> > set

> > > the Enable value. I also doubt it can do anything for filtering or
> > > rowsource.

> >
> >

>
>

[Ken Snell]

If I'm understanding correctly, you want a combo box to show different items
in its dropdown list (and also to be locked/disabled or not) depending upon
the value of another control in that record.

If this is correct, you can use the form's OnCurrent event to test the value
of the one control and then to make changes to the combo box's properties
for that record. Note that all other records' combo boxes will be changed in
the same way, so if a change for one record potentially sets up an "invalid"
data error for another record, you might have some problems.

Note also that, if you do this, there will be momentary delays as you switch
from one record to another while the code runs, and the user may be
"wondering" why the focus won't go into the combo box on the next record
when he/she tried to click on it. But that may not be an issue after a bit
of training.

--
Ken Snell
<MS ACCESS MVP>

"Larry" <lljo40@netscape.net> wrote in message
news:%23kKOot7SDHA.2316@tk2msftngp13.phx.gbl...

> Maybe I should have stated I am using Access XP.
>
> With conditional formatting, you can make a control enabled/disabled,

based

> on the contents of that control, and it does not effect every control on

the

> form (like it use to in previous versions).
>
> I was hoping there was a way to make it reference other controls. Seems

like

> there should be, but I haven't figured it out yet and I'm just using this
> for the first time.
>
>
> "Allen Browne" <abrowne1_SpamTrap@bigpond.net.au> wrote in message
> news:OZVvAk7SDHA.1920@TK2MSFTNGP11.phx.gbl...

> > Larry, there is only one actual combo on the form at any time. There is

> not

> > really a complete combo with its own RowSource on every row.
> >
> > If you disable the combo, it is visibly disabled on every row.
> > If you restrict the RowSource of the combo to only the building in the

> city

> > of the current record, the combo will not have the other buildings

loaded.

> > That means that if the combo's bound column is set to zero-width so it

> shows

> > another column, the data it needs for the other column will not be
> > available, so the combo will just look blank.
> >
> > If you do not set the width of the bound column to zero, the combo will
> > still display its value on each row, and you may be able to use

> conditional

> > formatting to make it look the way you want. You can use the form's

> Current

> > event to set the combo's RowSource, and everything will be fine when the
> > combo is dropped down. Since it's not possible to drop down the combo
> > without moving to the row, that works without problem.
> >
> > --
> > Allen Browne - Microsoft MVP. Perth, Western Australia.
> > Tips for Access users - [url]http://allenbrowne.com/tips.html[/url]
> > Reply to the newsgroup. (Email address has spurious "_SpamTrap")
> >
> > "Larry" <lljo40@netscape.net> wrote in message
> > news:%23u62ra7SDHA.2256@TK2MSFTNGP11.phx.gbl...

> > > I need to enable or disable a ComboBox on a continous form, based on

the

> > > value of another control on the same row, as well as make the values

in

> > the

> > > ComboBox be based on the selection of the other control.
> > >
> > > What I have is a ComboBox for City and a second one for Building. For

> some

> > > cities, there is no building option, so I want to disable the Building
> > > ComboBox for those cities. But for other cities, I need the ComboBox
> > > enabled, and the choice of buildings it shows based on the city chosen

> in

> > > the first ComboBox.
> > >
> > > This is a piece of cake, if done on a single record form, but I can't

> > figure

> > > it out on a continuous form. I was hoping Conditional Formatting would

> > help,

> > > but I can't figure out how to reference the other control, to be able

to

> > set

> > > the Enable value. I also doubt it can do anything for filtering or
> > > rowsource.

> >
> >

>
>

[Larry]

The problem is that this is a Continuous form, not a Single Record form.
Using the On Current event to set the control enabled/disabled will set that
control for every record on the form.

I was wanting it to effect only the current record not each record being
shown on the continous form. That's why I was hoping Conditional Formatting
would work.


"Ken Snell" <kthissnellis9@notcomcast.realnet> wrote in message
news:eS#1uJ$SDHA.2148@TK2MSFTNGP11.phx.gbl...

> If I'm understanding correctly, you want a combo box to show different

items

> in its dropdown list (and also to be locked/disabled or not) depending

upon

> the value of another control in that record.
>
> If this is correct, you can use the form's OnCurrent event to test the

value

> of the one control and then to make changes to the combo box's properties
> for that record. Note that all other records' combo boxes will be changed

in

> the same way, so if a change for one record potentially sets up an

"invalid"

> data error for another record, you might have some problems.
>
> Note also that, if you do this, there will be momentary delays as you

switch

> from one record to another while the code runs, and the user may be
> "wondering" why the focus won't go into the combo box on the next record
> when he/she tried to click on it. But that may not be an issue after a bit
> of training.
>
> --
> Ken Snell
> <MS ACCESS MVP>
>
> "Larry" <lljo40@netscape.net> wrote in message
> news:%23kKOot7SDHA.2316@tk2msftngp13.phx.gbl...

> > Maybe I should have stated I am using Access XP.
> >
> > With conditional formatting, you can make a control enabled/disabled,

> based

> > on the contents of that control, and it does not effect every control on

> the

> > form (like it use to in previous versions).
> >
> > I was hoping there was a way to make it reference other controls. Seems

> like

> > there should be, but I haven't figured it out yet and I'm just using

this

> > for the first time.
> >
> >
> > "Allen Browne" <abrowne1_SpamTrap@bigpond.net.au> wrote in message
> > news:OZVvAk7SDHA.1920@TK2MSFTNGP11.phx.gbl...

> > > Larry, there is only one actual combo on the form at any time. There

is

> > not

> > > really a complete combo with its own RowSource on every row.
> > >
> > > If you disable the combo, it is visibly disabled on every row.
> > > If you restrict the RowSource of the combo to only the building in the

> > city

> > > of the current record, the combo will not have the other buildings

> loaded.

> > > That means that if the combo's bound column is set to zero-width so it

> > shows

> > > another column, the data it needs for the other column will not be
> > > available, so the combo will just look blank.
> > >
> > > If you do not set the width of the bound column to zero, the combo

will

> > > still display its value on each row, and you may be able to use

> > conditional

> > > formatting to make it look the way you want. You can use the form's

> > Current

> > > event to set the combo's RowSource, and everything will be fine when

the

> > > combo is dropped down. Since it's not possible to drop down the combo
> > > without moving to the row, that works without problem.
> > >
> > > --
> > > Allen Browne - Microsoft MVP. Perth, Western Australia.
> > > Tips for Access users - [url]http://allenbrowne.com/tips.html[/url]
> > > Reply to the newsgroup. (Email address has spurious "_SpamTrap")
> > >
> > > "Larry" <lljo40@netscape.net> wrote in message
> > > news:%23u62ra7SDHA.2256@TK2MSFTNGP11.phx.gbl...
> > > > I need to enable or disable a ComboBox on a continous form, based on

> the

> > > > value of another control on the same row, as well as make the values

> in

> > > the
> > > > ComboBox be based on the selection of the other control.
> > > >
> > > > What I have is a ComboBox for City and a second one for Building.

For

> > some

> > > > cities, there is no building option, so I want to disable the

Building

> > > > ComboBox for those cities. But for other cities, I need the ComboBox
> > > > enabled, and the choice of buildings it shows based on the city

chosen

> > in

> > > > the first ComboBox.
> > > >
> > > > This is a piece of cake, if done on a single record form, but I

can't

> > > figure
> > > > it out on a continuous form. I was hoping Conditional Formatting

would

> > > help,
> > > > but I can't figure out how to reference the other control, to be

able

> to

> > > set
> > > > the Enable value. I also doubt it can do anything for filtering or
> > > > rowsource.
> > >
> > >

> >
> >

>
>

[Larry]

Well, I finally figured out how to do the disable/enable so that each row is
independent "looking" of the others. But I still can't find a good solution
for the values in the dropdown box.

To make the enable/disable work, I had to create a public function that
returned True/False, to test the value of the other City control, returning
True if there was a value in the control. I then used Conditional Formatting
on the Building, using the Expresion Is option, and putting the name of the
new function (in brackets) in the corresponding textbox. This stops the user
from trying to set a Building, before setting the City.

I then tried to set the Row Source of the Building control to reference the
City control, to return only those Buildings in the City that was just set.
That works fine, on the first row. BUT, on the next row, when a City is
selected that is different, the Building disappears from the first row's
control (see Allen's description as to why) and any others where the City is
different. Sure enough, the values in the dropdown are valid for the second
City, but doesn't do me much good if the other rows have blank Buildings!

Does anyone have a solution to this problem? I assume not, but I'll ask
anyway, b/c I know my user is going to want a "magical" solution to this
problem.


"Larry" <lljo40@netscape.net> wrote in message
news:#u62ra7SDHA.2256@TK2MSFTNGP11.phx.gbl...

> I need to enable or disable a ComboBox on a continous form, based on the
> value of another control on the same row, as well as make the values in

the

> ComboBox be based on the selection of the other control.
>
> What I have is a ComboBox for City and a second one for Building. For some
> cities, there is no building option, so I want to disable the Building
> ComboBox for those cities. But for other cities, I need the ComboBox
> enabled, and the choice of buildings it shows based on the city chosen in
> the first ComboBox.
>
> This is a piece of cake, if done on a single record form, but I can't

figure

> it out on a continuous form. I was hoping Conditional Formatting would

help,

> but I can't figure out how to reference the other control, to be able to

set

> the Enable value. I also doubt it can do anything for filtering or
> rowsource.
>
> So I would appreciate any suggestions.
>
> Thanks,
> Larry
>
>

[John Spencer (MVP)]

It can be done.

What you have to do is add a textbox control to display the Building - this
means that you have to have the Building name in your query for the form. You
put the new textbox control on top of the combobox and then add code to the new
textbox to set the focus to the combobox whenever the textbox gets the focus.

That should be enough to get you started.

Larry wrote:

>
> Well, I finally figured out how to do the disable/enable so that each row is
> independent "looking" of the others. But I still can't find a good solution
> for the values in the dropdown box.
>
> To make the enable/disable work, I had to create a public function that
> returned True/False, to test the value of the other City control, returning
> True if there was a value in the control. I then used Conditional Formatting
> on the Building, using the Expresion Is option, and putting the name of the
> new function (in brackets) in the corresponding textbox. This stops the user
> from trying to set a Building, before setting the City.
>
> I then tried to set the Row Source of the Building control to reference the
> City control, to return only those Buildings in the City that was just set.
> That works fine, on the first row. BUT, on the next row, when a City is
> selected that is different, the Building disappears from the first row's
> control (see Allen's description as to why) and any others where the City is
> different. Sure enough, the values in the dropdown are valid for the second
> City, but doesn't do me much good if the other rows have blank Buildings!
>
> Does anyone have a solution to this problem? I assume not, but I'll ask
> anyway, b/c I know my user is going to want a "magical" solution to this
> problem.
>
> "Larry" <lljo40@netscape.net> wrote in message
> news:#u62ra7SDHA.2256@TK2MSFTNGP11.phx.gbl...

> > I need to enable or disable a ComboBox on a continous form, based on the
> > value of another control on the same row, as well as make the values in

> the

> > ComboBox be based on the selection of the other control.
> >
> > What I have is a ComboBox for City and a second one for Building. For some
> > cities, there is no building option, so I want to disable the Building
> > ComboBox for those cities. But for other cities, I need the ComboBox
> > enabled, and the choice of buildings it shows based on the city chosen in
> > the first ComboBox.
> >
> > This is a piece of cake, if done on a single record form, but I can't

> figure

> > it out on a continuous form. I was hoping Conditional Formatting would

> help,

> > but I can't figure out how to reference the other control, to be able to

> set

> > the Enable value. I also doubt it can do anything for filtering or
> > rowsource.
> >
> > So I would appreciate any suggestions.
> >
> > Thanks,
> > Larry
> >
> >