Results 1 to 9 of 9
  1. #1
    Join Date
    Aug 2010
    Posts
    57

    Unanswered: Need multiple buttons with similar functionality - change content of field

    I have a number of forms each with the same setup (call them "Main" forms). Each Main form has multiple fields implemented as a pull down which queries data from a different table (the "References" table). If the desired data is already in the References table, then the pulldown works fine. If it isn't, I have a button that will open up a "References" form that allows the user to enter all the data related to the Reference. When the References form is updated, the corresponding entry is selected/updated in the pulldown of the Main form.

    The process works great. Except, as I mentioned I have multiple Reference entries in multiple forms - all of which work the same way, except each entry is a different field and therefore has a different button. Each button has an event handler. Said event handler calls a common function that opens up the References form. After a Reference is selected, it's key (ID) is returned to the event handler, which then uses it to update the corresponding pulldown.

    I've managed to remove significant amount of code into a single function that is called from all the button even handlers. But each button event handler still has some amount of code related to getting and populating its corresponding pull-down field. Is there a way to consolidate all the event handlers for the buttons into a single function? Some ways come to mind, but I don't know how to implement:
    • A single event handler that can identify which button was pressed, and from the buttons name or text determine the corresponding pulldown field name so it can update it.
    • Multiple event handlers that have a single call to the shared function. However, I need a way of passing the object for the corresponding pulldown field to the common function.


    Any ideas would be appreciated!

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    One solution that comes to my mind would consist in writing a Class Module declared With Event then instanciate this class in every form where the same code structure is needed. You can even go further and create a form containing the button and use it as a subform in any form where it is needed.

    You would end up with a single class that could be common to several subforms containing one or more buttons and one of those subforms could be common to several forms.
    Have a nice day!

  3. #3
    Join Date
    Aug 2010
    Posts
    57
    Sinndho - The Class Module idea sounds about right. I'm not that familiar with classes in VBA - what do you mean by "declared with event"? Would the class instance be able to access the fields in the form? That's the same concern I have with the subform concept - I didn't think a subform could access its host's forms fields.
    Carlos

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    assuming that the sub form is open then its data is availabel to any other form or report that is also open

    you can pull values from another form
    eg
    acontrol = forms!myalreadyopenform.control
    avariable = forms!myalreadyopenform.thatvariable
    forget iit may require the exclamation mark for each element
    avariable = forms!myalreadyopenform!thatvariabel

    or you can push claues
    forms!myotherform.thatvariable = thisvariable
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Accessing a control on the parent form is fairly easy:
    Code:
    Me.Parent.<ControlName>
    or
    Code:
    Me.Parent!<ControlName>
    or
    Code:
    Me.Parent.Controls("<ControlName>")
    Be careful with the second notation, though, as it can address several collections on the form (controls or fields in the recordset). See for instance: access formscoding Re: The Bang ! operator vs the . operator..., ! or . or Re: The Bang ! operator vs the . operator... (among many others).

    I suggested a class because it allows to group several common functionalities (properties and methods) that become common to all subforms instanciating the class, then centralizing the code. See the attached file for a very simple example of what can be done that way.
    Attached Files Attached Files
    Have a nice day!

  6. #6
    Join Date
    Aug 2010
    Posts
    57
    Sinddho - thanks for your help. I agree with you that a class is the way to go. Only problem I'm having at the moment, is that the click event on my button is not passing down to the event handler in the class. The class has a control member (which I declared "WithEvents") to which I assign the button control in the form. I know I have passed the control to the class OK, because I can change its size, but I can't get it to receive any events. Once I have that, I think your class idea will work great.

    Unfortunately I cannot open up your example due to security restrictions in my company.

  7. #7
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    For an in-depth discussion about using classes declared WithEvents see:
    http://www.vb123.com/kb/index.html?199901_ss_event.htm and http://smsconsulting.spb****/shamil_.../otherpubs.htm
    Have a nice day!

  8. #8
    Join Date
    Aug 2010
    Posts
    57
    I had a simple problem! I'm used to Access automatically adding the "[Event Procedure]" to each button's click event. But of course, since here I'm only having one event handler, I have to add it manually. Oops. Thanks for your help, though - you got me started down the right path.

  9. #9
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You're welcome!

    b.t.w. You can have the "[Event Procedure]" added for the controls you want by the class when you initialize it. Here's an excerp from the first site I mentioned in my former post:
    Code:
    ' Anyway, on with the creation of your first DEEP object. 
    ' Add this method to the Class module:
    '
    Public Sub DeepsAttach(ByRef rfrm As Form)
    
        Set Form = rfrm
        Set cmdFirst = Form![cmdFirst]
        cmdFirst.OnClick = "[Event Procedure]"
        Set cmdNew = Form![cmdNew]
        cmdNew.OnClick = "[Event Procedure]"
        Set cmdDel = Form![cmdDel]
        cmdDel.OnClick = "[Event Procedure]"
        etc.
    
    End Sub
    Have a nice day!

Posting Permissions

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