Dynamic Form?

[odin1701]

So I am working on a report builder for my current database. Basically it will let you choose a variety of things which will generate a SQL WHERE clause to use to modify a base report prior to opening the report.

So I have several things that I will be able to filter by. E.g. Product Type, Customer, Discounts, Margins, etc.

For the Product Type in particular, users of the database will be able to add additional Product Type codes as needed in the future. I would like to be able to create a cascading combo box which shows these - but only shows the available ones.

For instance, let's say there are 5 type codes.

In the first combo box you select TYPE1, then the second combo box should only show TYPE2, 3, 4, 5. Then if you select TYPE3 in the second combo box, the third combo box should only show TYPE2, 4, 5 and so on.

That is the "simple" part. What I'm wondering is if there is a way that I can cause additional combo boxes to be dynamically added to the form? Say that in the future someone adds TYPE6, 7, 8. There were originally only 4 combo boxes. Now three more need to be added. The number of combo boxes would be based on the number of records in a table.

Is this even possible?

I could probably just create 15 or so and just hide all but the needed ones but I'm curious if something like this is possible.

 

[gemma-the-husky]

If you want to insulate users from Access design (and I am sure you do) - then I think you need to pre-design the combo boxes. If they want more options give them more boxes. The problem with databases is that they are just too powerful to allow unfettered use.

So 2 options to achieve what you want

1. A generic boolean function to compare the table values of all the fields in which you may be interested, with the combo box values, and return true or false depending on whether you want to include the row or not

2. Just give the users a spreadsheet dump of all the data, and let them have that to play around with themselves.

 

[pbaldy]

Another option might be a multi-select listbox to let the user choose. That would be dynamic, presuming it was based on a table containing the types.

 

[odin1701]

Another option might be a multi-select listbox to let the user choose. That would be dynamic, presuming it was based on a table containing the types.

lol...but that would be too simple!

Yes that should work just fine.

 

[pbaldy]

Simple-minded people come up with simple solutions.

 

[odin1701]

Simple-minded people come up with simple solutions.

I do have a knack for trying to make solutions to problems harder than they need to be

Also, in the end everything will be compiled and in an executable DB....so if there is some way of doing it, it would probably fail now that I think about it.

 

[pbaldy]

Not sure what you mean by executable, but anything that requires design view would likely fail in an mde/accde or in the runtime version of Access.

 

[smig]

Take a look at this
http://www.access-programmers.co.uk/forums/showthread.php?t=205613