Dynamic cmd button creation based on recordset

[Greg Gaines]

I'm trying to create a form with buttons populated by a recordset without
actually displaying the recordset. I want to use each recordset item as the
caption on the corresponding button. I'd like to display the buttons in
rows of 10. The buttons will also be filtered by category. I've done this
in ASP years ago, but I'm having problems trying to figure out how to do
this in Access VBA. This is being built in Access 2007. If anyone can help
or point me to another thread that relates I'd appreciate it.

Thanks for any help or suggestions!

Greg

 

[RDub]

If I understand what it is that you are attempting to do, then I gott'a tell
you this at best is a non-standard way to present data, and at worst a
horrible design.

But be that as it may, if you are determined to go doen this path, what you
want to do is to create a form with as many buttons as you think you will
EVER need all lined up nice and pretty. The name of each button should be
something like cmd1, cmd2, cmd3, etc. Set the on OnClick event of each
button to a function you will create on your form like =DoThisWhenClicked().
Access doesn't directly support Arrays of controls, but this is a reasonable
way to provide that functionality.

Then when you form loads, open your recordset and do the iteration thing,
setting each buttons Caption and probably its Tag property with something
that will let your function what the heck to do if clicked. You can set the
Caption and Tag properties using the following syntax from within your
iteration of the recordset.

Me("cmd" & x).Caption = whatever you like
Me("cmd" & x).tag = Something else

Where x is an integer value that you are incrementing in your looping code.
You also want to hide all of the extra buttons that you are not using, and
perhaps even dynamically resizing the form so there isn't a bunch of blank
space at the bottom.

In the code behind the form you will have a function that handles all of the
Button Clicks. Might look like this

Private Function DoThisWhenClicked()

Select Case Screen.ActiveControl.Tag
Case this
Call this one
Case that
Call that one
Case ...
Call whatever
Case else
Call when all else fails
End Select
End Function

As I said this sort of interface sounds like a bad Idea to me, but if you
like support calls from confused users, "Go ahead and make their day"!
Before you actually write this code, you might want to rethink what it is
you are tying to do. Feel free to ask questions here. There are many
bright folks that may be able to come up with a better way.

Rdub