Select Case Public Function

**CJ_London** · 08-05-2021, 03:32 PM

Nothing is occuring with the reports.

depends which event you are calling the code from - try the report open event. Also I assume your function is in a general module?

**CJ_London** · 08-05-2021, 04:05 PM

on a general point and not to digress too far, functions can be called from anywhere including sql queries and as an event, Subs cannot. Functions can return a value or not as the case may be. I will often return a boolean which indicates the function has performed as expected e.g.

Code:

if myfunction(1,2) then 'myfunction has worked OK
     'carry on processing
else
     'make an orderly exit
end if

and myfunction might be something like

Code:

function myfunction(i as integer, j as integer) as boolean
on error goto errctrl


     'do something here

     myfunction=true
     exit function
      
errctrl:
     msgbox err.number & " " & err.description
     myfunction=false
end function

I also would not include Call and the brackets - they relate to ancient times for backwards compatibility and call is required for subs with more than one parameter and brackets are included. Just makes for more confusion

@PSSMargaret - to explain 'as an event' in form parameters, events tab, where you see [Event Procedure] you could simply type

=subBoxes([Form],[categoryID]+1))

in the form load or current event - not tested and not sure of your logic as to how categoryID is actually determined. But another simple example

in a general module create a function

Code:

Function fName(frm as form)

    msgbox "you have opened " & frm.name

End Function

and in any form open event where you would see [Event Procedure] put

=fName([Form])

then open the form and the function will run

Why would you do this? one reason is to make the form module smaller or not even required - which means the form will load faster. Another is when developing, you don't need to open the module to see the code. It is not a panacea for everything - for example you cannot use it where the event has parameters such as mousemove or double click events. But has it's uses

**CJ_London** · 08-05-2021, 05:09 PM

Is there a way to get the locked field codes to work with this?

only by excluding them for reports - perhaps something like

if typename(obj) like "Form_*" then .WithinToll.Locked = True 'otherwise do nothing

or disable the errors

not sure which error you are getting bus assuming it is 12345

Code:

function.....

    on error goto errctrl

Code:

    with obj

        'your code here

    end with

    exit function

errctrl:
   select case err
       Case 12345 'locked property does not exist
           resume next
       Case Else
           msgbox err.number & " " & err.description
   end select
end function

**PSSMargaret** · 08-06-2021, 07:28 AM

Thank Ajax for your suggestions above. I will read through and give them a try. I was very busy and had to move on so what I did was create two modules, one with the field shading code and the other with the fielding locking code. Called the field shading and field locking functions for the forms and called the field shading function only for the reports. There is still only one place to make changes for either shading or locking and both seem to be working well. I have to test further. Posting the code for other that come across this thread and will investigate Ajax's suggestions further. Thanks again.

Code:

Function FieldShading (meObj As Object, arg As Long)
 
    With meObj
       
        Select Case arg
 
        Case 1
 
            .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
 
            .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
 
            .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 Function

Code:

Function FieldLocks (meObj As Object, arg As Long)
 
    With meObj
 
     Select Case arg
 
      Case 1
            .ProblemState.Locked = True
            .LocalPriority.Locked = True
            .HwyIntLocalMatch.Locked = True
            .CriticalOpp.Locked = True
            .ProjectReadiness.Locked = True
            .WithinToll.Locked = True
            .FederalAid.Locked = True
 
      Case 2
            .ProblemState.Locked = False
            .LocalPriority.Locked = False
            .HwyIntLocalMatch.Locked = False
            .CriticalOpp.Locked = False
            .ProjectReadiness.Locked = False
            .WithinToll.Locked = False
            .FederalAid.Locked = False
 
      Case 3
            .ProblemState.Locked = True
            .LocalPriority.Locked = False
            .HwyIntLocalMatch.Locked = True
            .CriticalOpp.Locked = False
            .ProjectReadiness.Locked = False
            .WithinToll.Locked = True
            .FederalAid.Locked = True
 
End Select
       
End With
   
End Function

Code:

Public Sub Form_Current()
 
    If Forms!frmprojectdetailsdataentry!CategoryID <> 1 Or Forms!frmprojectdetailsdataentry!CategoryID <> 2 Or IsNull(Forms!frmprojectdetailsdataentry!CategoryID) Then
        Call FieldShading(Me, 1)
        Call FieldLocks(Me, 1)
    End If
 
    If Forms!frmprojectdetailsdataentry!CategoryID = 1 Then
        Call FieldShading(Me, 2)
        Call FieldLocks(Me, 2)
    End If
 
    If Forms!frmprojectdetailsdataentry!CategoryID = 2 Then
        Call FieldShading(Me, 3)
        Call FieldLocks(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 FieldShading(Me, 1)
    End If
 
    If Me.CategoryID = 1 Then
        Call FieldShading(Me, 2)
    End If
 
    If Me.CategoryID = 2 Then
        Call FieldShading(Me, 3)
    End If
 
End Sub

**Welshgasman** · 08-06-2021, 08:23 AM

Hmm, a third parameter seems easier and tidier to me, but to each their own.?

**Micron** · 08-06-2021, 08:57 AM

When you get tired of copying and pasting Forms!frmprojectdetailsdataentry.CategoryID over and over (or Heaven forbid, you are actually typing that) consider one of the following approaches

Code:

With Forms!frmprojectdetailsdataentry
  If .CategoryID <> 1 Or .CategoryID <> 2 Or IsNull(.CategoryID) Then

or

Code:

Dim ctl As Control
Set ctl = Forms!frmprojectdetailsdataentry.CategoryID

If IsNull(ctl) Or ctl <>1 Or ctl<>2 Then

**PSSMargaret** · 08-06-2021, 09:11 AM

I come to this board as a last resort and I almost made through without receiving condescending and belittling comments from another developer.

We get Micron, you’re way more experienced and smarter than the rest.

Being a developer to me is a work in progress and I learn all the time. I will review all the comments and hopefully learn from them but sometimes when you have a deadline, you have to move on and revisit later.

Learning on these boards would be so much nicer without the belittling comments. If you don’t have anything positive to lend, keep it to yourself.

**Welshgasman** · 08-06-2021, 09:23 AM

Originally Posted by PSSMargaret

I come to this board as a last resort and I almost made through without receiving condescending and belittling comments from another developer.

Learning on these boards would be so much nicer without the belittling comments. If you don’t have anything positive to lend, keep it to yourself.

If that is addressed to me, it was not meant to be, just my opinion, and the way I would have approached it.?
As i said 'each to their own', there is generally more than one way to achieve the goal?.
I'm sorry if you took it that way.

**PSSMargaret** · 08-06-2021, 09:35 AM

Welshgasman, My response was directed to Micron. Appreciate your suggestions.

**Micron** · 08-06-2021, 11:29 AM

So that's the thanks I get for trying to make your life easier? No problem - except that you have a paper thin skin and take what was meant to be helpful as condescending. Sorry if my style doesn't meet your standards. I'll just add you to my ignore list so that I never bother you again.

Select Case Public Function

Thread Tools

Similar Threads

Select case function problem

Public Function

Convert Private Function to Public Function

Public Function

Use Select Case to Consolidate Switch Function

Posting Permissions