Access OOP examples wanted

OOP and Access

I am looking for some examples.

What I found on the web so far stops just short of the interesting bits.

The first bits

1. Create item
2. Edit Item
3. Delete Item

are fathomable from what is out there. But, that is not enough. How does one operate on more than one?

Traditionally I could make a loop over records and do what ever needed to be done. How is that done with objects. Using collections? THink of a list of contacts, where you double click on one to edit it. How is that done?

4. Get list(collection) of objects with specific criteria'
5. Operate on a list/collection

When you create an object you can add it to a collection. Surely, that task is automated, but how?

Also, when operating on more than one object, a list or entire collection of them, how do you deal with the recordset opening for each and one... or else how is it done?

I have suggested in Site suggestions that there should be a corner on this site for this type of questions or examples. if you have an opinion on that please add to the thread mentioned.


(And finally, I have just dumped Firefox because the stupid thing yet again wiped out the previous version of this posting while I was working on it, by this silly newfangled "The connection was reset" bl.. unhelpful browser window wiping message!
)

spike

Search this forum (Advanced search)
keyword class
user Chriso

other links:
http://www.cpearson.com/excel/classes.aspx
http://www.techrepublic.com/article/build-your-skills-using-class-modules-in-an-access-database-solution/

http://sourcedaddy.com/ms-access/object-oriented-programming-with-vba.html

Example:
Jim Demarco - Classes for the Masses

update 10 April 2016: After talking with Markk on a slightly different topic, I rediscovered this thread. I found that the above link is no longer working. I found a copy of the article and have attached it to this thread. I hope it is useful.



Worth reading http://www.experts-exchange.com/Database/MS_Access/Q_28347394.html
http://bytes.com/topic/access/answers/946968-what-benefits-class-modules-over-plain-modules

Interesting article:
http://www.access-programmers.co.uk/forums/attachment.php?attachmentid=38532&d=1314280807

In the hope of helping spikepl maintain interest in OOP discussion, I've created a very simple game (Galaxy Buccaneers - a very very basic RPG game) in Access using OOP (as best VBA allows and my experience permits). Although it's a game, the OOP principles could be used to develop more business like apps e.g. managing a lorry fleet, managing product forecasting, managing bookings.

Some key points that might help folks who are trying to get their heads around OOP in conjunction with Access:

The form acts only as an interface for entering input and displaying output. I does not have any notion of the game other than the creation of a game object. It doesn't know anything about the other class object e.g. from clsCharacter or clsGroup. As such there are only a couple of outputs from clsGame. They are an array from the status method and a string from a couple of the other methods. These are used purely to update the interface form.

The game class clsGame does all the management of the gameplay. It does not get involved in the interface. This approach of creating an object to act as the "engine is typical of OOP design because the "engine" can be ported to other interfaces and remains interface independent. This would be much more difficult if the code was implemented in the form.

The game hopefully demonstrates some of the questions spikepl was enquiring about:
- creating an object
- adding an object to a collection
- editing an object
- getting a list of object (not with criteria but this follows on from the iterative nature of collections)
- operating on objects in a collection
- loading and saving to a table

Of course this game could be implemented as a single class or even all in the form. And the code would probably look simpler. Imho OOP comes into its own the more complex the project gets. Furthermore, OOP tends to lend itself to loading data into memory. As such, the coder can easily interact between object instances. Compare this to interacting between different records in a table where this becomes more tedious e.g. having continually search and update through recordsets at best.

I have tried to design the objects to only expose properties that are required externally to the class. An example of this is that clsCharacter has a private property of mHealth. We can "get" the value of mHealth publicly through the Get health property. But we can't set it because there is no "let" property. I designed it this way because health only changes as a result of successfully attacking a character through the attack method of clsCharacter. Another example is clsGroup. The groups could easily have been implemented as collections in clsGame. However, by creating a dedicated class, I have full control over the exposure of the groups properties and methods. An example is the aliveCount function which tells us how many members of the group were still alive. As a class function it becomes far more natural to use that a function in clsGame.

There are plenty of holes in this example. Feel free to criticise or fill them in as I am barely beyond novice at OOP.

Chris

Wauw - thanks a lot.

I'll digest this over the next few days and see if I can work out some of the questions popping up in my head.

@jdraw thanks for the links

Cool thread.

Trying to keep the thread going, here’s another OOP example.

I’ve seen quite a few people asking how to display normalised data in a more datagrid (spreadsheet) like format. So here’s one. The grid has the following features:
• Customisable
• Load from normalised data source into a grid
• Edit the data
• (auto) save the grid back to the normalised table.
• Scrollable
• Titles are fully synchronised with scrolling
• Handles different data types for row and column titles (date, string or integer). At the moment only handles integer data for the cells.

The form can be customised to some extent:
• Number of column and rows
• Row and column heights and grid positioning
• The datasource
The scope for customising is huge due to the cellular nature of the grid and the way the “cells” are address e.g. shade cells >100, highlight cells, sum highlighted cells etc

The design uses three classes. The first is clsData. This is similar to my previous example i.e. it handles the data loading/saving and storage in memory. Data is stored in memory because the actual underlying data is larger than the grid can display – hence the ability to easily scroll.

The second class is clsGrid. This handles all aspects of the grid controls e.g. positioning, scrolling, updating from the clsData object. Note that there are a load of invisible text boxes with tag “gridTextBox” with no specific position. It uses as many of these as needed to build the grid – beware there’s no error checking to see if there enough boxes though. The boxes are not named in any way as this is handles by the third class. All this could have been done in the form but doing it in a class makes it tidier and importantly ensures we only expose the properties necessary and not all properties.

The third class is clsTxtBoxEventHandler. This came about because I didn’t want to write methods for every box and worry about the positioning of the boxes. It's quite easy to point lots of events to a single routine but harder to pick up the reference to the object that called the event. So I used a technique based on this. I used an array instead of a collection for obvious reasons of ease or referencing. Each box in the form grid is reference by a corresponding clsTxtBoxEventHandler object. These are stored in an array mGrid(,) in the main grid object. By doing this the main grid object can address each text box via the array and does not need to know the name of the text box. So by doing this, we can write a single After Update procedure in clsTxtBoxEventHandler that will work for all text boxes where we create a corresponding object. Since we have a class we can add other event methods e.g. keypress. An offshoot of this approach is we can also address the textbox by referencing it through the array rather than trying to figure out how to reference it directly. Building a neat grid is pretty simple with some loops and property setting. From this perspective it is very easy to add more properties to play with.

Anyway, hope makes sense and that its useful for both OOP interest but also as a grid editor. I'd be interested to hear how it performs on some pcs.

I can't guarantee there are no errors and there's also some holes that still need filling. Also devoid of all error handling

Chris

Edit (19th Oct):
- tidied up the code and filled in some holes, fixed some bugs
- grid now deals with text data types as the value data (see three examples)
Edit (13th September 2016)
- switched the scrolling buttons round so they are right way round !
- added some more comments

Another example/article
http://www.kallal.ca/Articles/WhyClass.html

Thanks folks - this is getting really interesting, and of course I got swamped by uninteresting things, so my progress in this matter has been of quantum-leap size!

I'll get there ... it just takes time.

Just to let you know this is of great interest to me as well. I have been invisibly subscribed to this thread from the start. I am just making my interests public knowledge.

Here's a database that snaps forms together if you drag-and-drop them near enough. I wrote this a while ago and submitted it here for a sample database, but I can't find it anywhere.

One cool thing with classes is you can sink events that are raised in other classes, so in this example the cSnapForm object--which is a "wrapper" class--declares a WithEvents Access.Form variable, and sinks a few of that object's events, including Activate, Timer, and a couple of others.

Just a quick hit-and-run:

Guys guys, please, don't go crazy on super-advanced stuff: you are throwing margaritas ante porcos!

I would be quite happy with basics to start off with , as in plain vanilla data processing.

Think of an address book for starters, where classes could come handy for such mundane tasks as access control, or setting up outlook, or setting up, storing and reading personalized configuration of the application, and perhaps also for binding the entire list to a form (or not) and then editing single records in another form (or perhaps not).

This other stuff is great as appetiser, but we need to be able to creep before we can crawl before we can walk, rather than get lost in some admittedly very very fancy features

Here's a data access pattern I almost always use. At the heart of it all is the cDataRecord class, which might look a little complicated, but all it really does is provide a generic disconnected ADODB.Recordset of data from one record in a table, corresponding to the instance of the class that consumes it. So if you look at cCustomer, it has a
. . . to which it supplies a complete SQL statement that returns a single row from the tCustomer table. That is the data for one instance of cCustomer. So in this case cCustomer is sometimes called a data transfer class.

But where it gets cool is the cCustomer class also exposes . . .
. . . which embodies a collection of cOrder objects, and if you look at cOrders, it takes a cCustomer object in it's load method, and from that creates a collection of cOrder objects that belong to that customer, and each cOrder also uses cDataRecord for its data access.

But anyway, poke around and see what you think. It's a lot of overhead, but once it's built all your custom classes have intellisense, so you don't need to remember anything about field names and so on, so I find scaling this up is really easy two years from now.

Also, if you set breakpoints to check out how the code runs, make sure you have the locals windows open, since all custom Properties of a class are evaluated and displayed there, even other classes!!! So if you set a breakpoint in the Orders property of the cCustomer class, then the root of the locals window tree, Me, is a cCustomer, and then you can drill down into the Orders class, and see each order it contains, each of which supports an instance of cDataRecord, which has an ADODB.Recordset with a Fields collection of your data from the table. So the Locals Window provides this rich tree of live relationships between all your objects, and combine that with the Call Stack, you can minutely dissect the operation of your OOP code.

And that's not even all. In that locals tree, note that a cOrder object has a DivisionByZero property which yields an error if you call it. In the locals tree any property that results in an error, shows the error description, in this case . . .
. . . so this becomes a very rich debugging tool, where you can navigate the tree view of your custom class data structures and see what data might result in errors in what objects and when, all at a glance.

Sorry spike, if this is too much to consume all at once. Maybe just nibble on the cool bits.

spikepl said:

I would be quite happy with basics to start off with , as in plain vanilla data processing.

Think of an address book for starters, where classes could come handy for such mundane tasks as access control, or setting up outlook, or setting up, storing and reading personalized configuration of the application, and perhaps also for binding the entire list to a form (or not) and then editing single records in another form (or perhaps not).

Click to expand...

I think the only way to get your head round OOP is to get your hands dirty. The thing is, looking at various examples will give you show you how different developers have chosen to implement their classes. It's only when you start developing that you start to ask yourself what your classes will represent and what public/private properties and methods you will need.

Do you have a specific application in mind. If you have a form design and and one or more tables of data maybe we can help you through the design using OOP.

To anyone who hasn't yet created a class, I'd recommend creating a simple class e.g. one that does some simple calculation based on some inputs. I guarantee you'll get a warm feeling when you first start to use your objects and intellisense kicks in.

Chris

Hi Guys,
Here is another link re Class modules

Just to let you know that this subject is by no means forgotten - I simply got swamped by other things

I responded to a request by another poster and used a class module example in the response.
It is located here .

There is a simple Image control class in this post (another forum).
Shows a simple way to add multiple image controls onto a form and be able to identify each.

Thank you to all contributing here, I recently started a java OOP course and had NO idea I could do all these cool things in Access too...!

Some questions however if I may..

Is it more efficient to base forms for data entry around classes that we can write the values of to the table with a function once the data's validated?

Would it be considered better to use 'normal' vba for the simpler things and class objects for things that may be more complicated that we will need to re-use later down the line?

Sorry if the above don't make much sense uber tired but can't stop reading and playing around with all this stuff, thanks again to you guys so far!!

A couple of people have suggested I post this here, so HERE are a couple classes that generate pop-up forms displaying progress meters.

FYI

I found 2 youtube videos by WiseOwlTutorials -- they are under an Excel banner -- but they deal with vba. Best I have found as of 25April2016
Class Modules https://www.youtube.com/watch?v=MjbmsVDnAL0
Using Class modules https://www.youtube.com/watch?v=bBAdhclCT9g