Results 1 to 7 of 7
  1. #1
    d9pierce1 is offline Expert
    Windows 10 Access 2016
    Join Date
    Jan 2012
    Location
    Oklahoma
    Posts
    754

    Private Sub Assistance

    HI all,


    Trying to use more Private Subs and call to them to clean up a lot of useless code.
    Below is a Private Sub and I want to know is there a better way to write it and what would that be?
    I have a status lable on my header of form, It should be standard company unless one of the two
    check boxes are clicked. If Preferred is checked, then do not use is not enabled, and vise versa and it
    will change the lable to which ever is checked. This all works but something is telling me that the code in
    the sub could be condensed into a shorter version but not sure how.
    Thanks
    Dave


    Code:
    Private Sub CompanyStatus()
    
    
        If ChkPreferred = True Then
            Status.Caption = "Preferred Company"
            ChkNoSolicit.Enabled = False
            ChkPreferred.Enabled = True
        Else
            Status.Caption = "Standard Company"
            ChkNoSolicit.Enabled = True
            ChkPreferred.Enabled = True
            
        If ChkNoSolicit = True Then
            Status.Caption = "Do Not Use"
            ChkPreferred.Enabled = False
            ChkNoSolicit.Enabled = True
        Else
            Status.Caption = "Standard Company"
            ChkPreferred.Enabled = True
            ChkNoSolicit.Enabled = True
        End If
        End If
    End Sub

  2. #2
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Not sure about your exact requirements\setup but you could replace the two fields Yes\No fields ([Preferred],[NoSolicit]) with one called [CompanyStatus]. Then bind that field to an Option group with three options labeled "Preferred","Do Not Use" and "Standard Company". In the option group wizard set the desired values for those labels (numeric probably best, but you could use the labels themselves). This way you do not need this code at all.

    Cheers,
    Vlad
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  3. #3
    d9pierce1 is offline Expert
    Windows 10 Access 2016
    Join Date
    Jan 2012
    Location
    Oklahoma
    Posts
    754
    Thank you Gicu,
    I am trying my best to stay away from option groups as i have found they are hard to get a txt value from at times. In this case, it should be fine as will not be calling the txt value anywhere else so most likely would be an acceptable choice.
    I have been playing around with this and came up with this which removed a couple of lines.
    Thanks

    Code:
    Private Sub CompanyStatus()
    
    
        If ChkPreferred = True Then
            Status.Caption = "Preferred Company"
            ChkNoSolicit.Enabled = False
            ChkPreferred.Enabled = True
        ElseIf ChkNoSolicit = True Then
            Status.Caption = "Do Not Use"
            ChkPreferred.Enabled = False
            ChkNoSolicit.Enabled = True
        Else
            Status.Caption = "Standard Company"
            ChkPreferred.Enabled = True
            ChkNoSolicit.Enabled = True
        End If
        
    End Sub

  4. #4
    accesstos's Avatar
    accesstos is offline Expert
    Windows XP Access 2007
    Join Date
    Dec 2018
    Location
    Greece
    Posts
    551
    I agree with Vlad. Option group will be clearer for the user and absolve you from any code and risky disables.
    You can use its numeric value in a CHOOSE() function to produce any text result or you can get the caption directly from its labels.
    For example, in a textbox:
    Code:
    =[Form].[controls]("Label" & [Frame1]).[Caption]
    "Label1" could be the label of the first option etc.

  5. #5
    d9pierce1 is offline Expert
    Windows 10 Access 2016
    Join Date
    Jan 2012
    Location
    Oklahoma
    Posts
    754
    I will do some testing on it.
    Thanks
    dave

  6. #6
    ssanfu is offline Master of Nothing
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Just curious. With two check boxes, there are 4 possibilities.
    Code:
    Private Sub CompanyStatus()
    
        'Default
        If (ChkPreferred = False And ChkNoSolicit = False) Then
            Me.Status.Caption = "Standard Company"
            Me.ChkPreferred.Enabled = True
            Me.ChkNoSolicit.Enabled = True
        End If
    
        If (ChkPreferred = False And ChkNoSolicit = True) Then
            Me.Status.Caption = "Do Not Use"
            Me.ChkPreferred.Enabled = False
            Me.ChkNoSolicit.Enabled = True
        End If
    
        If (ChkPreferred = True And ChkNoSolicit = False) Then
            Me.Status.Caption = "Preferred Company"
            Me.ChkNoSolicit.Enabled = False
            Me.ChkPreferred.Enabled = True
        End If
    
        If (ChkPreferred = True And ChkNoSolicit = True) Then
        
            Me.Status.Caption = "Oh my. What to do?????"
        
        End If
    End Sub

  7. #7
    d9pierce1 is offline Expert
    Windows 10 Access 2016
    Join Date
    Jan 2012
    Location
    Oklahoma
    Posts
    754
    Hi ssanfu,
    The way i set it up there is no way to check both boxes as if one is checked, the other is not enabled to check. you must uncheck one to get to the other...
    Thanks

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

Similar Threads

  1. Private Function
    By UT227 in forum Programming
    Replies: 7
    Last Post: 05-03-2018, 09:47 AM
  2. Private Sub Form_Current use
    By Lou_Reed in forum Access
    Replies: 5
    Last Post: 01-20-2017, 09:32 AM
  3. Changing private sub dim to public sub dim
    By Stephenson in forum Programming
    Replies: 14
    Last Post: 10-05-2015, 02:59 PM
  4. Combining Private Sub
    By Thompyt in forum Programming
    Replies: 2
    Last Post: 11-11-2014, 02:25 PM
  5. Calling fields into VBA Private Sub
    By fullshape in forum Programming
    Replies: 3
    Last Post: 02-18-2011, 09:22 AM

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