Calling forms from within classes

Topic

New Reply

I have an Access 2007 app which uses lots of VBA. I am trying to improve it by making it more object based but am struggling conceptually to understand how to do something. I am still trying to get my head around the vba object model and how to develop in an object based way so please be gentle with me 🙂

I have created a class module called classPerson. In there I have identified key properties eg ID, Name, DOB etc. I have also created several methods eg ReadPerson (from ID provided and then set properties from recordset read), WritePerson, LookupPerson (from name or partial name provided).

My problem is that one of my forms (Load Form) reads data from a spreadsheet. Some of that data identifies people from their name so I invoke the LookupPerson method. In 95% of times this works fine – it gets a match on 1 record so LookupPerson finds an ID which is passed to ReadPerson and I am ok to proceed. However there are some occasions where the name passed to LookupPerson is not unique. I have the logic to handle this – I have a separate form (frmSelectAPerson) which lists all of the records it has a match on and allows the user to select the correct one.

I think frmSelectAPerson should be included in classPerson as this is functionality that could be reused elsewhere in the application. So I have another method called SelectAPerson which is called from LookupPerson if >1 person is matched. So far so good. But how do I invoke frmSelectAPerson from method SelectAPerson within the scope of classPerson?

I’ve tried the following:

1. In method SelectAPerson I use a DoCmd.OpenForm to open form frmSelectAPerson. I think I can get this to work but in a way that I think breaks the rules of object based design because I am going outside of the object to execute one of the objects methods. Any references to the properties, variables, methods in classPerson have to explicitly reference the class (which they would not have to do if it were within the class). It also means that I have to declare this instance of the class globally which to me seems counter to the principles of object based design – I only need to use this instance of the class in the Load Form.

2. I’ve declared a form object in classPerson:

Dim frmSelect As Form_frmSelectAPerson

Then in the SelectAPerson method I use

Set frmSelect = New Form_frmSelectAPerson

When I try to Open frmSelectAPerson I do not get the Open method as an option but I do seem able to use SetFocus (so maybe the Set command opens the form). Even so I cannot reference variables within classPerson from within frmSelectAPerson without explicitly referencing the class (as in 1 above). I can overcome this in this instance by setting the controls in frmSelectAPerson from the SelectAPerson method before I set focus but am wondering is this the best way to do it? Also how do I return a value from frmSelectAPerson to method SelectAPerson (in this instance it will be the value selected from a list box, the Person ID). I can think of 3 ways but again, am wondering if any of these are the recommended ways:
a) set a global variable
b) set the forms Tag property (does it persist after the form closes??)
c) assign the value to a variable in the Person class eg PERSON.SelectedID = 999 but again this raises the same point I’ve already made above.

Can someone suggest how I get round the problem of trying to call a form from within a class module or if I’m trying to solve this problem in the wrong way please can you put me on the right track.

Many thanks

Reply | Quote

Replies

I know everyone has their own programming techniques and style but I try to keep classes as unique and independant objects that don’t know anything about the outside world. So if I were trying to tackle the problem as you’ve described I would pass a reference of classPerson to frmSelectAPerson instead of making classPerson know about the form. What if you had other forms that needed to use the methods and properties of classPerson? If you started loading up classPerson with references to all these forms then things would get messy very quickly and classPerson is no longer a “true” object and in the future it would not be reusable in other projects without modification. See my point?

So if you need to use classPerson briefly in a form, here’s what I would do.

In the form’s declaration section, declare a variable of the classPerson type.

Option Explicit

Private m_clsPerson As classPerson

’Create a public property in the form. Remember a form is also a class.
Public Property Set clsPerson(ByRef clsPerson As classPerson)
Set m_clsPerson = clsPerson
End Property

’Use the local instance of the class to perform whatever function is required.
Private Sub Command1_Click()
If m_clsPerson.FindPerson(“Joe Blow”) = True Then
‘Go do some stuff
End If
End Sub

’Be sure to clean up when the form unloads.
Private Sub Form_Unload(Cancel As Integer)
Set m_clsPerson = Nothing
End Sub

So, after you invoke the SelectAPerson form, use the public property of the form to set a reference to your main person class object. Use the local reference to perform the required functions, then you’re done. Your main copy of classPerson should reflect any property changes that were invoked by frmSelectAPerson and you haven’t had to hack up classPerson to accomodate an outside object.

I hope this helps.
[/COLOR]

Reply | Quote

Thanks Bender,

It was creating a new Property for the form that was the missing link for me. Once you’d explained that I seemed to get it. Fortuitously I was working on another area of the application that needs this sort of solution when the notification of your reply arrived so I’ve tried implementing it but I cannot quite get it working. When I try to call a method in the class whose reference is passed to the form I get an error 91 : Object Variable or With block variable not set. I cannot see what I’m doing wrong.

The scenario here is a little different but I think the same concept. I have a form (frmPlayerProfile) showing various stats for a player. On the form is a button, if you click that button it opens another form (frmMatchList) which contains a list box with details of all the matches that player has played in. I have a class called classDbMatches which handles all the database interfaces for queries about matches. An instance of classDbMatches has already been set up in frmPlayerProfile, so when the button is clicked I set the properties in the instance of classDbMatches that are necessary for the query (ie PlayerID, other filter info). I then invoke frmMatchList but in the Form_Open sub I get the error referred to above.

My code is below:

Private Sub cmdMatches_Click()

Dim cMatches As classDbMatchList
Dim frmMatchList As Form_frmMatchList

Set cMatches = New classDbMatchList
Set frmMatchList = New Form_frmMatchList

cMatches.FilterOnDate = False
cMatches.PlayerID = cboPlayer
Set frmMatchList.Matches = cMatches
frmMatchList.SetFocus

End Sub

Then in frmMatchList:

Option Compare Database
Option Explicit

Private cMyMatchList As classDbMatchList

Public Property Set Matches(ByRef Matches As classDbMatchList)
cMyMatchList = Matches
End Property

And finally when frmMatchList is opened:

Private Sub Form_Open(Cancel As Integer)

….do other stuff then

lstMatches.RowSource = cMyMatchList.ListMatches(False)
lstMatches.Requery

I get the Error 91 on the line that calls the ListMatches() method (It should return the SQL necessary to populate the listbox).

I think I’ve followed your suggestion but just cannot get it working. Any ideas?

Thanks.

Reply | Quote

The error you see is quite common when an object has not been referenced properly. Here’s what I think is happening; You are declaring your class in a command button click event;

Private Sub cmdMatches_Click()
[INDENT]Dim cMatches As classDbMatchList[/INDENT]
[INDENT]Set cMatches = New classDbMatchList
[/INDENT]
End Sub[INDENT]
[/INDENT]
When you do that then the scope (life) of that object is only as long as the click event itself. So by the time the other form gets the reference and loaded itself, the original instance of the class has been destroyed (when the click event ended). Try declaring the class as a form level variable, then you can instantiate it via the button click. Let me know if that works.

Reply | Quote

I’ve moved both the class declarations (cMatches and frmMatchList) to form level and tried both Dim and Public. I’ve also tried instantiating cMatches when frmPlayerProfile opens rather than in the click event.

What happens now is that error 91 is triggered by the

Set frmMatchList.Matches = cMatches statement in the Click event. Stepping into the code the error is actually generated by executing

cMyMatchList = Matches in frmMatchList.

Reply | Quote

I created a test application and this runs fine. Make sure that you use a ‘Set’ command when you… Set fMatchList.DBMatchList = cMatches and make sure the form has been loaded or created.

*** frmMain ***
Option Explicit
Private cMatches As clsDBMatchList
Private fMatchList As New frmMatchList
Private Sub cmdMatches_Click()
Set cMatches = New clsDBMatchList

’Set a property
cMatches.FilterOnDate = True

Set fMatchList.DBMatchList = cMatches

fMatchList.Show
End Sub

*** frmMatchList ***

Option Explicit
Private cDBMatchList As clsDBMatchList
Public Property Set DBMatchList(Value As clsDBMatchList)
Set cDBMatchList = Value
End Property

*** clsDBMatchList ***

Option Explicit
Private m_blnFilterOnDate As Boolean
Public Property Get FilterOnDate() As Boolean
FilterOnDate = m_blnFilterOnDate
End Property
Public Property Let FilterOnDate(ByVal Value As Boolean)
m_blnFilterOnDate = Value
End Property

Reply | Quote

Thanks for that Bender. I created the test app also.

The code you provided wouldn’t compile on my machine for 2 reasons.

In frmMain the declaration statement “Private fMatchList As New frmMatchList” wouldn’t compile as it did not recognise “frmMatchList”. I had to change it to “Form_frmMatchList”

Secondly I then do not have the .Show method available so I used .SetFocus instead.

Something tells me the Form object in your environment has different capabilities to that in mine.

I also added another property to clsDBMatchList called MyText to test that the form can see it. It compiled ok but as before when I try to reference the class from within frmMatchList by writing its MyText property to a text box in the form I get error 91.

I think I must be using a different version/environment than you as mine treat forms differently. I’m using Access 2007 on XP SP3. In the VBA IDE I have the following References ticked:

[*]VBA
[*]MS Access 12.0 Object Library
[*]OLE Automation
[*]MS Office 12 Access database engine object library

I’ve attached my version of the test app fyi. Any ideas gratefully received.

Many thanks for you help on this.

Reply | Quote

Sorry about that. I was using VB6 to generate the examples and I also noticed a difference since I don’t have a Form_Open event in my programming environment. I will open your code in Access and bat it around a bit to see if we can make sense of this problem. Unfortunately I will be travelling most of this day so it will tomorrow before Ican take a look at it. Sorry for the delay.

Reply | Quote