Results 1 to 8 of 8
  1. #1
    Mclaren is offline Competent Performer
    Windows 7 Access 2007
    Join Date
    Feb 2010
    Location
    Johannesburg
    Posts
    164

    Reducing code

    I have a form with five text boxes. these are used in a similar manner to radio buttons. i.e if i double click a box an x is either inserted or removed from the box. I have the following code in each boxes double click event, with the box name changing for each box:



    Code:
    If Me.txtbox1 = "X" Then
    Me.txtbox1 = ""
    Else
    Me.txtbox1 = "X"
    End If
    i would like to be able to insert code to do the checking of each boxes using a function rather than duplicating code that only has a small difference for each box.

  2. #2
    ssanfu is offline Master of Nothing
    Windows 2K Access 2000
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Would this work for you??

    Code:
    Private Sub txtbox1_DblClick(Cancel As Integer)
       Me.txtbox1 = IIf(Me.txtbox1 = "X", "", "X")
    End Sub
    or you could try

    Code:
    Private Sub txtbox1_DblClick(Cancel As Integer)
       CheckUncheck ("txtbox1")
    End Sub
    
    Private Sub txtbox2_DblClick(Cancel As Integer)
       CheckUncheck ("txtbox2")
    End Sub
    
    
    Public Sub CheckUncheck(ctlName As String)
    
       If Me.Controls(ctlName) = "X" Then
          Me.Controls(ctlName) = ""
       Else
          Me.Controls(ctlName) = "X"
       End If
    End Sub

  3. #3
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    You can probably also use ActiveControl to avoid having to pass the control name. Used in a standard module with ActiveForm that would let you use it from any form.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  4. #4
    ssanfu is offline Master of Nothing
    Windows 2K Access 2000
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    I didn't even think about using ActiveForm/ActiveControl.


    Here is the revised code:

    This goes in the form module:

    Code:
    '(example code for control)
    Private Sub txtbox1_DblClick(Cancel As Integer)
       Call CheckUncheck(Screen.ActiveForm.Name, Screen.ActiveControl.Name)
    
    End Sub
    
    Private Sub txtbox2_DblClick(Cancel As Integer)
       Call CheckUncheck(Screen.ActiveForm.Name, Screen.ActiveControl.Name)
    
    End Sub
    And this goes in a standard module:
    Code:
    Sub CheckUncheck(frmsName As String, ctlName As String)
    
       If Forms(frmsName).Controls(ctlName) = "X" Then
          Forms(frmsName).Controls(ctlName) = ""
       Else
          Forms(frmsName).Controls(ctlName) = "X"
       End If
    End Sub
    Thanks Paul

  5. #5
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Right idea, wrong place.

    I'd put it in the function, not in every call:

    Code:
    Public Function CheckUncheck()
      Dim strForm                 As String
      Dim strControl              As String
    
      strForm = Screen.ActiveForm.Name
      strControl = Screen.ActiveControl.Name
      
      If Forms(strForm).Controls(strControl) = "X" Then
        Forms(strForm).Controls(strControl) = ""
      Else
        Forms(strForm).Controls(strControl) = "X"
      End If
    End Function
    That way all you need to call it is

    CheckUncheck

    with no parameters.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  6. #6
    Mclaren is offline Competent Performer
    Windows 7 Access 2007
    Join Date
    Feb 2010
    Location
    Johannesburg
    Posts
    164
    Thanks guys, i have just learnt a lot. no joke.

    This will help me in more than just this one example.

  7. #7
    ssanfu is offline Master of Nothing
    Windows 2K Access 2000
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Quote Originally Posted by pbaldy View Post
    Right idea, wrong place.

    I'd put it in the function, not in every call:

    Code:
    Public Function CheckUncheck()
      Dim strForm                 As String
      Dim strControl              As String
    
      strForm = Screen.ActiveForm.Name
      strControl = Screen.ActiveControl.Name
      
      If Forms(strForm).Controls(strControl) = "X" Then
        Forms(strForm).Controls(strControl) = ""
      Else
        Forms(strForm).Controls(strControl) = "X"
      End If
    End Function
    That way all you need to call it is

    CheckUncheck

    with no parameters.


    Arrrrggggghhhh...


    So then, how about this:

    Code:
    Public Function CheckUncheck()
       Dim strForm As String
       Dim strControl As String
    
       strForm = Screen.ActiveForm.Name
       strControl = Screen.ActiveControl.Name
    
       Forms(strForm).Controls(strControl) = IIf(Forms(strForm).Controls(strControl) = "X", "", "X")
    
    End Function

  8. #8
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    I like it!
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 4
    Last Post: 04-05-2011, 06:12 PM
  2. VBA Code Help Please
    By jo15765 in forum Programming
    Replies: 8
    Last Post: 01-15-2011, 01:00 PM
  3. Word code in Access - How to modify my current code
    By Alexandre Cote in forum Programming
    Replies: 0
    Last Post: 11-15-2010, 08:26 AM
  4. Code in combobox, code in text box
    By float in forum Forms
    Replies: 3
    Last Post: 09-29-2010, 07:12 AM
  5. Access 2003 code vs Access 2007 Code
    By ralphjramirez in forum Access
    Replies: 5
    Last Post: 11-23-2009, 12:33 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums