HCS Consulting Group.
We make Complex Systems simple

Using Class objects with ms-access.

By Albert D. Kallal
Tuesday, September 16, 2003

Why would I want to use a class object in ms-access?

The real trick to understanding class objects in ms-access is figure out WHEN to use them. You can’t normally sit down and say ok, lets use a class object. You need good reason to use one. So, when should you use an object in place of simply writing some code?

Generally, I use and create a class object when the process, or "thing" I am dealing with starts to become very complex. The other big reason is when you need multiple instances of the same “thing”. Class objects allow you to “set” values of the object. Hence, objects can contain many settings that you the programmer can set in code. Hence, if the problem you are dealing with has MANY SETTINGS in code, then again a class object makes sense. When I say settings, I don't mean things like height, weight and age of a person (that information can easily be placed in a table). When we say settings, we are talking about a bunch of variables and code and data that we are required to work with. So, when we have to work with a bunch of values that DON'T normally exist in the database, then a object starts to make sense. Further, in many cases, we might need things like height, weight, age. When data like height etc. IS in the database, but it is taking too much work to get information about the patient while writing code, then again a object makes sense. So, lots of code, lots of variables and lots of information from tables is a real winner. In fact, all of the classic reasons for using objects apply equally well to ms-access.

I had a lot of people ask me why use them? What are they for?

I going to jump right into some thinking that takes place when I am deciding on when to use class objects.

Lets assume we make a database to store information about doctors patients. You can easily build a data entry screen if the task at hand is simply entering a last visit date of that patient. If you need to print some reports on that patient, then again a button on the form with a bit of code will more suffice. Even if you are writing some billing software, then again a object will not help a lot. However, if you start having some very complex pricing procedures, then you certainly might start looking a creating pricing object.

Really, though, to work with these patients, you don't really need to create a patient “class” object. It just don’t make sense.

On the other hand, if you need to execute several "processes" on a patient, then a object starts to be come handy. When you find that you need a lot of code AND data to complete a certain task, then a class object starts to make sense. Hence, if for example you have a complex booking system that allocates doctors to that patient, or you have to schedule certain tests and procedures that requires you to book that patient to special exam room, then creating of a object now starts to make sense. Hence, creating some type of booking object will now start to be worth the effort. You might even consider making a patient object in this case also. 

Often, regular coding techniques will suffice,. It is just that things start to get real messy after a certain amount of complexity. When you first start writing code, you are not writing very complex applications. Hence, if you are just starting to write some code, you will not see the need for creating objects yet.

However, there are some real good reasons to consider using class objects. One great reason is you can have more than one instance of the object. This is really critical for certain kinds of user interface. I have a tour booking system, and I created a tour booking object. If you take look at the following screen, you can see that I have an active tour on the left side, and also have an active tour on the right side.


The above screen allows you to move people from one tour booking to another. On the left side is one tour booking, and on the right side is another. Each tour object has a zillion things like

  • The hotel name.

  • The type of rooms at his hotel.

  • The room rates for the particular season etc.

In the above example we are actually moving people from one room in a tour to another room. In the above example the tour on the left side is the same as the one on the right side, but it does not have to be.

This cute simple screen is VERY complex. When I want to move that person, tons of things like room pricing has to be re-calculated for that ONE person. That pricing comes from a complex seasonal pricing table. I have to check for bus availability (the person likely has to be moved from one bus to another, and this is done automatically). However, before moving to that bus, availability of that bus must be checked. So, the problem becomes that I need two sets of a very complex system in memory (the tour on the left side, and the tour on the right side).

The point here is that for each tour, there is a TON of things need to have in memory:

  • How many rooms available.
  • How many tour buses available.
  • How many seats on each tour bus.
  • Name of the tour bus company.
  • How many corporate bookings are holding seats..but not yet used.

The above list gets quite long. Each of the above "how many" questions is fact a bunch of code and queues to calculate the results. Note that if my design has STORED the above results, then probably I could get away NOT using a class object. However, EVERY single one of the above values requires some code to calculate. So, I don't store how many rooms are available anywhere in the database. I don't store how many seats are available where. All of these values are the result of on the fly calculations. So, it becomes really hard to write code. When we move a person from one booking to another, all of the above information will have to be dealt with (I need it at my finger tips while writing code). Further, if we need to display and have two bookings on the screen at the same time, that amount of information becomes VERY difficult when you now have to deal with two copies. Using class objects made the whole thing quite easy.

Lets try a bit of regular code here to ask a few questions about our tour. Lets assume we have a booking record for a customer. Ok, lets display the Tour name, and the hotel a user is booked into. Lets just assume that the user is booked to tour in the tours table (the tour table will define things like the hotel etc). Heck, lets just say the tour ID is 222. (this number is just the simple internal auto number for that tour record).

To get the hotel name for this booking, I would have to do the following.

dim lngTourId as long
dim rstTour as recordset
dim rstHotel as recordset
lngTourId = 222

set rstTour = currentdb.openrecordset("select * from tblTours where tourid = " & lngTourId)

Ok...so now we have the tour, lets display the tour name

msgbox "tour name = " & rstTour!TourName

Ok, now display the hotel name. The hotel name of course is simply a hotelID in the tour.

lngHotelId = rstTour!Hotel

set rstHotel = currentdb.openrecordset("select * from tblHotels where hotelID = " & lngHotelId)

msgbox "hotel name = " & rstHotel!HotelName

You can see what pain it is to just get the tour name, and the hotel name. We have to deal with two related tables, build two queries, declare two recordsets. Man, what a pain! We could perhaps use the dlookup function above to reduce some code, but even dlookup becomes a real hassle here. We also could use a query that is a relational join to reduce the above code. However, there is a lot of additional things we need when working with the tour.

Now extend the above to include the room type description (a another table), the room rates (another table, and complex looking to a seasonal pricing system). Grab the bus? Heck...this is becoming a night mare to simply display a few things about the users booking. And, do you want to try and work with two of these tours in memory at the same time?

Now, lets do all of the above with a class object.

These properties of the object are actual ones that I use:

' declare an instance of our tour object.

dim myTour as New clsRidesTour

myTour.TourId = lngTourId

' at this point, I can retrieve, and ask virtually anything I want about his tour. So.

myTour.TourName returns the name of the tour

myTour.HotelName returns the name of the hotel

myTour.HotelSpace returns the space allocated in the hotel

myTour.HotelRooms returns the number of rooms in this tour

myTour.HotelRoomsUsed returns the number of rooms used

I will stop at this point, but to get the above simple answers with regular coding methods..it will requite at lest 50 lines or more of code? Yikes!! Is not the above incredibly easy? Hey, lets get the space remaining in the tour:


You could just write a bunch of regular subroutines for each of the above questions, but you can see that many of the questions like TourName, hotel name etc will requite you to load a SET OF related records.

How nice and easy it is to get the hotel space remaining. Note only that, but inteli-sense works for all of the above..so I don't even have to remember all the names of subroutines/code as I type!

And to get number of people on the bus for this tour..?



Here is the actual class code for above example in the class object for InBusTotal:

Public Property Get InBusTotal() As Integer

   InBusTotal = 0

   If m_rstBusList.RecordCount > 0 Then


      Do While m_rstBusList.EOF = False

         InBusTotal = InBusTotal + Nz(m_rstBusList!InBus)



   End If

End Property

Now, if you look at the above property of the object, you will notice that a recordset m_rstBusList is already loaded. In other words, when I create and set the myTour = "tour id", then all of the recordsets for bus company, hotels, rates etc gets loaded into the object. Hence, the above code as a result is MUCH BETTER then just writing a stand alone subroutine called "in bus total". Since, a stand alone routine would have to still LOAD up a very complex query into the recordset. I DON'T have to do that since the class object is a collection of those reocrdsets and routines to give me the "things" I need to know about a tour. The more you add code to the class object...the more it simplifies the application. When you don't use a object the more code you write the worse your application will get! (ask your self, where would I put those recordsets? and how would I SHARE THEM between each routine? I can't use global vars, since then I would be restricted to working with ONE TOUR in memory at a time).

Anyway...you can see that the whole thing just becomes such a pain to code simple questions about a tour. Create a tour object, and virtually anything you need about that tour is ready made at your finger tips.

Another good read in ms-access for class objects in ms-access can be found at


Albert D. Kallal
Edmonton, Alberta Canada