Limiting Staff to only one table, Relationship Question

[vapid2323]

Hey guys!

So it might just be Monday but I am having a hell of a time wrapping my mind around how to do this.

I know there is a way to Keep my database down to only one Staff table but I need to link the staff to three things at this point.

A Region, SubRegion and Site.

I was thinking that all I needed was a bunch of FKs in the Staff table but I was not sure that was the best way to set it up.

I have attached my current relationships to give you a visual of what I have going on.

 

[vapid2323]

This is what I thought might work, I think it will allow me to track everything correctly.

EDIT: Yah I don’t think this will work as you can pick any site, and any region for that site.... I need to make sure the relationship will automatically track the Region and Sub region for each staff member. I hope that makes sense.

 

[vapid2323]

Any tips? Do you guys need more information about my issue?

 

[CBrighton]

Will the users be entering the data directly into the tables?

If not then the bound input form can have 3 combo boxes:

The first would be your region. This would have a row source of the region table.

The second and third would be disabled by default, the after update event of the first could then enable the second and requery it's row source which is a query showing all sub regiong within the region selected in the first combo box.

The third would be similar to the second but with a row source linked to the site table and the criteria being a reference to the second combo box.


I find it easier in situations like this to use the form to limit the input.


I have a similar setup in my database, but with an extra level. I have Area, Department, Section, Team. 4 comboboxes, each using the previous as the criteria for the row source to ensure the users can only select a department within the selected area, etc.

 

[vapid2323]

Will the users be entering the data directly into the tables?

If not then the bound input form can have 3 combo boxes:

The first would be your region. This would have a row source of the region table.

The second and third would be disabled by default, the after update event of the first could then enable the second and requery it's row source which is a query showing all sub regiong within the region selected in the first combo box.

The third would be similar to the second but with a row source linked to the site table and the criteria being a reference to the second combo box.


I find it easier in situations like this to use the form to limit the input.


I have a similar setup in my database, but with an extra level. I have Area, Department, Section, Team. 4 comboboxes, each using the previous as the criteria for the row source to ensure the users can only select a department within the selected area, etc.

So you’re saying to use cascading combo boxes on a form and not to let the table situation bother me.

I had a feeling that might be the solution; I just wanted to make sure. So to clarify my second post would work? I spent some time working on the after events but I found it difficult to limit the records and I was not sure if it was because of my relationships or my VBA inexperience.

 

[CBrighton]

I'm saying I'd use cascading comboboxes. Whether that's the 'best' way I don't know, but it works so long as the users are only using forms to input / amend data.

As for your difficulty getting them to update, you should be able to set all the rowsources in design view (and set enabled to false on any comboboxes other than the first one). then the after update event shouldn't need more than:

cboComboBox2.enabled = true
cboCombobox2.requery

This will enable the combobox below and requery it's rowsource.

Ideally it should also clear and set enabled = false to any even lower down (in this case:

cboCombobox3.enabled = false
cboCombobox3 = null

This will ensure that if someone changes the value in combobox 1 it clears the data in the lower ones to prompt them to input the data again for the new sub-region / site.

 

[vapid2323]

I'm saying I'd use cascading comboboxes. Whether that's the 'best' way I don't know, but it works so long as the users are only using forms to input / amend data.

As for your difficulty getting them to update, you should be able to set all the rowsources in design view (and set enabled to false on any comboboxes other than the first one). then the after update event shouldn't need more than:

cboComboBox2.enabled = true
cboCombobox2.requery

This will enable the combobox below and requery it's rowsource.

Ideally it should also clear and set enabled = false to any even lower down (in this case:

cboCombobox3.enabled = false
cboCombobox3 = null

This will ensure that if someone changes the value in combobox 1 it clears the data in the lower ones to prompt them to input the data again for the new sub-region / site.

Well that sure looks a lot more simple than what I was trying lol!

I will set it up and try some things out later today! Thank you for your help!