Results 1 to 8 of 8
  1. #1
    Star Lounger
    Join Date
    Jun 2010
    Location
    Lichfield, UK
    Posts
    56
    Thanks
    10
    Thanked 0 Times in 0 Posts

    Calling forms from within classes

    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

  2. #2
    Star Lounger
    Join Date
    Mar 2010
    Location
    Winston-Salem, NC USA
    Posts
    52
    Thanks
    11
    Thanked 3 Times in 3 Posts
    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.

    Last edited by Bender; 2012-05-17 at 08:38. Reason: Clarification
    Do your part!!! Report SPAM to http://www.spamcop.net/

  3. The Following User Says Thank You to Bender For This Useful Post:

    njl100 (2012-05-17)

  4. #3
    Star Lounger
    Join Date
    Jun 2010
    Location
    Lichfield, UK
    Posts
    56
    Thanks
    10
    Thanked 0 Times in 0 Posts
    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.

  5. #4
    Star Lounger
    Join Date
    Mar 2010
    Location
    Winston-Salem, NC USA
    Posts
    52
    Thanks
    11
    Thanked 3 Times in 3 Posts
    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()
    Dim cMatches As classDbMatchList
    Set cMatches = New classDbMatchList
    End Sub

    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.
    Do your part!!! Report SPAM to http://www.spamcop.net/

  6. #5
    Star Lounger
    Join Date
    Jun 2010
    Location
    Lichfield, UK
    Posts
    56
    Thanks
    10
    Thanked 0 Times in 0 Posts
    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.

  7. #6
    Star Lounger
    Join Date
    Mar 2010
    Location
    Winston-Salem, NC USA
    Posts
    52
    Thanks
    11
    Thanked 3 Times in 3 Posts
    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
    Last edited by Bender; 2012-05-17 at 16:00. Reason: code fix
    Do your part!!! Report SPAM to http://www.spamcop.net/

  8. #7
    Star Lounger
    Join Date
    Jun 2010
    Location
    Lichfield, UK
    Posts
    56
    Thanks
    10
    Thanked 0 Times in 0 Posts
    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.
    Attached Files Attached Files

  9. #8
    Star Lounger
    Join Date
    Mar 2010
    Location
    Winston-Salem, NC USA
    Posts
    52
    Thanks
    11
    Thanked 3 Times in 3 Posts
    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.
    Do your part!!! Report SPAM to http://www.spamcop.net/

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •