Results 1 to 11 of 11
  1. #1
    Join Date
    Jan 2005
    Posts
    24

    Unanswered: Common problem - but have no solution

    MSAccess 2003. Can anybody tell me if it is possible to define a procedure for an event such as double click to be picked up by the form, irrespective of the double click physically being in the bounds of a control - I mean without having to put a statement in each control. Trying to get around a problem of many controls, and future control additions (forgetting the code for the parent event)

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Sadly not Andy (can I call you Andy? )

    .NET allows you to have one handler for n controls but not Access VBA. The best way (I have found) to deal with this is:
    1) Write a private method for your form and put all your handler code in this.
    2) You will have to handle each controls event however you can merely put in a call to the proc you created in 1. Thus - any changes do not have to be repeated n times.
    3) Usually when I find myself in this situation I have a load of identical controls and I use a naming convention with a number in the name to differentiate (e.g. txtMenu1, txtMenu2, txtMenu3) - this is the closest to the VB 6 control array I have been able to get. If you have such a naming scheme (i.e. control names can be derived) then you can write a little proc to loop and print all the event handler code to the immediate window. You can then copy and paste this into your form module.

    HTH
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Jan 2005
    Posts
    24
    That's a pootle flumplistic idea.

    Doesn't deal with later additions which I am sure to forget, but its an improvement on the usual painfully boring process.

    Thanks anyway.

  4. #4
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011
    You can use the Double-Click event of the form itself, but if the mouse if over a control, the form won't see it.

  5. #5
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011
    Use the Tag property, so you can retain your control names.

    The code is repettive, but effective and easy to write. With a bit of savvy, you can make a spreadsheet that writes the code for you and just copy and paste.

  6. #6
    Join Date
    Jan 2005
    Posts
    24
    Quote Originally Posted by tcace
    You can use the Double-Click event of the form itself, but if the mouse if over a control, the form won't see it.
    That was my point and why I said i
    irrespective of the double click physically being in the bounds of a control

  7. #7
    Join Date
    Jan 2005
    Posts
    24
    I must admit I hadn't thought much along the lines of programatically generated code (excel or otherwise) but I am not comfortable with having 40 text controls named Text1 etc - as this makes maintenance in complex code a serious headache - but I will have to give this some thought.

    The whole thing is about productivity and consistant behaviour - double click does the same on all text controls for instance - in my case open a larger editing box.

    Anyway thanks for the comments.

  8. #8
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011
    Here's a little sample where clicking the colum header sorts the form by that field. All the Labels are named Sort1, Sort2, etc. and each one has the name of it's related field as it's tag property. The text box names match their respective control sources.
    Code:
    Sub SortStack(iCtl As Integer)
    
        Dim strSort As String
        Dim strCtl As String
        Dim iC As Integer
            
        strCtl = "Sort" & Format(iCtl)
    
        ' Default is descending
        strSort = "[" & Me(strCtl).Tag & "] Desc"
    
        ' If the form is already sorting by that field descending, then set it to ascending
        If Me.OrderBy = strSort Then strSort = "[" & Me(strCtl).Tag & "]"
    
        ' Set the form properties
        Me.OrderBy = strSort
        Me.OrderByOn = True
    
        ' This changes all columns to black text
        On Error Resume Next
        For iC = 1 To 21
            strSort = "Sort" & Format(iC)
            Me(strSort).ForeColor = 0
            strSort = Me("Sort" & Format(iC)).Tag
            Me(strSort).ForeColor = 0
            If Err Then Err.Clear
        Next
    
        ' This changes the column sorted by to blue
        Me(strCtl).ForeColor = 16711680
        strSort = Me("Sort" & Format(iCtl)).Tag
        Me(strSort).ForeColor = 16711680
        If Err Then Err.Clear
    
    End Sub
    
    Private Sub Sort1_Click()
        Call SortStack(1)
    End Sub
    
    Private Sub Sort2_Click()
        Call SortStack(2)
    End Sub
    
    Private Sub Sort3_Click()
        Call SortStack(3)
    End Sub
    
    Private Sub Sort4_Click()
        Call SortStack(4)
    End Sub
    
    Private Sub Sort5_Click()
        Call SortStack(5)
    End Sub
    
    Private Sub Sort6_Click()
        Call SortStack(6)
    End Sub
    
    Private Sub Sort7_Click()
        Call SortStack(7)
    End Sub
    Adding and removing controls from the form is a snap - especially since I have code for about 30 controls even though there's only about 20 controls currently on it (Access doesn't care if you have extra Subs).

    Unfortunately, Access doesn't let you use control arrays - of course, a control array would require all the controls to have the same name anyway!
    Last edited by tcace; 04-06-06 at 21:51.

  9. #9
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    As it happens - the text1, text2 etc idea is only when the controls are the "same". It actually reduces technical headaches! However - if you mean you have a normal form with lots of controls that serve all different functions then you could adjust the idea to loop through the forms controls collection e.g.:

    Code:
        Dim ctl As Access.Control
        
        For Each ctl In Me.Controls
            
            Debug.Print ctl.Name & "_DblClick(Cancel As Integer)" & vbCrLf
            Debug.Print vbTab & "Call MyDoubleClickHandler" & vbCrLf
            Debug.Print "End Sub" & vbCrLf
        
        Next ctl
        
        Set ctl = Nothing
    Testimonial:
    pootle flump
    ur codings are working excelent.

  10. #10
    Join Date
    Jan 2005
    Posts
    24
    Yes lots of different controls and functionality but requiring uniform interface effects (such as opening a larger edit box)

    and with this reply: now were talking - a flumptastical solution.

    But moving on from this then I wonder if it is possible to write code at the application level ( coming from delphi - not sure of the correct terminology here but meaning at a level that could perhaps monitor events at the form design level). Your solution is great from an initial pre-coding perspective, where controls are all laid out on the form in advance. But I am always amending existing forms, so would benefit from extending the concept so that new fields added to a form would not risk being forgotton: achievable if it were possible to trap an event that occurs whilst in form design i.e dropping a new text box onto the form!

    Quote Originally Posted by pootle flump
    As it happens - the text1, text2 etc idea is only when the controls are the "same". It actually reduces technical headaches! However - if you mean you have a normal form with lots of controls that serve all different functions then you could adjust the idea to loop through the forms controls collection e.g.:

    Code:
        Dim ctl As Access.Control
        
        For Each ctl In Me.Controls
            
            Debug.Print ctl.Name & "_DblClick(Cancel As Integer)" & vbCrLf
            Debug.Print vbTab & "Call MyDoubleClickHandler" & vbCrLf
            Debug.Print "End Sub" & vbCrLf
        
        Next ctl
        
        Set ctl = Nothing

  11. #11
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    I'm a big believer in coding any part of my job that is repetative and follows constant rules.

    There is no way to trap such an event. The best you can do is run the code periodically. Or for more sophistication you could play around with the Access Module object - perhaps loop through your forms, loop through their controls and parse the form module to see if the control events exist. If not - write them to the module. You could have this code run on startup. My first ever thread on this forum involves most of these techniques - http://www.dbforums.com/showthread.php?t=995279

    However - although I'm a fan of automating my job this might be a step or two further than is worth the effort - you are the best judge
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

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