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