'Serving the Microsoft® Access
Community since 1997'

Free Samples
About Us



Buttons: Now you see them, Now you don't

A reader wrote to ask me the following:

Thank you very much for your sample databases. I did software for my hotel reservations and now I want to make SW for cash desk in my restaurant. Problem is that I do not know how to make matrix of buttons. Each buttom must have another name and another function. Names I have in my store software as PLU. For example I want to sell coffee then my staff will select category drinks and my SW will prepare matrix of buttons. There will be ice coffee, coffee, etc. How to do it. I have approximately 200 items in assorted categories.

I found this question quite interesting and it grabbed my curiosity. I actually dropped what I was doing to experiment.

(NOTE: this article was written based on research using Access 2000. It should work on other versions.)

NOTE: All the VBA code segments on the Database Lessons site assume that you have DAO references active. If you are not sure what this means, and you are using Microsoft Access 2000 or higher, click here.

Step 1. Create a form with a large group of buttons (as many as the largest grouping will need).

Step 2. Name the buttons in an organized way. Perhaps cmdFunctionxx where xx is a number

Step 3. Change the "Tag" property to have a code. For example, put the word "buttons" in the Tag property.

(article continues after sponsor spot)

Step 4. Change the visible property for all the buttons to "No".

Step 5. Create a common function for handling the sales. Example name would be "basSale(strCode as string) as boolean". This assumes that you will pass a string field representing the item or service being sold. The code for this function must handle all the different codes that are possible.

Step 6. On the Events tab of the command button's properties, choose the 'On Click' property and type in =basSale('abc'). It does not matter about the abc as that gets changed at time of sale.

Step 7. When the employee chooses a category of product, you will have VBA code that will fill in the correct button labels and function references. For example, the following code changes the label on button # 12 to coffee, changes the OnClick command to match, and makes the button visible.

    me.cmdFunction12.caption = "Coffee"
    me.cmdFunction12.onclick = "=basSale('coffee')"
    me.cmdFunction12.visible = True

You can use code similar to that from the article Looping Through Form Objects Using the Tag Property to turn all the buttons back to invisible at the end.

As time allows I plan on writing a more in depth version of this article, complete with an accompanying database.

Note: This web site dedicated to MS Access database users is an independent publication of Richard W. Killey and is not affiliated with, nor has it been authorized, sponsored, or otherwise approved by Microsoft® Corporation.


© 2006, 2007, 2008 Richard W. Killey. All Rights Reserved. - Privacy Policy