Linking two combo box in a form

[thedavix]

Good morning



I have a question regarding how is the best practice to link 2 combo box
What is following is a simplifed example of my problem

I have 3 tables
t_client
t_Departements
t_Divisions

One client contains a name and belongs to a department
A department contains a name and belongs to a division
A Division contains just a name

In my form f_client I have my client Name, my division and my departement.

What I would like to do is to use the Division first in order to filter out the departments from this division, so what I've putted is a requery of of the departement on my Division_Change() event.
Till that point everything works.

But now I have issue when I want to select the values in the department field, I simply can't some time it tells me that my relation of my table is wrong, or sometimes it tells me that I need to save the record first before selecting the value, these two cases depends if I'm in edition or new record mode.

My goal would be to use this rather simple form in a continuous form.

I've putted my sample db in attachment, I don't know if somebody could help me here I would really appreciate

Thank you
David

[alansidman]

Look at these links. There are two different solutions here. Both will work. One suggests the use of one table while the other suggests the use of multiple tables.

http://www.access-programmers.co.uk/...d.php?t=223980

http://www.fontstuff.com/access/acctut10.htm

Alan

[thedavix]

Thank you Alan, it helped a lot!
It's now working in a regular form, in continuous form when I change the the independent combox box value, all the lines are changed :-(

[thedavix]

I stand corrected, I used the second option and it works for continuous form but I find it very ugly to add the state field in the main table as we already have this relation in the other table...
Anyway.. I think this is the bad part of working with access...