Last month our function contained the following code (if you need to review check out August 06):

Public Function CubitsToInches(dblCubits As Double) As Double
   CubitsToInches = 30 * dblCubits
End Function

The value 30 is our conversion factor, and it works wonderfully for me since it represents the length of my cubit. What about Andre the Giant who has a cubit of 54 inches, or Mildred the Midget who has a cubit length of 12.5 inches? We could hard code separate functions for those varying lengths, but wouldn't life be easier if we could just tweak our function to handle cubits of different lengths? That's just what we'll do! And to make life even easier, once we've entered the length of a person's cubit, we never want to worry about it again. This situation calls for a cubit table – like the following.

Our table has three fields

1. An AutoNumber primary key called CubitKey – I'm a fanatic about primary keys; see Keys are Critical.
2. A Person field to record the person's name
3. A cubit length (CubitLen) field where we'll record the length of the cubit for each person.

Once we populate the table it looks like this:

Now we can tweak our function code to handle variable cubit lengths for individuals from the cubit length table.

Our revised function looks like this

Function CubitsToInches(dblCubits As Double, strPerson As String) As Double
   Dim dblInchesPerCubit As Double
   dblInchesPerCubit = DLookup
("CubitLen", "tblCubit", "Person = '" & strPerson & "'") CubitsToInches = dblInchesPerCubit * dblCubits
End Function

Compared to our earlier function we've made just a few changes:

1. We've added another argument (see last month's issue for an explanation of arguments) strPerson As String to the function. This code allows a user to select a person and associated cubit length to be used for the conversion. We're asking that this value comes in as a text string, hence the argument As String
2. We've added a new variable Dim dblInchesPerCubit As Double. This variable will contain the conversion factor (the inches per cubit for each individual). For example, Andre’s conversion value (inches per cubit) is 54; for Mildred it will be 12.5.
3. We've added a line to lookup the value of inches for the person requested: dblInchesPerCubit = DLookup("CubitLen", "tblCubit", "Person = '" & strPerson & "'")

This lookup function is a powerful way of easily getting a value from a table. It's part of a much larger discussion of domain functions, so I won't go into it too deeply here, but basically it takes three arguments

• the field to lookup, in our case CubitLen
• the name of the table tblCubit and
• the criteria: "Person = '" & strPerson & "'"

The criteria is the only tricky part, so I'll break that down for you. It says give me the record where the field "person" is equal to the variable strPerson. In other words, if the conversion is to be done for Mildred, go to the record where the field Person is equal to "Mildred."

We also have some punctuation to deal with. Each of the three arguments: Field Name, Table Name, and Criteria are surrounded by double quotes, and our criteria field Person is a text field so that the person we are looking for must also be in quotes, but because the criteria field as a whole is in double quotes, when we designate the person we use single quotes. Finally since we're using a variable strPerson we also are doing concatenation – hence our use of the ampersand.

I recognize that this is not terribly friendly or easy – many users have trouble with dlookup statements and I've not fully covered them here. I'll be doing that in a future issue. For your purposes in the mean time when you use a dlookup statement use the Access Help system to get an explanation. Though the help system is not terribly good, it will give you a start.

Now that we have our function in place, let's give it a whirl.

In the VBA immediate window (type ctrl-G to get there) type:

? CubitsToInches(3,"Andre")
and hit return.

This statement is used to answer the question “How many inches long are 3 Andre cubits?” It gets the answer by calling Andre’s conversion factor from the table. You should get back 162. That’s 3 times 54, the Cubit measure for Andre.

Now try the same thing replacing Andre with Mildred and you'll get 37.6.

Our function is considerable more flexible now. It can return the appropriate cubits for anyone who is recorded in the Cubits table.

Our function is pretty nifty and has many uses – it can be called in a form, report or query – we'll see that and more as we move forward. But what happens if a user asks for inches to cubits for someone who is not in our table? That's a real problem and currently would cause a very ugly error message – which we really want to avoid. We'll also tackle that problem as we make our cubits to inches function more powerful and flexible.

This is a trap that I see a lot of users fall into, and Access makes it very easy to be a victim here. When you're creating a form or a report, Access not only invites you to use a query as a data source, but if you build the data source from within the form and explicitly save the query, you'll end up creating a new query.

The problem here is two fold – as your application develops you'll have more and more queries and it will become much harder to navigate. Although this is a problem, it’s not nearly as serious as the one you face if you delete or change a query that a report or form uses. In that case you'll get an annoying dialog box asking you to supply cryptic information, and your form or report may not work at all.

The way around this is to create the data source from the form or report design view (it's just like creating a query) and then close the view. Access will ask you “Do you want to save the changes made to the SQL statement and update the property?”

Say yes and the SQL statement will be embedded within your report or Form. This process may not be intuitive so I'll cover it in much greater depth in a future article.