Creating and Initialising Custom Class/Object

[beginner83]

Hi All,

I've been playing around with creating my own class in VBA but I'm having problems calling its methods. My class is pretty basic, its called cDentist and the properties are just Name, Address, DOB etc and one method AddDentist. AddDentist will add the details to a sql server table.

So I create an instance of the class in a module called Dentist. At the very top of the code i put..

Option Compare Database

Global Dentist As cDentist

I have a method then in module Dentist, where I initialise the instance of class cDentist and i populate it's properities...

Sub RecordDentistDetails()

Set Dentist = New cDentist

Dentist.Name = Forms!frm_enterdetails!txtName
Dentist.Address = Forms!frm_enterdetails!txtAddress
Dentist.dob = Forms!frm_enterdetails!txtdob

End Sub

My problem comes then in a form where the user enters the dentist details and clicks 'Save' button. Code below is on 'Save' button..

RecordDentistDetails

Dentist.AddDentist

When I click 'Save' I get error, Method or data member not found. And '.AddDentist' is highlighted. I'm a little confused why it's not seeing AddDentist as a method of class cDentist.

Any advice is greatly appreciated

 

[Galaxiom]

Is AddDentist a Public function in cDentist?

 

[pr2-eugin]

Hello beginner83, Welcome to AWF..

When you type Dentist. does it come up with the list of methods available in the Class? Specially AddDentist? If it does not see if you have spelt it correctly, see the Declaration of the method.. See if it is set to Public..

I think the Keyword to declare Global variable is Public..

Option Compare Database
[B]Public[/B] Dentist As cDentist

EDIT: Forgot to Click 'Post Reply', by the time I did you beat me to it Galxiom..

 

[Galaxiom]

I think the Keyword to declare Global variable is Public..

Either Public or Global work. Public is preferred by Microsoft but who are they to tell us what to do?

Some developers like to use Global in Standard Modules and Public in Classes because that does reflect the scope of the variable.

 

[beginner83]

Thanks for your posts.

Galaxiom...Yip it is a public function in class cDentists

Public Function AddDentist() As Integer

pr2-eugin...when i type 'dentist.' in the Dentist module I get the list of methods and properties I created in cDentist (AddDentist, name, address, dob etc) but when I do the same on the code behind the Save button on the main form I don't get those options. Instead the options are Application, Caption, Controls, ControlTipText etc.

I have tried changing the keyword from Global to Public and nothing changes.

 

[pr2-eugin]

Either Public or Global work. Public is preferred by Microsoft but who are they to tell us what to do?

Some developers like to use Global in Standard Modules and Public in Classes because that does reflect the scope of the variable.

Ha ha.. Did not know that, its good to learn something new everyday..

 

[pr2-eugin]

Okay instead of the function inside the Mod to init the variables, why not do it like this.. see if this works..

Set Dentist = New cDentist

Dentist.Name = Forms!frm_enterdetails!txtName
Dentist.Address = Forms!frm_enterdetails!txtAddress
Dentist.dob = Forms!frm_enterdetails!txtdob

Dentist.AddDentist

 

[beginner83]

I was originally going to do that but my function AddDentist returns a value, the ID of the dentist in the sql server table. So I had...

MainForm (Form)..

Private Sub btn_Save_Click()

Dim intDenID As Integer
RecordDentistDetails

intDenID = Dentist.AddDentist

End Sub

Dentist (Module)..

Option Compare Database

Public Dentist As cDentist

Sub RecordDentistDetails()

Set Dentist = New cDentist

Dentist.Name = Forms!frm_enterdetails!txtName
Dentist.Address = Forms!frm_enterdetails!txtAddress
Dentist.dob = Forms!frm_enterdetails!txtdob


End Sub

So if I put cDentist.AddDentist into RecordDentistDetails method, I could declare the variable that stores the result as public and be able to use it, right? Would that be a good way to go about it?

Option Compare Database

Public Dentist As cDentist
Public intDenID as integer

Sub RecordDentistDetails()

Set Dentist = New cDentist

Dentist.Name = Forms!frm_enterdetails!txtName
Dentist.Address = Forms!frm_enterdetails!txtAddress
Dentist.dob = Forms!frm_enterdetails!txtdob

intDenID = Dentist.AddDentist

End Sub

 

[pr2-eugin]

In theory it should work, the only way is to test it..

 

[beginner83]

That's it working

Thanks!!!!!!

 

[pr2-eugin]

Glad to hear you have it up and running now.. Good Luck..

 

[Galaxiom]

Typically classes are designed to be self contained. As such the AddDentist method would not refer to the form at all because this would mean the class only worked with that form.

Instead the function would have arguments for Name, Address and DOB. The code behind the button would let the AddDentist arguments to the values of the form controls and call the method.

Moreover the Dentist would usually be added as an Item in a Dentists Collection. The Add method of the collection would check for any matching dentists stored in the database, save the new details to the table if necessary and also load it to the collection in memory. The Index of the collection would usually be the ID from the table.

One of the points of this construction is to store the information about the dentists locally so that the database does not need to be queried again for those already loaded in the collection. This can be important in a database with a lot of users.

You are only getting half the benefit by having the Dentist class just write the records to the database and then get overwritten by another dentist's details.

What you are getting, if properly designed as I outlined in my comment about the function arguments, is encapsulation the saving process. Separation of the database interaction from the forms is a good practice in unbound form design.

 

[pr2-eugin]

I have not myself used Classes in my application (YET !!), but I do understand what you are coming to say Galaxiom.. It sure is a good piece of advice.. As the general use of Classes and Objects is the Reusibility and Encapsulation.. Which makes more sense to pass values as Arguments..

 

[beginner83]

Galaxiom, I'm getting a little confused, I'm still pretty new to this.

Typically classes are designed to be self contained. As such the AddDentist method would not refer to the form at all because this would mean the class only worked with that form.

What do you mean about the AddDentist method not referring to the form? I was trying to call the AddDentist method when a button was clicked on the form. Is this not a good way to do it?

Instead the function would have arguments for Name, Address and DOB. The code behind the button would let the AddDentist arguments to the values of the form controls and call the method.

I don't pass arguments to the method as the object was initialised and the properties of the object, name, address, dob were populated. And so from that instance of the class I wanted to call AddDentist.

My Dentist class...

Private dentist_Name As String
Private dentist_Address As String
Private dentist_DOB As String


Public Property Get Name() As String
    Name= dentist_Name
End Property

Public Property Let Name(value As String)
    dentist_Name = value
End Property

Public Property Get Address() As String
    Address = dentist_Address
End Property

Public Property Let Address(value As String)
    dentist_Address = value
End Property

Public Property Get DOB() As String
    DOB= dentist_DOB
End Property

Public Property Let DOB(value As String)
    dentist_DOB = value
End Property


'********    METHODS    ***********'

Public Function AddDentist() As Integer
...
...
End Function

So I set the properties here..

Dentist.Name = Forms!frm_enterdetails!txtName
Dentist.Address = Forms!frm_enterdetails!txtAddress
Dentist.dob = Forms!frm_enterdetails!txtdob

Even though I do this should i still be passing them as argument to the method AddDentist?

I don't know anything about Collections, something I will definitely read up on.

Thanks

 

[Galaxiom]

Custom classes are awesome objects. They are not as complex as they initially seem and I would encourage all developers to explore what they can offer to your designs. Make sure you have a good understanding of private collections before you launch into writing a class or you won't realise the full potential.

Aside from portability and replicability the idea of a class is to present a simple interface to an application. My favourite class is one I developed to load and parse text files for display in an Access form. It is actually a good example of using a class.

When the instance is initialized it sets up collection for fabricated ADO Recordsets where the data is made available to the front end. As each text file is requested the class determines if its data is already in the collection and loads it straight to the form's recordset if available.

Otherwise it consults the database of third party document indexing system to determine the file required. Information about parsing the data is held in yet another database. This is read when a document type is first loaded and also held in a collection where it is used when required.

This saves a lot of time because parsing is relatively slow. Once the data from the text file is held in the ADO recordset it is virtually instantly available because it is held in RAM. Moreover any further requests are not loaded to the network file server.

Even the various ADO connections are held in a collection. (Well to be honest the "collections" I have been speaking of are actually Dictionary objects from the Scripting library but for some reason not included in VBA. These are superior to collections in that they have searchable indexes but are otherwise very similar.)

The class provides a simple interface to the display form where a handful of public functions (ie Methods of the object) control a deep complexity. For example the LoadDocument method is simply provided with the ID of the document and a complex sequence is initiated. Underneath, connections are checked for their existence and reestablished if required.

There is a Private function to manage the creation of a connection but the application never needs to consider it. The instance of the class simply lives inside the form's module and provides it with extremely accessible massively increased power to handle data.

A class like that can be dropped straight into another project or can be handed to a far less experienced developer who never really needs to understand the code inside.

 

[Galaxiom]

Your original idea of the AddDenist method was on the money.

Instead of poking the data in piece by piece as you are now the AddDentist method does it in one line using a public function. Moreover, inside the class, code would be used to validate that new record, something you could not really do so neatly if the values were passed in one at a time.

Chip Person's site has excellent introductions to building classes. His stuff on Collections is also very good.

It is all under Excel but applied equally to Access.