Else in If statement without having to repeat code
The code attached works, but it is messy. What I need is if there is no record count, the code simply runs the strSelect statement. However, I am trying to eliinate the repeated code for the select statement in each of the if statements, am looking for suggestions on how to make this a little more elegant, right now it is simply blunt force processing.
Public Function GetRepPersonalInfoEOM(ByVal RepID As Integer, ByVal EvalYear As Integer, ByVal EvalQuarter As Integer, ByVal Description As String) As String
On Error GoTo ErrorHandler
'Employee of the month is handled a little differently, first need to check to see if managers have inputted a
'value, it not, then do not bring in data as it may overwrite what has been entered from the Accountabilities side
Dim strSelect As String
Dim strEOM As String
Dim strDesc As String
Dim strCol As String
Dim rst As Recordset
Dim rs As Recordset
If Description = "Employee of the Month ($$$)" Then
strEOM = "SELECT Representative.ID, TblIncentives.IncYear, TblIncentives.IncQuarter, TblIncentives.IncEOM " & _
"FROM (TblEmployees INNER JOIN TblIncentives ON TblEmployees.EmpEmployeeID = TblIncentives.IncEmployeeID) " & _
"INNER JOIN Representative ON TblEmployees.EmpFRBEmployeeNumber = Representative.frb_employee_number " & _
"WHERE Representative.ID = " & RepID & " AND TblIncentives.IncYear = " & EvalYear & " AND " & _
"TblIncentives.IncQuarter = " & EvalQuarter & " AND TblIncentives.IncEOM > 0 "
Set rst = CurrentDb.OpenRecordset(strEOM, dbOpenSnapshot)
If rst.RecordCount > 0 Then
GetRepPersonalInfoEOM = (rst!IncEOM)
Exit Function
Else
strSelect = "SELECT value " & _
"FROM EmployeeInfo " & _
"WHERE ID=" & RepID & " AND eval_quarter=" & EvalQuarter & " AND eval_year=" & EvalYear & " " & _
"AND description='" & Description & "' AND Nz(value, 0) > 0 "
Set rs = CurrentDb.OpenRecordset(strSelect, dbOpenSnapshot)
If rs.RecordCount > 0 Then
GetRepPersonalInfoEOM = Nz(rs!value)
End If
End If
ElseIf Description = "Employee of the Month Nominee ($$)" Then
strEOM = "SELECT Representative.ID, TblIncentives.IncYear, TblIncentives.IncQuarter, TblIncentives.IncOther " & _
"FROM (TblEmployees INNER JOIN TblIncentives ON TblEmployees.EmpEmployeeID = TblIncentives.IncEmployeeID) " & _
"INNER JOIN Representative ON TblEmployees.EmpFRBEmployeeNumber = Representative.frb_employee_number " & _
"WHERE Representative.ID = " & RepID & " AND TblIncentives.IncYear = " & EvalYear & " AND " & _
"TblIncentives.IncQuarter = " & EvalQuarter & " AND TblIncentives.IncOther > 0 "
Set rst = CurrentDb.OpenRecordset(strEOM, dbOpenSnapshot)
If rst.RecordCount > 0 Then
GetRepPersonalInfoEOM = (rst!IncOther)
Exit Function
Else
strSelect = "SELECT value " & _
"FROM EmployeeInfo " & _
"WHERE ID=" & RepID & " AND eval_quarter=" & EvalQuarter & " AND eval_year=" & EvalYear & " " & _
"AND description='" & Description & "' AND Nz(value, 0) > 0 "
Set rs = CurrentDb.OpenRecordset(strSelect, dbOpenSnapshot)
If rs.RecordCount > 0 Then
GetRepPersonalInfoEOM = Nz(rs!value)
End If
End If
End If
Exit_ErrorHandler:
Exit Function
ErrorHandler:
MsgBox "Error Number: " & Err.Number & vbCrLf & _
"Description: " & Err.Description & vbCrLf & _
"Function: GetRepPersonalInfoEOM "
Resume Exit_ErrorHandler
End Function
place the strSelect statement before the If then statement..
or use
select Case <variable>
case <value1>
'code here
case <value2>
'code here
end select
OK, that won't work work. I have to process two conditions before I get to the select statement. The first two descriptions are checks adn for each, a record count is done, only then does the select staetment get processed if the recordcound is 0 for the description. I cannot process the select first as it is really only if there is no recordcound for the description. What is happening, if there is a recorcount for the repid, then I need a value from TblIncentives for the return value, if there is no recordcount, then I need to return the value from the EmployeeInfo table back to the calling procedure.
Actually, I solved it by using some variables for the select statement columns. It is much neater, but thank you for your help
Public Function GetRepPersonalInfoEOM(ByVal RepID As Integer, ByVal EvalYear As Integer, ByVal EvalQuarter As Integer, ByVal Description As String) As String
On Error GoTo ErrorHandler
'Employee of the month is handled a little differently, first need to check to see if managers have inputted a
'value, it not, then do not bring in data as it may overwrite what has been entered from the Accountabilities side
Dim strSelect As String
Dim strEOM As String
Dim strCol As String
Dim rst As Recordset
Dim rs As Recordset
If Description = "Employee of the Month ($$$)" Then
strCol = "IncEOM"
ElseIf Description = "Employee of the Month Nominee ($$)" Then
strCol = "IncOther"
End If
strEOM = "SELECT TblIncentives." & strCol & " As EOM FROM (TblEmployees INNER JOIN TblIncentives ON " & _
"TblEmployees.EmpEmployeeID = TblIncentives.IncEmployeeID) " & _
"INNER JOIN Representative ON TblEmployees.EmpFRBEmployeeNumber = Representative.frb_employee_number " & _
"WHERE Representative.ID = " & RepID & " AND TblIncentives.IncYear = " & EvalYear & " AND " & _
"TblIncentives.IncQuarter = " & EvalQuarter & " AND TblIncentives." & strCol & " > 0 "
Set rst = CurrentDb.OpenRecordset(strEOM, dbOpenSnapshot)
If rst.RecordCount > 0 Then
GetRepPersonalInfoEOM = (rst!EOM)
Exit Function
Else
strSelect = "SELECT value " & _
"FROM EmployeeInfo " & _
"WHERE ID=" & RepID & " AND eval_quarter=" & EvalQuarter & " AND eval_year=" & EvalYear & " " & _
"AND description='" & Description & "' AND Nz(value, 0) > 0 "
Set rs = CurrentDb.OpenRecordset(strSelect, dbOpenSnapshot)
If rs.RecordCount > 0 Then
GetRepPersonalInfoEOM = Nz(rs!value)
Else
GetRepPersonalInfoEOM = ""
End If
End If
Set rst = Nothing
Set rs = Nothing
Exit_ErrorHandler:
Exit Function
ErrorHandler:
MsgBox "Error Number: " & Err.Number & vbCrLf & _
"Description: " & Err.Description & vbCrLf & _
"Function: GetRepPersonalInfoEOM "
Resume Exit_ErrorHandler
End Function
Premium Content
You need an Expert Office subscription to comment.Start Free Trial