Dependant combos on continuous form show same value

[Fjordur]

Hi,
I have a form with 2 combos; second combo value list depends on the value of
the 1st one. It works fine in single form view.
But I want to display data in datasheet (or continuous form) view, and I
find that the second combo of all records displays a value that depends on
the value of the 1st combo of the current record, not the value of each
record. Dunno if I make myself understood...
What can I do?

 

[Guest]

Hi

You need to requery the combo when it gets the focus

Put this behind the OnFocus event of the combo - change the ComboName

Me.ComboName.Requery

 

[Tony Vrolyk]

Unbound controls on continuous forms or datasheets all display the same
value at once. They also exibit the same properties like color or enabled.

It sounds like the second combo box is unbound - meaning nothing entered for
it's Control Source. If that is the case whenever it's value changes it will
appear to change of all records displayed.

You could live with it - you could add code to the On Current event so that
as you move through the records Box 2 is updated correctly based on box 1.

Or you can add a field to the underlying query of the form that will be Box
2's Control Source. It will then display correctly.

Hope that helps
Tony

 

[Steve Schapel]

Fjordur,

Do not Requery the second combobox. This will not work on a continuous
form. You will need code like this on the Enter event of the dependent
second combobox...
Me.NameOfCombo.RowSource = "SELECT MyID, MyName FROM MyTable WHERE
MyFK =" & Me.FirstCombo
.... and then on the Exit event...
Me.NameOfCombo.RowSource = "SELECT MyID, MyName FROM MyTable"

Well, I'm only guessing of course with the actual query for the row
source, but you get the idea. Post back with specific details of the
comboboxes if you need more explicit help.

There is another method that is often used to solve this problem, which
involves overlaying a textbox on top of the combobox, so that the
combobox is only visible when it has the focus... but that is more
complicated.

 

[Fjordur]

Unbound controls on continuous forms or datasheets all display the same
value at once. They also exibit the same properties like color or enabled.

It sounds like the second combo box is unbound

No, it IS bound to a field

 

[Fjordur]

Do not Requery the second combobox. This will not work on a continuous
form. You will need code like this on the Enter event of the dependent
second combobox...
Me.NameOfCombo.RowSource = "SELECT MyID, MyName FROM MyTable WHERE
MyFK =" & Me.FirstCombo
... and then on the Exit event...
Me.NameOfCombo.RowSource = "SELECT MyID, MyName FROM MyTable"

Well, I'm only guessing of course with the actual query for the row
source, but you get the idea. Post back with specific details of the
comboboxes if you need more explicit help.

Thanks for the help. I did not specify that both combos are bound to a
table. The combos display now correctly BUT... there are 2 problems:
- say I have 2 possible values A1 and A2 for the 1st combo. When I click in
combo 2 on a record that has 1st combo = A1, the 2nd combo values disappear
(blank) for all records that do not have 1st combo = A1
- sometimes (sorry, I could not determine in which case precisely) when I
click on 2nd combo, the value that is selected in the combo list is not the
value taht was displayed.

Any ideas?

 

[Steve Schapel]

Fjordur,

Do you mean that this is happening after using the method I suggested?
Can you post back with the details of the Row Source of the 2nd
combobox? What is the reason for clicking on the 2nd combobox in a
record that already has a value entered... to edit it to another value?

 

[Ron2006]

I have not had that problem if:

1) both combo boxes are bound to a field.
2) Besides doing the requery of combo 2 in the afterupdate of combo1
I MUST ALSO do the requery of combo 2 in the OnCurrent event for
the form.

Ron

 

[Steve Schapel]

Ron,

Please note that we are talking about a continuous view form here!

 

[Ron2006]

Yes I am taking about a continuous form.

I just ran a test to triple check and it worked fine.

table 1
id
company
catagory
item

tblcatagory
id
catagory

tblItem
id
catagory
item


Form5
subform continuous view of table 1 showing with name [company
subform]
1) company and catagory and item
2) catagory is combo box with a row souce of
SELECT catagories.catagory FROM catagories;
3) item is a combo box with a row source of:
SELECT items.catagory, items.item FROM items WHERE
(((items.catagory)=[Forms]![Form5]![company
subform].[Form]![catagory]));

has two columns bound column is column 2 and field lengths
are 0;2.0

The afterupdate for catagory has
Me.item.Requery

AND
the oncurrent event for this subform [company subform] has
Me.item.Requery

And everything runs as I described. The fields stay with their values
AND the dropdown for the second field always contains the appropriate
subselection of fields.

((My catagories are
ID# catagory
1 fruit
2 vegetables

My items are
ID# catagory item
1 fruit apples
2 fruit oranges
3 vegetables peas
4 vegetables Cabage

my Company table has:
ID# company catagory item
1 CityMarket fruit apples
2 city market vegetables peas
3 city market fruit oranges

Am I wrong in that that is what he was trying to do?

Ron

 

[Steve Schapel]

Ron,

Thanks very much for that. I have made a test database doing exactly as
you described, and it indeed works as you suggest. I must admit I was
surprised to see that it did.

However, in practical terms, this is not really much use. The way you
have designed this database is very unusual. You have [id] fields in
the tblcategory and tblitem talbes, but they are not used, so I am not
sure what their purpose is.

Anyway, I think the problem we have been discussing becomes apparent if
the bound column of the combobox is not the column that is displayed.
In the case of category = fruit or vegetable, this may not necessarily
be the case. But with cascading comboboxes, in my experience it is
almost always the case that the displayed column is not the bound
column, for example the basis of the relationship between the first
combobox and the dependent second combobox might be SupplierID but it is
the SupplierName that is shown.

Using your example, try it like this...

1. In tblitem, change catagory to a Number data type, and edit it so it
contains the id value from the catagory table, e.g. 1 instead of "fruit".

2. In table1, change catagory and items to Number data type, and edit
them so they contain the id values, so in the example you gave, it would
look like this...
1 CityMarket 1 1
2 CityMarket 2 3
3 CityMarket 1 2

3. On the form, change the properties of the catagory combobox to like
this...
Row Source - SELECT id, catagory FROM tblcatagory
Column Count - 2
Bound Column - 1
Column Widths - 0;2

4. On the form, change the properties of the item combobox to like this...
Row Source - SELECT id, item FROM tblItem WHERE
catagory=Forms!Form5![company subform]!catagory
Column Count - 2
Bound Column - 1
Column Widths - 0;2

Then you will see what we are talking about, I think.

 

[Ron2006]

I switched the tables over to what you described, and I see what you
mean.

I guess it comes down now to a decision of whether I want to have the
database fully normalized or not.

If you only do the normalization on the first item and not both (use
the ID on the catagory, but NOT on the item) it still works as I
described.

So if it is fully normalized then the display becomes more dificult. If
the second item is not normalized, then the display is easier, but one
of the normalizing rules is not followed completely.

Decisions.... Decisions....

Ron