Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Vienna, Wien, Austria
    Posts
    5,009
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Class Module (VBA (Access))

    I have been trying to decipher an old (late 1999) article by Ken Getz Getting WithEvents apparently excerpted from Access 2000 Developer's Handbook Volume I (Desktop) - without, however, the relevant code download.

    The article discusses "sinking" form events in a Class Module. From what is said, it would appear that, by using a Property Set statement, you can "sink" all desired default Events into a Class Module in bulk. (This would be like one can customise default Events in Word.) Either I have not been able to interpret what is said correctly or that is not possible in Access.

    What I have been able to get to work is the method described in Beginning Access 2002 VBA. Here one has to use a Function in the Class Module (called "init" in the example). The practical drawback is that the Function requires an argument for every single (potential) Control to which it applies in the Form. This is rather messy, but then so can Access be at times. The unused references may perhaps also affect performance.

    Is the "second" method, in fact, the only practical method, or have I missed divining a crucial step in using Property Set (the Ken Getz method). I do realise that Property Set does apply to custom Events. The web page article is attached as a TXT file (excluding images). Change the extension to read it in your browser.
    Gre

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Class Module (VBA (Access))

    They're actually talking about the same thing, Unk, with slight variations. You can create a class object to handle events in a form, a control, a report. The class "sinks" the events of the object it is passed using the WithEvents keyword. The events are raised automatically by the calling object and the sink reacts to them. The way around the problem you're describing is to create a form class and also individual control classes rather than having everything in a single class. Then you bind the form to the form class by passing the object (Me) to the class initialization routine. In that form class, you loop through the controls on the passed form and bind those you want to sink to individual control classes If you make the control arguments to the form class optional booleans with False as the default, you can simply pass an argument for, say, <code>Init(frm:=Me,Textboxes:=True)</code>
    , and set up the form class to only bind those controls flagged for binding.

    Or did I misunderstand your question?
    Charlotte

  3. #3
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Vienna, Wien, Austria
    Posts
    5,009
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Class Module (VBA (Access))

    In other words, what Ken Getz says in the Events Don't Happen on Their Own subsection (last
    Gre

  4. #4
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Class Module (VBA (Access))

    No, he's telling the absolute truth. You do need that property set to [Event Procedure], you can't raise events from a form that has no module. The code is setting up that property. This is standard code for binding the control in order to trap its events.
    Charlotte

  5. #5
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Vienna, Wien, Austria
    Posts
    5,009
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Class Module (VBA (Access))

    Okay, finally got there. Thank you.
    Gre

  6. #6
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Vienna, Wien, Austria
    Posts
    5,009
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Class Module (VBA (Access))

    As far as tiering the Class Modules, it seems that I'm missing a step or three.
    1. <LI>In the Form Module, the Declaration now refers to the "Form Class Module": <code>Private fmRecordings As clsForm</code><LI>Also in the Form Module, the Form_Open Sub changes to: <code>Set fmRecordings = New clsForm</code>. While I comprehend the idea about Booleans, following that through is another matter.... I do not see how the controls are now "looped through".<LI>In the "Form Class Module" something like <code>Private WithEvents mfrm As Access.Form Private mcbo As clsComboBox Private mtxt As clsTextBox</code> could be appropriate, or is that missing the point?<LI>It would seem that the Booleans are brought in in an Init Function in the "Form Class Module".<LI>When it comes, however, to running Init in the Form_Open Sub in the Form Module, to set the Boolean to True, I get "data member not found".
    Any 10T hints, please?
    Gre

  7. #7
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Class Module (VBA (Access))

    I'm sorry but what booleans are you referring to?

    1. Private fmRecordings As clsForm declares an object variable to hold an instance of a form class. Please note that it does not bind clsForm to a form called frmRecordings, even if that happens to be the form where this code is executing.

    2. frmRecordings = New clsForm instantiates frmRecordings as a new instance of clsForm, not as any particular form. To do that, you would need to pass clsForm's initialization routine (which has to be IN clsForm, not in the calling form) the form object you want to bind, i.e., frmRecordings.Init(Me) or frmRecordings.Init(Me, True)

    3. The WithEvents behavior depends on where you declare it. If you have a clsForm that includes code to conditionally bind all the comboboxes on a passed form object to clsCombo if you pass in a boolean, the clsForm.Init() might look a little like this:

    <pre>'clsForm
    Option Compare Database
    Option Explicit

    Private WithEvents cForm As Access.Form
    Private mstrFormNm As String
    Private mcolControls As New Collection

    Public Function Init(ByRef rfrm As Access.Form, _
    Optional blnBindCombos As Boolean)
    On Error GoTo Init_err
    mstrFormNm = rfrm.Name
    Set cForm = rfrm

    'Set up events to intercept
    cForm.OnClose = "[Event procedure]"
    cForm.OnError = "[Event procedure]"
    'Bind the form's controls
    Init = BindControls(rfrm, blnBindCombos)
    Init_exit:
    On Error Resume Next
    Exit Function
    Init_err:
    MsgBox "An error has occurred in the system." & vbCrLf _
    & "Unable to process your instructions."
    Resume Init_exit
    End Function

    Private Function BindControls(ByRef rfrm As Access.Form, _
    blnBindCombos As Boolean) As Boolean
    On Error GoTo BindControls_err
    Dim ctl As Access.Control
    Dim objCbo As clsComboBox

    Set cForm = rfrm
    cForm.OnClose = "[Event procedure]"
    cForm.OnCurrent = "[Event procedure]"

    For Each ctl In cForm.Controls
    If TypeOf ctl Is Access.ComboBox Then
    Set objCbo = New clsComboBox
    objCbo.Init rcbo:=ctl
    mcolControls.Add objCbo
    End If
    Next
    BindControls = True
    BindControls_exit:
    On Error Resume Next
    Exit Function
    BindControls_err:
    Select Case Err.Number
    Case CTL_NOT_FOUND
    ' Control not used on this form
    Resume Next
    Case Else
    MsgBox " Error #" & Err.Number _
    & vbCrLf & Err.Description
    Resume BindControls_exit
    End Select
    End Function</pre>


    Does that help at all?
    Charlotte

  8. #8
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Vienna, Wien, Austria
    Posts
    5,009
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Class Module (VBA (Access))

    That was all extremely helpful. (The Boolean I was referring to was in the one you refer to as frmRecordings.Init(Me, True). I still, however, get a Type mismatch on:<pre>Private Sub Form_Open(Cancel As Integer)
    Set fmRecordings = New clsForm
    fmRecordings.Init (Me)
    End Sub</pre>

    Expanding the arguments to fmRecordings.Init (Me, True) produces either "Syntax error" when pre-compiling or "Expected =" when running without pre-compiling.

    It also does seem that simply hiving off the previously functioning Class Module into clsComboBox may not be quite enough. At the moment, I have:<pre>Option Compare Database
    Option Explicit
    Private WithEvents mcbo As Access.ComboBox

    Private Sub Class_Terminate()
    Set mcbo = Nothing
    End Sub

    Public Function Init(ByRef rcbo As Access.ComboBox)
    On Error GoTo Init_err
    Set mcbo = rcbo

    'Set up events to intercept
    mcbo.OnNotInList = "[Event Procedure]"
    mcbo.OnGotFocus = "[Event Procedure]"

    Init_exit:
    On Error Resume Next
    Exit Function

    Init_err:
    MsgBox "An error has occurred in the system." & vbCrLf _
    & "Unable to process your instructions."
    Resume Init_exit
    End Function</pre>

    The function is cannibalised from your previous post. Previously, I had it as a Property Set procedure. I have not posted the Event Procedures.
    Gre

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: Class Module (VBA (Access))

    Your Init function expects to be passed a combo box object (ByRef rcbo As Access.ComboBox), but you pass it the form running the code (Me). You should create a function that loops through the controls of the form and processes the combo boxes, as shown in Charlotte's reply (BindControls).

  10. #10
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Vienna, Wien, Austria
    Posts
    5,009
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Class Module (VBA (Access))

    Let me rephrase the question. How should the Init Function in the clsComboBox Class be phrased? It is called by objCbo.Init rcbo:=ctl. Your second sentence seems to be already dealt with in the (above-mentioned) For loop in Charlotte's post.
    Gre

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: Class Module (VBA (Access))

    There are two distinct Init functions involved: one in the clsForm class module, and one in the clsComboBox class module.

    The first takes a Form object as argument, and perhaps an option Boolean argument:
    Public Function Init(ByRef rfrm As Access.Form, Optional blnBindCombos As Boolean)

    The one in clsComboBox takes a combo box object as argument:
    Public Function Init(ByRef rcbo As Access.ComboBox)

    The clsForm Init function calls a BindControls function, which in its turn calls the clsComboBox Init function.

  12. #12
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Vienna, Wien, Austria
    Posts
    5,009
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Class Module (VBA (Access))

    The case(s) you mention are represented by the code that I am running. Are you saying that you are not clear as to why I am getting a Type Mismatch?

    FWIW if I change the Form Code back to refer directly to the ComboBox Class Module - which means only one ComboBox per Form, the Event Procedures trigger as required.
    Gre

  13. #13
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Class Module (VBA (Access))

    You can't have two classes in a module, if that's what you're trying to do. You show this code:

    Private Sub Form_Open(Cancel As Integer)
    Set fmRecordings = New clsForm
    fmRecordings.Init (Me)
    End Sub

    but that has nothing to do with the combobox class, so I'm not sure what your question is. If you want to directly bind a combobox on your form to a combobox class, you would do it like this:

    <font color=blue>Private mcbo As clsComboBox</font color=blue>

    Private Sub Form_Open(Cancel As Integer)
    Set fmRecordings = New clsForm
    fmRecordings.Init (Me)

    <font color=blue>set mcbo = New clsComboBox
    mcbo.Init(Me.Combo1)</font color=blue>

    End Sub
    Charlotte

  14. #14
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Class Module (VBA (Access))

    Unk, I'm attaching a quick demo in A2k format that shows you how you can bind a control through the form class instead of directly. I hope this makes it clearer. The demo doesn't really do anything except allow you to select a customer name in a combobox and see the customerID appear in a textbox. However, the combobox changes color when it gets and loses the focus and that, plus setting the textbox value is done in the class modules rather than in the form itself.
    Charlotte

  15. #15
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Vienna, Wien, Austria
    Posts
    5,009
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Class Module (VBA (Access))

    Very many thanks. <img src=/S/hailpraise.gif border=0 alt=hailpraise width=27 height=22> The "flow through" now takes place. Progress had not been accelerated by trying to use a Function in the "lower tier" Class Module (clsComboBox) instead of a Sub!
    Gre

Page 1 of 2 12 LastLast

Posting Permissions

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