This is something I was thinking about before a short holiday break (Strasbourg – very pretty) …
Suppose that we have an inventory of Components, perhaps in a manufacturing or engineering context. For each Component, we record its Parts (that is, sub-components). For example:
Sorry, my creativity did not extend to dreaming up meaningful data – think of car parts, or something.
So, an A is a top-level component, and consists of Bs and Cs (we do not specify quantities – an issue deferred); a B consists of Fs and Gs; E, G and H are elementary components (indicated by underscore). A component, such as B or H, can be used by multiple higher-level components. The restriction to 3 part types per component is just for simplicity.
These relationships form a directed, acyclic graph (there are top-level and bottom-level components, and no component can contain itself directly or indirectly).
Even with a very simple example, like above, it is quite difficult to see what contains what. With a realistically large inventory, say hundreds or thousands of records, it would be next to impossible.
One solution would be to support navigation around the graph of relationships:
- Drill-down the ‘contains’ relationship: for example, [A contains B, C] >>> [B contains F, G] >>> [F contains H] >>> [H is elementary]
- Move along the ‘used in’ relationship: for example, [B used in A] >>> [B used in D] >>> [no more uses of B]
Moving along the ‘used in’ relationship for ‘_’ (underscore) takes us through all the elementary components.
When you get the end of a ‘used in’ chain, you could have the option to start again from the beginning (as in textual searches):
In addition, we could have navigation ‘back’ through the sequence of visited Components.
We could offer these operations and the resulting ‘current record’ to a user via a form. However, it seems more lightweight to use keyboard shortcuts.
So, how might we do this? As previous postings have illustrated, I like the idea of a cursor object, which provides move operations of some kind, and access to the ‘current record’. In this case, the cursor position will be indicated visibly to the user by explicitly Selecting the relevant cell (and thus scrolling to the record).
An instance of the cursor object can be created on Workbook_Open, and linked on Class_Initialize to a the table. For simplicity, let’s assume that we have a single worksheet with a single table. As usual, there are advantages to using a v2007 Table (in VBA a ListObject), but you could get it to work in v2003.
More on the design/code in the next posting.