"Cascading" form/combo boxes (1 Viewer)

tmyers

Active member
Local time
Today, 05:59
Joined
Sep 8, 2020
Messages
696
Cascading was about the only way I could describe this, but it may be the wrong word choice. This is a new app I am working on for quoting the electric for multi-building apartment complexes. I will be using jargon related to the industry, but will try to explain as I go so it is easier to understand my thought process.

On a form I am making, the general idea is as follows:
I want combo box that allows selection of a specific building (input elsewhere).
That combo box then sets the forms BuildingID, allowing an entry via a textbox for the buildings electric meter part number and designation.
Those entries then power another combo box that is populated with those entries. Seems like a simple query for the row source. Easy enough.

The next part is where I start to get lost, as I have not "cascaded" this deep before. I would then need another text and combo-box that allows entry for the branch devices (the branch devices are parts that attach to the unit entered previously that actually houses the electric metering equipment). I would assume the combo would have yet another query behind it that is looking for the previous two ID's to narrow it down.

Once I get that working, I would then need a sub-form datasheet to enter the bits and pieces that go into the branch devices (such as the meters themselves and the breakers). So I need to be able to set essentially 3 different ID's for the datasheet to work in the way I think it needs to. I need it to go under a "master" building, then a (second master?) meter, then to the child branch devices so I enter the parts that go into each branch device.

To try and give a summary:
Building --> Meter Main --> Branch Meter --> Several parts

Does anyone understand that mess?
 
Last edited:

MajP

You've got your good things, and you've got mine.
Local time
Today, 05:59
Joined
May 21, 2018
Messages
5,047
Not sure of you VB skills, but you may be interested in a tree view to do something like this with many layers. Any chance you could post a sample db.? If interested I can see if I can throw it into one.


To caveat. No reason this cannot be done with traditional cascades or subforms. Just another possibility. It sounds like something that would fit well.
 

tmyers

Active member
Local time
Today, 05:59
Joined
Sep 8, 2020
Messages
696
Sure thing!
I have more or less just started so there isn't a a ton to it.
I have started trying to make the form. It is the in the ProjectQuote form, Building Meters tab. I was attempting to get the second combo working at time of attaching.
 

Attachments

  • Example.accdb
    2 MB · Views: 56

MajP

You've got your good things, and you've got mine.
Local time
Today, 05:59
Joined
May 21, 2018
Messages
5,047
These types of databases can be pretty tricky. So better get your tables correct before jumping in. There have been some very similiar ones. Some very complex. I would search this site for some ideas.

I would think you need several many to many junction tables, which I do not see. Only see one for building units.

In your design without some junction tables every meter, panel, disconnect, etc would have to be discrete. I am not suggesting this is wrong, but hard to say without seeing real data. If for example I pull breakers from a list of breakers I would not want to type the name and cost every time. I would want to reference the breaker table and store a breaker ID.

One thing that can get confusing (which you might have) is a junction table with more than two foreign keys.
 

tmyers

Active member
Local time
Today, 05:59
Joined
Sep 8, 2020
Messages
696
These types of databases can be pretty tricky. So better get your tables correct before jumping in. There have been some very similiar ones. Some very complex. I would search this site for some ideas.

I would think you need several many to many junction tables, which I do not see. Only see one for building units.

In your design without some junction tables every meter, panel, disconnect, etc would have to be discrete. I am not suggesting this is wrong, but hard to say without seeing real data. If for example I pull breakers from a list of breakers I would not want to type the name and cost every time. I would want to reference the breaker table and store a breaker ID.

One thing that can get confusing (which you might have) is a junction table with more than two foreign keys.
While I was messing with it, I more or less started to come to the same conclusion. This app will more than likely end up needing several junction tables to do what I need it to.
 

Users who are viewing this thread

Top Bottom