databaseLessons

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. To see my full answer, check out Buttons: Now you see them, Now you don’t.

p.s. I’ve started including “Digg This” logos. If you appreciate all my hard work on these articles, please “Digg” my work. thanks

I noticed a posting on a forum the other day. The writer wanted to know how to get a simple list of all the tables in their database.

My initial research resulted in this query.

SELECT MsysObjects.Name
FROM MsysObjects
WHERE (((Left$([Name],1))<>“~”) AND ((Left$([Name],4))<>“Msys”) AND
(((MsysObjects.Type)=1)))
ORDER BY MsysObjects.Name;

The writer was grateful but then re-posted a bit later to say that this did not list all the tables. A quick bit of research showed me that linked tables have a MsysObjects.Type=6, so …

SELECT MsysObjects.Name
FROM MsysObjects
WHERE (((Left$([Name],1))<>“~”) AND ((Left$([Name],4))<>“Msys”) AND
(((MsysObjects.Type)=1) OR ((MsysObjects.Type)=6)))
ORDER BY MsysObjects.Name;

It is easy to loop through objects on a form that seem to be unrelated, and are not even connected to a recordset. We can just use the Tag property.

See the article at Looping Through Form Objects Using the Tag Property.

An age old question. How can we use a combo box to add new values to its list, “on-the-fly”?

This question has been answered thousands of times, and people keep asking it. Well, I am adding my voice to the side of the answers crowd in the hopes that at least 1 person will not have to ask.

To read my version of the answer, visit Not-in-List.

p.s. This one comes with a sample database!

Just a friendly reminder. You need to ALWAYS use Option Explicit. Look at the top of any module of code, whether under forms or modules. The first two lines should be …

Option Compare Database
Option Explicit

By default, Access only puts the 1st one. You have two ways of getting the 2nd line in there.

1. type it in manually, or,
2. under Tools, Options in the VBA editor, turn on “Require Variable Declaration”

What does it do? Forces the “compiler” to make sure that EVERY variable has been declared. Basically, helps stop typos from causing “bugs”.

Just trust me on this one, and do it!

I may have given this one before, but it is SO useful. I personally had to use it last week.

The computer you are at does not have the Linked Tables utility working and you need to know where the user’s data is stored. Try this command in the VBA Editor’s Immediate Window (Ctrl-G)

? CurrentDb.TableDefs(”Holders”).Connect

Where Holders is an example table name (change that to one from your database).

(NOTE: you may have to have DAO references on. see this page if you do not know what that means)