Relationships between tables

[Anauaton]

Hi,

I started using access on my own last November and I have received already some very useful tips through this forum, so I hope to repeat this experience with something which seems to me now a lot more sophisticated than last time.

I have now 3 tables, one containing my main records which I update via a form, and two minor tables which I update only in order not to update manually drop-down lists on my form.

On my form, there are two fields called, one "Sector", and one "Industry". So I created a table for the Sectors, which appear as a drop-down list in the field "sector" on my form - so far so good.

I created a table Industry, in which I created my industries linked to their respective sectors (many industries per sector but only one sector for each industry).

So when I update the field Industry on my form, I expect to see only the industries linked to the sector chosen before, in a drop-down list. That means, when I update the field Sector, I expect to see, in the drop-down list Industry, only the industries linked to this sector as per my table Industry.

What I have at the present is, in my drop-down list, all the industries that I have created in my table industry, disregarding the sector associated; which is still ok, since I can find the one I am looking for, but not very safe since I don't want to be able to chose an industry that is not related to the chosen sector.

Hope this is clear. Ah, and I am using Access 2007.

Thanks for your help.

 

[pbaldy]

The concept of what you're describing is called cascading combos. I demonstrate a couple of methods here:

http://www.baldyweb.com/CascadingCombo.htm

 

[Anauaton]

I used your first method (the second one seemed a bit complicate?) and it works!

Thanks a lot - I also like your grouping stuff on your first field which allows you to gather all the data in only one table, so I don't know now whether I really need two tables... I will figure it out early enough I guess.

Thanks a lot for your USEFUL help

 

[pbaldy]

From your PM (better to post to the thread than PM):

How can I ask in the afterevent code to put the default value in my second drop-down instead of a requery? the reason why I ask is I do not allow 0 length data; I already input a constant default in that field but I don't know how to call for it.

You would still want the requery, to update the selections. If you wanted to then select the first item in the list:

Me.ComboName = Me.ComboName.ItemData(0)

 

[Anauaton]

Noted, thank you. So that means my default value needs to be in my row source too I guess? I was thinking of calling something like "Please select..." but to put this in my industry table looks weird..?

Thanks for your help.