Private Sub PrintDebreifingsCR()
Dim rs As Recordset 'Record set to load cboUserName data
Dim strTypeConnection As String 'Connection type (i.e. SQL -1)
Dim strDataBaseName As String 'Database Name
Dim strDBName As String 'Sets Database Name
Dim strDBCursorType As String 'Cursor (Dynamic, Forward Only, Keyset, Static)
Dim strDBLockType As String 'Locks (BatchOptimistic,Optimistic,Pessimistic
'Read Only)
Dim strDBOptions As String 'DB Options
Dim UserActionText As String
Dim strSQL As String
Dim CommandCount As Long
Dim InvUnit As String
Dim Debrief_Count As String
Dim Debrief_Max As String
Dim SearchYear As String
SearchYear = ""
SearchYear = CStr(Me.Text6.Text)
strTypeConnection = "5" 'SQL Database
strDataBaseName = "Debrief" 'Database Connection String
strDBCursorType = adOpenDynamic 'CursorType
strDBLockType = adLockOptimistic 'LockType
strDBOptions = adCmdText 'Options
Set rs = New Recordset 'Creates record set
strSQL = "SELECT tblIABCommands.Inv_Unit, "
strSQL = strSQL & "COUNT(tblDebriefing.Debrief_Num) AS UnboundString1, "
strSQL = strSQL & "MAX(tblDebriefing.Debrief_Num) As Debrief_Max, "
strSQL = strSQL & "SUBSTRING(tblDebriefing.Debrief_Num, 1, 4) "
strSQL = strSQL & "FROM tblDebriefing RIGHT OUTER JOIN tblIABCommands ON "
strSQL = strSQL & "tblDebriefing.Inv_Unit = tblIABCommands.Inv_Unit "
strSQL = strSQL & "GROUP BY tblIABCommands.Inv_Unit, tblIABCommands.Sort2, (SUBSTRING(tblDebriefing.Debrief_Num, 1, 4)) "
strSQL = strSQL & "HAVING (tblIABCommands.Inv_Unit LIKE N'Group%') AND "
strSQL = strSQL & "(SUBSTRING(tblDebriefing.Debrief_Num, 1, 4) = '" & SearchYear & "') "
strSQL = strSQL & "ORDER BY tblIABCommands.Sort2;"
Set rs = New Recordset 'Creates record set
rs.Open strSQL, _
ConnectString(strTypeConnection, strDataBaseName), _
strDBCursorType, _
strDBLockType, _
strDBOptions
If rs.EOF = True Then
MsgBox "There are no Debriefing records for Calendar Year - " & SearchYear, vbCritical, "No Debriefing Data - " & SearchYear
Else
With CrystalReport1
.ReportFileName = App.Path & "\CrystalReport1.rpt"
.DiscardSavedData = True
.RetrieveDataFiles
.ReportSource = 0
.ReportTitle = "Employee Details Report"
.Destination = crptToWindow
.PrintFileType = crptCrystal
.WindowState = crptMaximized
.WindowMaxButton = False
.WindowMinButton = False
End With
End If
rs.Close
Set rs = Nothing
