Here is an approach that you can CONSIDER - but remember that you are asking for a BIG headache because of its dynamic nature. If you are not good in VBA, don't try this at home. (If you do try it, you'll be a lot better at VBA when you are done, I assure you.)
1. Form is almost guaranteed that it must be unbound. You will be doing everything based on dynamic recordsets.
2. Define ahead of time the maximum number of buttons you will allow to be on screen at once. This will be essentially a visibility/touch-screen resolution issue.
3. Look up definitions of these terms in ACCESS HELP:
LEFT - as in, location of left edge of a control
TOP - as in, location of top edge of a control
WIDTH - as in, width of a control from left to right edge
HEIGHT - as in, height of a control from top to bottom edge
CAPTION - which is a property of command buttons that don't have pictures
TWIPS - which is the unit of measure on screens
VISIBLE - which is a property that lets you see something
ENABLED - which is a property that allows something to be actively used
RECORDSETS - learn how to manipulate, query, dynamically build the SQL to create an on-the-fly recordset AND QUERY IT.
4. Create a form with all of your command buttons that you will allow at one time. Name the buttons CMDBUT001 through CMDBUT0nn, nn= max allowed.
5. In the ONCLICK routine of each button, write code that stores a constant (equal to the button number) in a central variable declared in the form's class module's declaration area. Have a couple of arrays - probably strings - in that same declaration area, defined to have as many elements per array as the number of buttons you will allow. Have a "TOP" and "LEFT" variable (named something other than TOP and LEFT, which are keywords) in this area too. Finally, you will need some sort of STATE variable that shows you what is next on the agenda - INITIAL button, continuing selection DRILL-DOWN (which is probably a loop of some sort, but you could sub-divide it), OTHER action, whatever states you define.
6. After storing a value in the ONCLICK, have the same action routine call a common routine that does the following in VBA:
a. Identify which button was pushed from that common variable.
b. Decide on what you want to do to restructure everything.
b.1. If this requires you to open a dynamic recordset, build your SQL based on the current selection.
b.2. execute the dynamic SQL
b.3. When you are finished with this, remember the kindergarten rules: If you opened it, close it. If you take it out, put it away. If you mess it up, clean it up. OTHERWISE, you will eventually get an "OUT OF MEMORY" error of some flavor.
c. IF this action means you will be displaying a different set of buttons, then
c.1. reload the class-module arrays with the NEW title and any other new info you need for the next action. (Probably you will do this in the stuff I've labeled "b" earlier, but it is hard to get everything in the right order.)
c.2 In a loop AFTER c.1, rebuild the buttons based on the array contents. Reset the button-builder position data.
c.3 For each button starting from 1,
c.3.a. Move the button to the stored TOP and LEFT area.
c.3.b. Update the stored TOP and LEFT area
c.3.b.1 If you have room, just add WIDTH to the LEFT placeholder.
c.3.b.2 If not, reset the LEFT placeholder to the reference LEFT and add HEIGHT to the TOP placeholder instead.
c.4 After all "active" buttons are defined, as a separate loop, reset the rest of the buttons to move them to a neutral "resting place" that is NOT part of your active display area. Mark them .VISIBLE=false, .ENABLED=false. They can overlap in that place.
d. If this is not going to entail more buttons, then switch to (POP UP?) a new form. You might have to use a "MOVE_TO_FRONT action" -see the ACCESS HELP files on that topic, without the underscores perhaps...
e. When that new form comes back to you, reset your state variable to its INITIAL setting and reload your arrays appropriately.
f. The form's OnLoad event must simulate a button click that is, in essence, "RESET to TOP LEVEL"
This is as dynamic as it gets. The only saving grace MIGHT be if you have good indexes and a fast machine, you would be able to do this fairly fast. Otherwise, this could plod along like a couple of old mules with a heavy load.
WARNING: I consider this to be VERY VERY AMBITIOUS - but it sounds like it might do what you wanted done. The key to this will be the common "staging" routine that rebuilds the arrays and rebuilds the buttons.
Make several subroutines callable by the staging routine. I see at least one to build visible buttons, one to move invisible buttons, one to reload your button-defining arrays based on a dynamic query. Might be more than a few others here, too.
Remember that you can synthesize a name if you use an orderly method. E.g. "CMDBUT0" & Format$ of the index to build a string such as CMDBUT001, CMDBUT002, etc. So your loops can build the required name on the fly to pass in to your button relocator routines.
Arrays and variables you might need:
BtnCapt: Array of strings for the SHORT caption you want for each button
BtnCode: Array of ? to define what each button should do
BtnLeft, BtnTop: Scalars - Next place to put a button
BtnLeftMax, BtnTopMax: Scalars - constants you define some oddball way to limit the buttons you will build or otherwise give you a sanity check.
BtnUsed: Scalar for the button that was pressed to store its index.
Then you have
FrmState: A state code that tells the common routine the state it was in when it was called - and which is updated when anything does a state change.
LastSQL: A string to build the dynamic query implied by a DRILL-DOWN click event.
This is VERY rough and will not come into existence quickly. Nor should you consider this list definitive or complete in any way.
