Two CBO's and Tables relationship

[durexlw]

I feel confused about something and I have the need for assistance because solving this small thing would make my life easier. I very much appreciate you taking the time to read this.

I'll focus in on a small part of my database:

----------------
| tblBreakpoint|
----------------
| BreakpointId |
| Remarks      |
| ...          |
----------------

each Breakpoint can have only one Module and a Module can have many Breakpoints

----------------
|  tblModule   |
----------------
| ModuleId     |
| ModuleName   |
| ....         |
----------------

each module can have only one Function and a Function can have only one module

----------------
| tblFunction  |
----------------
| FunctionId   |
| FunctionName |
|   ...        |
----------------

I want to normalize my database as much as possible.

I will make a make a form for Breakpoint containing two comboboxes. The top combobox will contain all Modules and I want the second combobox to contain only the Functions that are related to the selected Module from the first combobox.

I know this can be programmed, that's how I do it now: in the onChange-event of the first combobox, I load the content of the second combobox.

However: what I would like to know specifically 3 things:
--> what is a common way to solve this?
--> Is there a way to solve this using just table-relationships? (drop 2 cbo's on the form and with correct relationships access populates the second and/or the first automatically)
--> What are the correct table relationships for this and therefore what missing ID's (used for relationships) have to be filled in in the above 3 tables?

 

[neileg]

each module can have only one Function and a Function can have only one module

If this is true, why have the functions in a separate table?

--> what is a common way to solve this?

Do a search in these forums for cascading combo boxes.

--> Is there a way to solve this using just table-relationships? (drop 2 cbo's on the form and with correct relationships access populates the second and/or the first automatically)

No you need queries and a snippet of VBA.

--> What are the correct table relationships for this and therefore what missing ID's (used for relationships) have to be filled in in the above 3 tables?

You need to hold the primary key of the one side of the relationship as a foreign key in the many side

 

[durexlw]

If this is true, why have the functions in a separate table?

When you said the above, Neil, I felt relief because it allowed me to see that trying to normalize this is not functional.

Do a search in these forums for cascading combo boxes.
No you need queries and a snippet of VBA.

Thanks for the directions. That's all I need.

You need to hold the primary key of the one side of the relationship as a foreign key in the many side

Thank you for confirming this.

I feel appreciation for the way you responded in short and specific ways because it made me feel understood. I would like you to know that by reading your answers, I found myself relieved of this issue that's been biting my brains for some while now and I want to thank you for that.

Regards,
Andy

 

[neileg]

Happy to help.