Cascading Combo Boxes on continuous forms in Access 2000 - SOMEONE PLEASE HELP

Hi everyone,

Please let there be someone out there who can help.

I have two BOUND combo boxes on a continuous form, the second being
dependent on the first. I have no problem getting the second combo to
change depending on what values the user selects in the first box, it's
just that every time the user changes the first combobox, the second
combobox FOR EVERY RECORD goes blank.

The second combo does, however save the correct ID to the table, so
this is a bonus.

I have googled around and found that there is only 1 combobox shared
between all the records. Yes, I am not the only person to have asked
this question, BUT there does not seem to be an intelligible solution.

I found this advice on one thread:

"
I make the combo box only large enough where you see the drop down
arrow. About 1/4 inch wide.

Next, I move combo box arrow (on the right side) of the field EmpName.


Now I have a combo box and the field I need next to it (that unless
your in design view
looks just like a combo box) 0 column EmpID 1 column EmpName.

On the afterupdate event of the combo box I'll put something to the
effect
Me![EmpName] = Combo1.Column(2)

Now I have the bound column 1 empid in the table and EmpName showing on
the
screen.

This way when it requeries I get the values I want and the Name
stays on the screen. The only draw back is that when your typing John
Smith
you must type in the full name or pick from the list.

"
tried it with the example below and found that if the textbox is
unbound then the textbox on every record changes. However I do not
want to save the any text descriptions, just the IDs and display the
text for the user.

An extremely small example:

tblPetTypes
PetTypeID (autonumber) PK
PetTypeDesc

tblPetBreeds
PetBreedID (autonumber) PK
PetTypeID FK
PetBreedDesc

tblPetSales
SalesID (autonumber) PK
PetTypeID FK (can I get rid of this?)
PetBreedID FK (ideally from a pets drop down box)
Date
Amount

Following the advice above I:

* created a BOUND continuous form (select * from tblSales)

* put a BOUND combo box (cboPetTypes) listing:

1 cats
2 dogs
3 birds

* put a BOUND combo box putting the breeds according to the category
chosen
(select PetBreedID, PetBreedDesc from tblPetBreeds where PetTypeID =
forms!myForm!cboPetTypes). so user may get:

eg
Persian
Siamese

or
Terrier
Great Dane

or
Parrot
Finch

from tblPetBreeds depending on what they chose in cboPetTypes.

* select a pet type from first combo, second combo for every record
goes blank :(

How do I fix this?

Please provide specific steps to solve this problem including any code
or form or control settings. Otherwise, please advise if cascading
combos cannot be used on continuous forms.

Cheers


Edwinah63

re: Cascading Combo Boxes on continuous forms in Access 2000 - SOMEONE PLEASE HELP

Ediwnah,

I suppose you requery your PetBreed combo box at some point in order to
have the correct entries that belong to the selected PetType? The
problem with continuous forms is that you requery the whole form, not
just the current record.

Google "problems with requery on continuous form" for a workaround
posted by Wayne Gillespie.

Rog

re: Cascading Combo Boxes on continuous forms in Access 2000 - SOMEONE PLEASE HELP

Ediwnah,

I suppose you requery your PetBreed combo box at some point in order to
have the correct entries that belong to the selected PetType? The
problem with continuous forms is that you requery the whole form, not
just the current record.

Google "problems with requery on continuous form" for a workaround
posted by Wayne Gillespie.

Rog

re: Cascading Combo Boxes on continuous forms in Access 2000 - SOMEONE PLEASE HELP

Hey Edwinah,

Try this:

Add a bound text box over the second comb box, and make it the same
size & font setting as the underlying combo box, except make the text
box width the same as the text portion of the combo box. Make the text
box locked and disabled.

That way the stored value for each record will be shown in the text
box, but the user has to use the combo box to change values. The fact
that there are two controls on the form should be not obvious to the
users, and the text box value will cover up the blank combo box values.

Let me know if this works out,

Rusty Morr

[color=blue]
> Hi everyone,
>
> Please let there be someone out there who can help.
>
> I have two BOUND combo boxes on a continuous form, the second being
> dependent on the first. I have no problem getting the second combo to
> change depending on what values the user selects in the first box, it's
> just that every time the user changes the first combobox, the second
> combobox FOR EVERY RECORD goes blank.
>
> The second combo does, however save the correct ID to the table, so
> this is a bonus.
>
> I have googled around and found that there is only 1 combobox shared
> between all the records. Yes, I am not the only person to have asked
> this question, BUT there does not seem to be an intelligible solution.
>
> I found this advice on one thread:
>
> "
> I make the combo box only large enough where you see the drop down
> arrow. About 1/4 inch wide.
>
> Next, I move combo box arrow (on the right side) of the field EmpName.
>
>
> Now I have a combo box and the field I need next to it (that unless
> your in design view
> looks just like a combo box) 0 column EmpID 1 column EmpName.
>
> On the afterupdate event of the combo box I'll put something to the
> effect
> Me![EmpName] = Combo1.Column(2)
>
> Now I have the bound column 1 empid in the table and EmpName showing on
> the
> screen.
>
> This way when it requeries I get the values I want and the Name
> stays on the screen. The only draw back is that when your typing John
> Smith
> you must type in the full name or pick from the list.
>
> "
> tried it with the example below and found that if the textbox is
> unbound then the textbox on every record changes. However I do not
> want to save the any text descriptions, just the IDs and display the
> text for the user.
>
> An extremely small example:
>
> tblPetTypes
> PetTypeID (autonumber) PK
> PetTypeDesc
>
> tblPetBreeds
> PetBreedID (autonumber) PK
> PetTypeID FK
> PetBreedDesc
>
> tblPetSales
> SalesID (autonumber) PK
> PetTypeID FK (can I get rid of this?)
> PetBreedID FK (ideally from a pets drop down box)
> Date
> Amount
>
> Following the advice above I:
>
> * created a BOUND continuous form (select * from tblSales)
>
> * put a BOUND combo box (cboPetTypes) listing:
>
> 1 cats
> 2 dogs
> 3 birds
>
> * put a BOUND combo box putting the breeds according to the category
> chosen
> (select PetBreedID, PetBreedDesc from tblPetBreeds where PetTypeID =
> forms!myForm!cboPetTypes). so user may get:
>
> eg
> Persian
> Siamese
>
> or
> Terrier
> Great Dane
>
> or
> Parrot
> Finch
>
> from tblPetBreeds depending on what they chose in cboPetTypes.
>
> * select a pet type from first combo, second combo for every record
> goes blank :(
>
> How do I fix this?
>
> Please provide specific steps to solve this problem including any code
> or form or control settings. Otherwise, please advise if cascading
> combos cannot be used on continuous forms.
>
> Cheers
>
>
> Edwinah63[/color]

re: Cascading Combo Boxes on continuous forms in Access 2000 - SOMEONE PLEASE HELP

On 8 Mar 2006 05:11:34 -0800, "Rog" <delahayerc@state.gov> wrote:
[color=blue]
>Ediwnah,
>
>I suppose you requery your PetBreed combo box at some point in order to
>have the correct entries that belong to the selected PetType? The
>problem with continuous forms is that you requery the whole form, not
>just the current record.
>
>Google "problems with requery on continuous form" for a workaround
>posted by Wayne Gillespie.
>
>Rog[/color]

A small sample db (A2K) demonstrating the technique can be downloaded from -
http://www.bestfitsoftware.com.au/ut...usComboA2K.zip


Wayne Gillespie
Gosford NSW Australia

re: Cascading Combo Boxes on continuous forms in Access 2000 - SOMEONE PLEASE HELP

Hi Wayne,

Thanks for your reply, I downloaded the zip file as you suggested but
have been unable to open it with either Access 2, 97 or 2000.

Could you advise which version it is? or post the code and instructions
on the thread?

Regards

Edwinah63


Wayne Gillespie wrote:[color=blue]
> On 8 Mar 2006 05:11:34 -0800, "Rog" <delahayerc@state.gov> wrote:[/color]
[color=blue]
>
> A small sample db (A2K) demonstrating the technique can be downloaded from -
> http://www.bestfitsoftware.com.au/ut...usComboA2K.zip[/color]

re: Cascading Combo Boxes on continuous forms in Access 2000 - SOMEONE PLEASE HELP

Hi Rog,

thanks For your reply, I do indeed requery cboPetBreeds in the after
update event of cboPetTypes.

Edwinah63

re: Cascading Combo Boxes on continuous forms in Access 2000 - SOMEONE PLEASE HELP

Hi Wayne,

I take it your sample db is an Access 2000 db, but I have been unable
to open it (message comes back that it an unrecognized file format or
created with a later version.) Could you confirm that the sample was
made with A2k?

I found a reference of one of your previous posts to putting a text box
beside the combo box but it mentioned BINDING the text box to my
underlying table. Wouldn't this mean that in addition to the
petbreedid that I want to save to my Sales table, that I would also
have save the breed description to my Sales table as well?

Edwina

re: Cascading Combo Boxes on continuous forms in Access 2000 - SOMEONE PLEASE HELP

On 8 Mar 2006 18:29:24 -0800, "Edwinah63" <edwinah@customercare.com.au> wrote:
[color=blue]
>Hi Wayne,
>
>Thanks for your reply, I downloaded the zip file as you suggested but
>have been unable to open it with either Access 2, 97 or 2000.
>
>Could you advise which version it is? or post the code and instructions
>on the thread?
>
>Regards
>
>Edwinah63
>
>[/color]

Try this copy (Access 2000)
http://www.bestfitsoftware.com.au/ut...usComboA2K.zip


Wayne Gillespie
Gosford NSW Australia

re: Cascading Combo Boxes on continuous forms in Access 2000 - SOMEONE PLEASE HELP

On 8 Mar 2006 18:42:50 -0800, "Edwinah63" <edwinah@customercare.com.au> wrote:
[color=blue]
>Hi Wayne,
>
>I take it your sample db is an Access 2000 db, but I have been unable
>to open it (message comes back that it an unrecognized file format or
>created with a later version.) Could you confirm that the sample was
>made with A2k?[/color]

There was a problem with the file. I have put a new version up. Yes it is A2k
format.
[color=blue]
>
>I found a reference of one of your previous posts to putting a text box
>beside the combo box but it mentioned BINDING the text box to my
>underlying table. Wouldn't this mean that in addition to the
>petbreedid that I want to save to my Sales table, that I would also
>have save the breed description to my Sales table as well?
>
>Edwina[/color]

Assuming you have a table containing the breed description, add this table to
your form's recordscource with a join between petbreedid in both tables. There
is no need to include the breed description in the sales table.

See tblTestOptions in the recordsource of the sample file.


Wayne Gillespie
Gosford NSW Australia