Coding help 1

[lbunch]

I need to combine these two procedures and not sure how. User will Validate amounts but also need to have "rights" to approve which the criteria is in the Sub Can Approve.
Where and how do I use the CanApprove in the Validate sub???

Sub Validate()
If Val(Me.Projected & "") + Val(Me.Projected & "") = 0 Then
MsgBox "Forecast Must Be Entered"
Else
If Val(Me.Projected & "") + Val(Me.Projected & "") < 30000 Then
MsgBox "Approved"
Else
MsgBox "Amounts Cannot Be Approved"
End If
End If
End If
End Sub

Function CanApprove(strManagerType As String, TotalCost As Long) As Boolean
Dim ApprovalLimit As Long
Select Case strManagerType
Case "SM"
ApprovalLimit = 300000
Case "Dir"
ApprovalLimit = 1500000
Case "VP"
ApprovalLimit = 3000000
Case "EVP"
CanApprove = True
Exit Function
End Select
CanApprove = (TotalCost < ApprovalLimit)
End Function

 

[SgtJarrow]

Perhaps the following?

Sub Validate(strManagerType As String)
    If Val(Me.Projected & "") + Val(Me.Projected & "") = 0 Then
        MsgBox "Forecast Must Be Entered"
    Else
        If strManagerType = "EVP" Then
            MsgBox "Approved"
        Else
            If Val(Me.Projected & "") + Val(Me.Projected & "") < ApprovalAmount(strManagerType) Then
                MsgBox "Approved"
            Else
                MsgBox "Amounts Cannot Be Approved"
            End If
        End If
    End If
End Sub
      
Function ApprovalAmount(strManagerType As String) As Long
    Dim ApprovalLimit As Long
    Select Case strManagerType
        Case "SM"
            ApprovalLimit = 300000
        Case "Dir"
            ApprovalLimit = 1500000
        Case "VP"
            ApprovalLimit = 3000000
    End Select
        CanApprove = ApprovalLimit
End Function

=======================================
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
CCNA, CCDA, MCSA, CNA, Net+, A+, CHDP
VB/Access Programmer

 

[Remou]

mstrmage1768
I think you mean:
ApprovalAmount = ApprovalLimit

Yes No?

 

[SgtJarrow]

Yes..... Thanks. I changed the name of the function at the last minute and forget to change the return call.

=======================================
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
CCNA, CCDA, MCSA, CNA, Net+, A+, CHDP
VB/Access Programmer

 

[danvlas]

Maybe a table with 3 fields (ID, ManagerType, ApprovalLimit) would be more flexible.

For unlimited, you could enter 0 or null, as a special case.

This way you can have someone else manage the approval limits, as there is no change in the code. Many times, these limits change over time, depending on external factors and you don't want to be bothered or responsible...

I would also change the result of the function, to indicate:
-1: person is not authorized at all
0: cost exceeds authorization
1: cost can be authorized

Function CanApprove(strManagerType As String, TotalCost As Long) As Integer
Dim ApprovalLimit As Long
Dim rst As New ADODB.Recordset

Set rst = CurrentProject.Connection.Execute("Select * From tblApprovals Where ManagerType = '" & strManagerType & "'")

With rst
    If .EOF Then
        CanApprove = -1 'not authorized
    Else
        If Val(Nz(.Fields("ApprovalLimit"), 0)) = 0 Then 'Null or zero = unlimited
            CanApprove = 1
        Else
            CanApprove = Abs(TotalCost <= .Fields("ApprovalLimit")) 'compare cost with limit
        End If
    End If
End With

Set rst = Nothing
End Function

Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html

 

[SgtJarrow]

Good idea danvlas....If the original poster can add that, that would be much more flexible. I kept with the original data in my attempt to assist. Thanks for the alternate method.

=======================================
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
CCNA, CCDA, MCSA, CNA, Net+, A+, CHDP
VB/Access Programmer

 

[lbunch]

Thanks for the good help. If I use SM and Dir etc that is integrated in the Security workgroup this code will check who is logged on, right????

 

[lbunch]

Thanks for the help - am still working through

 

[lbunch]

Danvlas,
If I use the table - how does access know who is logged in - would I use a query to filter current user??? Say an EVP logs in - how would the code know who they were??

 

[danvlas]

What security model have you got in place? How do users log in? How does your original code identify the manager type?
You can use CurrentUser function to get the user name, if you use Access security model. Then you can check what groups he belongs to (I don't have the code at hand right now, but the Help file could give you a good start). Then check the approval limit for each group and keep the max value.

For another type of security, please describe it.

HTH


Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html