Cascading combo box is not working

[jodilees]

Hi All

I am tearing my hear out with this. I know it is probably a simple setting somewhere.

I am going to upload a copy of the database. On my OrdersF I want the following to occur
Customer.zip
Field
Pivot - generated from a list of pivots shown on OrdersT
Variety - only shows the variety grown on that pivot


Generation - only shows the generation linked to that variety on that pivot (this sometimes might be multiple values.

I have managed to get pivot and variety fields working fine, but I can not get the Generation to work. I am sure I have probably put a setting wrong somewhere in my playing but I can't work it out.

Please please can someone help me.

Access novice here!

[June7]

Shouldn't the Generation filter criteria reference Variety?

[jodilees]

Are you talking in the form query part?
I have added and taken bits out some many times trying to get it to work.

[June7]

Talking about the Generation combobox RowSource SQL.

[jodilees]

Yes I deleted it back out before i zipped the file.
I have been getting all sorts of results in that field except the one I want.
I have played with the settings on the table and also on the form. I just can’t get it to work (:

[mike60smart]

Hi

Your table CropT structure is wrong.

You should initially have a form to manage the Varieties associated with a Pivot as well as the Generations associated to a Specific Variety.

[jodilees]

Thanks, I imported them from the excel spreadsheets we are currently running from.
How does a novice like me fix it?

[mike60smart]

Hi Can you upload a list of the Generations?

[xps35]

There is more wrong in your model. You must relate tables by key. And in related tables you must only include the key of the main table. So this is wrong:


First rebuild this to:


But that is not all. If I look at the distinct values of the (improved) CropT table (excluding ID and Generation) I see:

It looks like this table is not properly normalized. For each PivotID in this table, I see the same Paddock Name and DISTRICT. It seems to me those fields shout be part of PivotT.

Get all these things right before you continue. It is essential to have a proper structure first.

[June7]

Rename the comboboxes different from the field names, like: cbxPivot, cbxVariety, cbxGeneration

Generation combobox properties:

RowSource: SELECT DISTINCT Generation FROM CropT WHERE Variety=[cbxVariety];
BoundColumn: 1
ColumnCount: 1
ColumnWidths: 1"

RowSource for other two comboboxes:

SELECT DISTINCT Pivot FROM CropT;

SELECT DISTINCT Variety FROM CropT WHERE Pivot=[cbxPivot];


Advise not to build lookups in table, just comboboxes or listboxes on form, unless you really must have multi-value field which I also advise against.

Really want Generation field set for multi-value?


Agree with xps35: should be saving CustomerID into OrdersT, not CustomerName and same could be said for PivotID. Should not be PivotID and Pivot fields in CropT.

I expect you used wizard to create the lookups in table and now removing them to fix the key links will be a challenge. Might be easiest to rebuild tables from scratch.


Advise not to use spaces in naming convention. And avoid same exact field name in multiple tables.

Pivot is a reserved and should avoid reserved words as names. Review http://allenbrowne.com/AppIssueBadWord.html

[jodilees]

Hi All

Thank you so much for the hints and tips. I started again and rebuilt what I was aiming for without any problems. I think I had fiddled with too many settings trying to get it working previously.

With a clean slate it was easy to set up.

In hindsight I should have asked for some help earlier rather than creating a tangled web.

Kind Regards

Jodi

[jodilees]

Hi All

Im still a novice, but have managed to find my way through for what I needed to do. However I am having an issue with one part.

I set up my forms for entry with cascading combo's and it all worked great.

But when I went back to my tables, I was not getting the result there. I updated the tables to be the same settings as in the forms, however the cascading combo is just not working how it should be.

Can you not have cascading combos in a table?

Thanks in advance to any and all that respond to my query.

Cheers

Jodi

[mike60smart]

Hi Jodi

What values are being stored in your tables in regard to the Cascading Combobox's?

[jodilees]

Hi Mike
They are l each doing a lookup back to the crop list.
Each pivot has multiple varieties and multiple generations.
My cascade in my form is
variety where pivot in form = pivot on crop
Generation where variety in form = variety in crop and pivot in form = pivot on crop
I attempted to replicate this logic in the table and when I did it seemed to confuse the form cascading combos.
I’m trying to achieve a data sheet type view of orders.
I hope this isn’t too confusing
Cheers
Jodi

[mike60smart]

Hi Jodi

Easiest way we can resolve this is for you to upload a copy of the database