Page 1 of 2 12 LastLast
Results 1 to 15 of 25
  1. 08-05-2021, 08:16 AM #1
    PSSMargaret
    PSSMargaret is offline Advanced Beginner
    Windows 10 Access 2010 64bit
    Join Date
    Dec 2016
    Posts
    55

    Select Case Public Function

    I have the Select Case code below in two forms and two reports. It provides shading changes to fields based on the category of the record. Also included in the same forms/reports is the code further below which calls the select case procedure based on the category of the current record. I have removed error handling and some of the code for simplicity.



    I believe that the Select Case code really belongs in a module as a Public Function instead of in four separate objects so when a change is needed, I don’t have to change it in four different places. However, I don’t know how to transition it to a Public Function. I copied it to module and, of course, when compiled the (me.) prior to the field names caused issues. Any guidance on how to transition this to a public function and call it from forms/reports would be greatly appreciated.


    Code:
    Private Sub subBoxes(arg As Long)
 
    Select Case arg
 
        Case 1 'All fields gray.
 
            Me.ProblemState.BackColor = RGB(217, 217, 217)
 
            Me.LocalPriority.BackColor = RGB(217, 217, 217)
 
            Me.HwyIntLocalMatch.BackColor = RGB(217, 217, 217)
            Me.HwyIntLocalMatchPoints.ForeColor = RGB(217, 217, 217)
            Me.HwyIntLocalMatchPoints.BackColor = RGB(217, 217, 217)
           
            Me.CriticalOpp.BackColor = RGB(217, 217, 217)
            Me.CriticalOppPoints.ForeColor = RGB(217, 217, 217)
            Me.CriticalOppPoints.BackColor = RGB(217, 217, 217)
           
            Me.ProjectReadiness.BackColor = RGB(217, 217, 217)
            Me.ProjectReadinessPoints.ForeColor = RGB(217, 217, 217)
            Me.ProjectReadinessPoints.BackColor = RGB(217, 217, 217)
           
            Me.WithinToll.BackColor = RGB(217, 217, 217)
           
            Me.FederalAid.BackColor = RGB(217, 217, 217)
 
 
        Case 2 'Highway & Intersection
 
            Me.ProblemState.BackColor = RGB(255, 255, 255)
 
            Me.LocalPriority.BackColor = RGB(255, 255, 255)
 
            Me.HwyIntLocalMatch.BackColor = RGB(255, 255, 255)
            Me.HwyIntLocalMatchPoints.ForeColor = RGB(0, 0, 0)
            Me.HwyIntLocalMatchPoints.BackColor = RGB(255, 255, 255)
 
            Me.CriticalOpp.BackColor = RGB(255, 255, 255)
            Me.CriticalOppPoints.ForeColor = RGB(0, 0, 0)
            Me.CriticalOppPoints.BackColor = RGB(255, 255, 255)
 
            Me.ProjectReadiness.BackColor = RGB(255, 255, 255)
            Me.ProjectReadinessPoints.ForeColor = RGB(0, 0, 0)
            Me.ProjectReadinessPoints.BackColor = RGB(255, 255, 255)
 
            Me.WithinToll.BackColor = RGB(255, 255, 255)
 
            Me.FederalAid.BackColor = RGB(255, 255, 255)
 
       Case 3 'Non-Highway (Bike & Ped)
           
            Me.ProblemState.BackColor = RGB(217, 217, 217)
 
            Me.LocalPriority.BackColor = RGB(255, 255, 255)
 
            Me.HwyIntLocalMatch.BackColor = RGB(217, 217, 217)
            Me.HwyIntLocalMatchPoints.ForeColor = RGB(217, 217, 217)
            Me.HwyIntLocalMatchPoints.BackColor = RGB(217, 217, 217)
 
            Me.CriticalOpp.BackColor = RGB(255, 255, 255)
            Me.CriticalOppPoints.ForeColor = RGB(0, 0, 0)
            Me.CriticalOppPoints.BackColor = RGB(255, 255, 255)
 
            Me.ProjectReadiness.BackColor = RGB(255, 255, 255)
            Me.ProjectReadinessPoints.ForeColor = RGB(0, 0, 0)
            Me.ProjectReadinessPoints.BackColor = RGB(255, 255, 255)
 
            Me.WithinToll.BackColor = RGB(217, 217, 217)
 
            Me.FederalAid.BackColor = RGB(217, 217, 217)
 
    End Select
 
End Sub


    Code:
    Public Sub Form_Current()
 
    If Forms!frmprojectdetailsdataentry!CategoryID <> 1 Or Forms!frmprojectdetailsdataentry!CategoryID <> 2 Or IsNull(Forms!frmprojectdetailsdataentry!CategoryID) Then
        Call subBoxes(1)
    End If
 
    If Forms!frmprojectdetailsdataentry!CategoryID = 1 Then
        Call subBoxes(2)
    End If
 
    If Forms!frmprojectdetailsdataentry!CategoryID = 2 Then
        Call subBoxes(3)
    End If
 
End Sub
    Reply With Quote Reply With Quote
  2. 08-05-2021, 08:22 AM #2
    Welshgasman
    Welshgasman is online now VIP
    Windows 10 Access 2007
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,940
    Pass in the form object Me, and use that in the function
    Code:
    subBoxes(frm AS Form, arg As Long)
    Then use frm instead of Me in that function.

    Calling it would be
    Code:
    Call subBoxes(Me,1)
    However that would only handle forms, not sure for reports?, perhaps use Object instead of form in the function?

    Try it?
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba
    Reply With Quote Reply With Quote
  3. 08-05-2021, 08:36 AM #3
    Micron
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,801
    when compiled the (me.) prior to the field names caused issues
    Because Me is out of scope. It can only be used in the module behind the form or report. Could do as suggested, or use the long version Forms!frmName.ControlNameHere
    I'd prefer passing the form if you need a function that will work for several forms.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.
    Reply With Quote Reply With Quote
  4. 08-05-2021, 08:46 AM #4
    orange's Avatar
    orange
    orange is offline Moderator
    Windows 10 Office 365
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,726
    Margaret,

    When you say all fields on the form or report
    Case 1 'All fields gray.
    , do you really mean all textboxes or some other control?
    AccessFunctions_SyntaxAndExamples TechontheNet
    M$oftVBA Function Details
    Debugging VBA and Error Handling
    Links to articles re Database Planning and Design
    DataPig Videos Revived DataPig Queries
    DataPig Form Basics
    Reply With Quote Reply With Quote
  5. 08-05-2021, 08:48 AM #5
    CJ_London
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,430
    in a standard module, something like

    Code:
    Function subBoxes(frm as Form, arg As Long) 'Public is the default
 
    with frm

        Select Case arg
 
        Case 1 'All fields gray.
 
            .ProblemState.BackColor = RGB(217, 217, 217)
 
            .LocalPriority.BackColor = RGB(217, 217, 217)
 
            .HwyIntLocalMatch.BackColor = RGB(217, 217, 217)
            .HwyIntLocalMatchPoints.ForeColor = RGB(217, 217, 217)
            .HwyIntLocalMatchPoints.BackColor = RGB(217, 217, 217)
           
            .CriticalOpp.BackColor = RGB(217, 217, 217)
...
...
...

    end with
end sub
    Reply With Quote Reply With Quote
  6. 08-05-2021, 08:52 AM #6
    Welshgasman
    Welshgasman is online now VIP
    Windows 10 Access 2007
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,940
    @Ajax,

    Ok, for my benefit please, what would we need to do, if it was a report?
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba
    Reply With Quote Reply With Quote
  7. 08-05-2021, 09:41 AM #7
    CJ_London
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,430
    use object type - works for forms and reports

    Code:
    Function subBoxes(meObj as object, arg As Long)

    with meObj
    Reply With Quote Reply With Quote
  8. 08-05-2021, 09:56 AM #8
    Welshgasman
    Welshgasman is online now VIP
    Windows 10 Access 2007
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,940
    Ah Ha, I guessed correct then. ��
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba
    Reply With Quote Reply With Quote
  9. 08-05-2021, 12:09 PM #9
    PSSMargaret
    PSSMargaret is offline Advanced Beginner
    Windows 10 Access 2010 64bit
    Join Date
    Dec 2016
    Posts
    55
    With the below changes in blue and removing the (me) before each field, it works great with the form. Nothing is occuring with the reports. The third set of code below is what I have for the reports. It's a Detail_Format event.



    Code:
    Function subBoxes(meObj As Object, arg As Long)
 
    With meObj

    Select Case arg
 
        Case 1 'All fields gray.
 
            .ProblemState.BackColor = RGB(217, 217, 217)
 
            .LocalPriority.BackColor = RGB(217, 217, 217)
 
            .HwyIntLocalMatch.BackColor = RGB(217, 217, 217)
            .HwyIntLocalMatchPoints.ForeColor = RGB(217, 217, 217)
            .HwyIntLocalMatchPoints.BackColor = RGB(217, 217, 217)
           
            .CriticalOpp.BackColor = RGB(217, 217, 217)
            .CriticalOppPoints.ForeColor = RGB(217, 217, 217)
            .CriticalOppPoints.BackColor = RGB(217, 217, 217)
           
            .ProjectReadiness.BackColor = RGB(217, 217, 217)
            .ProjectReadinessPoints.ForeColor = RGB(217, 217, 217)
            .ProjectReadinessPoints.BackColor = RGB(217, 217, 217)
           
            .WithinToll.BackColor = RGB(217, 217, 217)
           
            .FederalAid.BackColor = RGB(217, 217, 217)
 
 
        Case 2 'Highway & Intersection
 
            .ProblemState.BackColor = RGB(255, 255, 255)
 
            .LocalPriority.BackColor = RGB(255, 255, 255)
 
            .HwyIntLocalMatch.BackColor = RGB(255, 255, 255)
            .HwyIntLocalMatchPoints.ForeColor = RGB(0, 0, 0)
            .HwyIntLocalMatchPoints.BackColor = RGB(255, 255, 255)
 
            .CriticalOpp.BackColor = RGB(255, 255, 255)
            .CriticalOppPoints.ForeColor = RGB(0, 0, 0)
            .CriticalOppPoints.BackColor = RGB(255, 255, 255)
 
            .ProjectReadiness.BackColor = RGB(255, 255, 255)
            .ProjectReadinessPoints.ForeColor = RGB(0, 0, 0)
            .ProjectReadinessPoints.BackColor = RGB(255, 255, 255)
 
            .WithinToll.BackColor = RGB(255, 255, 255)
 
            .FederalAid.BackColor = RGB(255, 255, 255)
 
       Case 3 'Non-Highway (Bike & Ped)
           
            .ProblemState.BackColor = RGB(217, 217, 217)
 
            .LocalPriority.BackColor = RGB(255, 255, 255)
 
            .HwyIntLocalMatch.BackColor = RGB(217, 217, 217)
            .HwyIntLocalMatchPoints.ForeColor = RGB(217, 217, 217)
            .HwyIntLocalMatchPoints.BackColor = RGB(217, 217, 217)
 
            .CriticalOpp.BackColor = RGB(255, 255, 255)
            .CriticalOppPoints.ForeColor = RGB(0, 0, 0)
            .CriticalOppPoints.BackColor = RGB(255, 255, 255)
 
            .ProjectReadiness.BackColor = RGB(255, 255, 255)
            .ProjectReadinessPoints.ForeColor = RGB(0, 0, 0)
            .ProjectReadinessPoints.BackColor = RGB(255, 255, 255)
 
            .WithinToll.BackColor = RGB(217, 217, 217)
 
            .FederalAid.BackColor = RGB(217, 217, 217)
 
    End Select

    End With
 
End Sub

    Code:
    Public Sub Form_Current()
 
    If Forms!frmprojectdetailsdataentry!CategoryID <> 1 Or Forms!frmprojectdetailsdataentry!CategoryID <> 2 Or IsNull(Forms!frmprojectdetailsdataentry!CategoryID) Then
        Call subBoxes(Me, 1)
    End If
 
    If Forms!frmprojectdetailsdataentry!CategoryID = 1 Then
        Call subBoxes(Me, 2)
    End If
 
    If Forms!frmprojectdetailsdataentry!CategoryID = 2 Then
        Call subBoxes(Me, 3)
    End If
 
End Sub

    Code:
    Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)

    If Me.CategoryID <> 1 Or Me.CategoryID <> 2 Or IsNull(Me.CategoryID) Then
        Call subBoxes(Me, 1)
    End If

    If Me.CategoryID = 1 Then
        Call subBoxes(Me, 2)
        Me.srptCapacity.Visible = True
    End If

    If Me.CategoryID = 2 Then
        Call subBoxes(Me, 3)
        Me.srptDestinations.Visible = True
    End If

        End Sub
    Reply With Quote Reply With Quote
  10. 08-05-2021, 12:15 PM #10
    PSSMargaret
    PSSMargaret is offline Advanced Beginner
    Windows 10 Access 2010 64bit
    Join Date
    Dec 2016
    Posts
    55
    Not all text boxes are applicable to every category so the different cases are based on specific categories.
    Reply With Quote Reply With Quote
  11. 08-05-2021, 12:20 PM #11
    Welshgasman
    Welshgasman is online now VIP
    Windows 10 Access 2007
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,940
    How do you have a function ending with End Sub?
    I would have thought it would be a Sub anyway?
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba
    Reply With Quote Reply With Quote
  12. 08-05-2021, 12:25 PM #12
    PSSMargaret
    PSSMargaret is offline Advanced Beginner
    Windows 10 Access 2010 64bit
    Join Date
    Dec 2016
    Posts
    55
    Actually, I have End Function at the end of the Public Function. Missed updating that in the text above. Also, the module contains some direction to lock fields which isn't applicable to reports. Would that just be ignored by the report procedure.
    Reply With Quote Reply With Quote
  13. 08-05-2021, 12:54 PM #13
    orange's Avatar
    orange
    orange is offline Moderator
    Windows 10 Office 365
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,726
    welshgasman,
    I wondered the same thing when function was displayed- why this wasn't a sub - since it isn't returning a value.

    Would that just be ignored by the report procedure.
    I don't know, but you could test to confirm. It may give a trappable error you could deal with.
    I found this link.

    Do you have a sample database you could post? Just enough to see/test the effects of the sub routine with form and report.
    AccessFunctions_SyntaxAndExamples TechontheNet
    M$oftVBA Function Details
    Debugging VBA and Error Handling
    Links to articles re Database Planning and Design
    DataPig Videos Revived DataPig Queries
    DataPig Form Basics
    Reply With Quote Reply With Quote
  14. 08-05-2021, 02:14 PM #14
    PSSMargaret
    PSSMargaret is offline Advanced Beginner
    Windows 10 Access 2010 64bit
    Join Date
    Dec 2016
    Posts
    55
    I commented out all of the lines in module code that had to do with locking the fields and the shading code actually does work with the reports. Is there a way to get the locked field codes to work with this?

    Code:
    Function subBoxes(meObj As Object, arg As Long)

    With meObj
        
        Select Case arg

        Case 1 'All fields locked and gray.

            '.ProblemState.Locked = True
            .ProblemState.BackColor = RGB(217, 217, 217)

            '.LocalPriority.Locked = True
            .LocalPriority.BackColor = RGB(217, 217, 217)

            '.HwyIntLocalMatch.Locked = True
            .HwyIntLocalMatch.BackColor = RGB(217, 217, 217)
            .HwyIntLocalMatchPoints.ForeColor = RGB(217, 217, 217)
            .HwyIntLocalMatchPoints.BackColor = RGB(217, 217, 217)

            '.CriticalOpp.Locked = True
            .CriticalOpp.BackColor = RGB(217, 217, 217)
            .CriticalOppPoints.ForeColor = RGB(217, 217, 217)
            .CriticalOppPoints.BackColor = RGB(217, 217, 217)

            '.ProjectReadiness.Locked = True
            .ProjectReadiness.BackColor = RGB(217, 217, 217)
            .ProjectReadinessPoints.ForeColor = RGB(217, 217, 217)
            .ProjectReadinessPoints.BackColor = RGB(217, 217, 217)

            '.WithinToll.Locked = True
            .WithinToll.BackColor = RGB(217, 217, 217)

            '.FederalAid.Locked = True
            .FederalAid.BackColor = RGB(217, 217, 217)

Case 2 'Highway & Intersection

            '.ProblemState.Locked = False
            .ProblemState.BackColor = RGB(255, 255, 255)

            '.LocalPriority.Locked = False
            .LocalPriority.BackColor = RGB(255, 255, 255)

            '.HwyIntLocalMatch.Locked = False
            .HwyIntLocalMatch.BackColor = RGB(255, 255, 255)
            .HwyIntLocalMatchPoints.ForeColor = RGB(0, 0, 0)
            .HwyIntLocalMatchPoints.BackColor = RGB(255, 255, 255)

            '.CriticalOpp.Locked = False
            .CriticalOpp.BackColor = RGB(255, 255, 255)
            .CriticalOppPoints.ForeColor = RGB(0, 0, 0)
            .CriticalOppPoints.BackColor = RGB(255, 255, 255)

            '.ProjectReadiness.Locked = False
            .ProjectReadiness.BackColor = RGB(255, 255, 255)
            .ProjectReadinessPoints.ForeColor = RGB(0, 0, 0)
            .ProjectReadinessPoints.BackColor = RGB(255, 255, 255)

            '.WithinToll.Locked = False
            .WithinToll.BackColor = RGB(255, 255, 255)

            '.FederalAid.Locked = False
            .FederalAid.BackColor = RGB(255, 255, 255)

Case 3 'Non-Highway (Bike & Ped)

            '.ProblemState.Locked = True
            .ProblemState.BackColor = RGB(217, 217, 217)

            '.LocalPriority.Locked = False
            .LocalPriority.BackColor = RGB(255, 255, 255)

            '.HwyIntLocalMatch.Locked = True
            .HwyIntLocalMatch.BackColor = RGB(217, 217, 217)
            .HwyIntLocalMatchPoints.ForeColor = RGB(217, 217, 217)
            .HwyIntLocalMatchPoints.BackColor = RGB(217, 217, 217)

            '.CriticalOpp.Locked = False
            .CriticalOpp.BackColor = RGB(255, 255, 255)
            .CriticalOppPoints.ForeColor = RGB(0, 0, 0)
            .CriticalOppPoints.BackColor = RGB(255, 255, 255)

            '.ProjectReadiness.Locked = False
            .ProjectReadiness.BackColor = RGB(255, 255, 255)
            .ProjectReadinessPoints.ForeColor = RGB(0, 0, 0)
            .ProjectReadinessPoints.BackColor = RGB(255, 255, 255)

            '.WithinToll.Locked = True
            .WithinToll.BackColor = RGB(217, 217, 217)

            '.FederalAid.Locked = True
            .FederalAid.BackColor = RGB(217, 217, 217)

End Select
        
    End With
    
End Function
    Reply With Quote Reply With Quote
  15. 08-05-2021, 02:31 PM #15
    Welshgasman
    Welshgasman is online now VIP
    Windows 10 Access 2007
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,940
    Pass another parameter into the sub, indicating form or report, and lock on form?
    Alternatively see if you can identify the type of object?
    https://stackoverflow.com/questions/...ne-object-type
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba
    Reply With Quote Reply With Quote
Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.
« Previous Thread | Next Thread »

Similar Threads

  1. Select case function problem
    By HS_1 in forum Programming
    Replies: 12
    Last Post: 10-27-2019, 09:32 PM
  2. Public Function
    By sparker75 in forum Programming
    Replies: 14
    Last Post: 11-14-2018, 10:21 AM
  3. Convert Private Function to Public Function
    By zozzz in forum Modules
    Replies: 15
    Last Post: 10-17-2018, 09:26 PM
  4. Public Function
    By ShostyFan in forum Programming
    Replies: 3
    Last Post: 09-30-2017, 09:58 AM
  5. Use Select Case to Consolidate Switch Function
    By P5C768 in forum Queries
    Replies: 3
    Last Post: 05-06-2011, 02:49 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
  •  

Forum Rules

Other Forums: Microsoft Office Forums