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

[Edwinah63]

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

[Rog]

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

[Rog]

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

[Rusty Morr]

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

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

[Wayne Gillespie]

On 8 Mar 2006 05:11:34 -0800, "Rog" <de********@state.gov> wrote:

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

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

[Edwinah63]

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:

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

[Edwinah63]

Hi Rog,

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

Edwinah63

[Edwinah63]

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

[Wayne Gillespie]

On 8 Mar 2006 18:29:24 -0800, "Edwinah63" <ed*****@customercare.com.au> wrote:

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

Try this copy (Access 2000)
http://www.bestfitsoftware.com.au/ut...usComboA2K.zip
Wayne Gillespie
Gosford NSW Australia

[Wayne Gillespie]

On 8 Mar 2006 18:42:50 -0800, "Edwinah63" <ed*****@customercare.com.au> wrote:

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?
There was a problem with the file. I have put a new version up. Yes it is A2k
format.

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

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