Joseph,
A command was used to create this report within a form.
Do you think this is the root of the problem? Someone
developed the database for us. Below is the event
procedure for the command button (Report). Can you help?
Option Compare Database
Option Explicit
Dim num As String * 4
Dim caseNumber As String * 12
Dim lastName As String * 16
Dim id As String
'Dim sanctionNumber As String * 12
Dim sanctionAction As String * 29
Dim sanctionDue As String
Dim printState(1 To 4, 1 To 2) As String
Dim objAll As Database
Dim objSanction(1 To 4) As Recordset
Dim totalUncom%
Dim year As String
Dim month As String
Dim qq As Date
Dim sqlDate As String
Dim sanctionNum%
Dim tempstring1$, tempstring2$
Dim total%
Private Sub cmdReport_click()
Forms!sanctionscompliance!cmbYear.SetFocus
year = cmbYear.Text
Forms!sanctionscompliance!cmbMonth.SetFocus
month = cmbMonth.Text
If (year = "") Or (month = "") Then
MsgBox "Please choose the year and month
first", , "Warning!"
Else
initialize
Call createRecord(year, month)
For sanctionNum = 1 To 4
totalUncom = 0
Do While objSanction(sanctionNum).EOF <> True
If (IsNull(objSanction(sanctionNum).Fields
(4))) Then '_ '(objSanction.Fields("Sanction 2") Like
sqlDate) And
totalUncom = totalUncom + 1
End If
objSanction(sanctionNum).MoveNext
Loop
Next sanctionNum
For sanctionNum = 1 To 4
totalUncom = 0
tempstring1 = ""
tempstring2 = ""
If objSanction(sanctionNum).BOF <> True Then
objSanction(sanctionNum).MoveFirst
Do While objSanction(sanctionNum).EOF <>
True
If (IsNull(objSanction
(sanctionNum).Fields(4))) Then
totalUncom = totalUncom + 1
num = totalUncom & ".__________"
caseNumber = objSanction
(sanctionNum).Fields("Case#") & "______________"
lastName = objSanction
(sanctionNum).Fields("Last Name") & "_______________"
id = objSanction
(sanctionNum).Fields("AUID")
sanctionAction = objSanction
(sanctionNum).Fields(2) & "___________________"
sanctionDue = objSanction
(sanctionNum).Fields(3)
tempstring1 = tempstring1 & num &
caseNumber & lastName & id & vbCrLf
tempstring2 = tempstring2 &
sanctionAction & sanctionDue & vbCrLf
End If
objSanction(sanctionNum).MoveNext
Loop
printState(sanctionNum, 1) = tempstring1
printState(sanctionNum, 2) = tempstring2
End If
Select Case sanctionNum
Case 1
Forms!sanctionscompliance!
txtSanction1a.SetFocus
txtSanction1a.Text = tempstring1
Forms!sanctionscompliance!
txtSanction1b.SetFocus
txtSanction1b.Text = tempstring2
Case 2
Forms!sanctionscompliance!
txtsanction2a.SetFocus
txtsanction2a.Text = tempstring1
Forms!sanctionscompliance!
txtSanction2b.SetFocus
txtSanction2b.Text = tempstring2
Case 3
Forms!sanctionscompliance!
txtsanction3a.SetFocus
txtsanction3a.Text = tempstring1
Forms!sanctionscompliance!
txtSanction3b.SetFocus
txtSanction3b.Text = tempstring2
Case 4
Forms!sanctionscompliance!
txtsanction4a.SetFocus
txtsanction4a.Text = tempstring1
Forms!sanctionscompliance!
txtSanction4b.SetFocus
txtSanction4b.Text = tempstring2
End Select
total = total + totalUncom
Next sanctionNum
Forms!sanctionscompliance!txtTry.SetFocus
txtTry.Text = total
End If
End Sub
Private Sub initialize()
Dim tempNum
For tempNum = 1 To 4
printState(tempNum, 1) = ""
printState(tempNum, 2) = ""
Next tempNum
Forms!sanctionscompliance!txtTry.SetFocus
txtTry.Text = ""
Forms!sanctionscompliance!txtSanction1a.SetFocus
txtSanction1a.Text = ""
Forms!sanctionscompliance!txtsanction2a.SetFocus
txtsanction2a.Text = ""
Forms!sanctionscompliance!txtsanction3a.SetFocus
txtsanction3a.Text = ""
Forms!sanctionscompliance!txtsanction4a.SetFocus
txtsanction4a.Text = ""
Forms!sanctionscompliance!txtSanction1b.SetFocus
txtSanction1b.Text = ""
Forms!sanctionscompliance!txtSanction2b.SetFocus
txtSanction2b.Text = ""
Forms!sanctionscompliance!txtSanction3b.SetFocus
txtSanction3b.Text = ""
Forms!sanctionscompliance!txtSanction4b.SetFocus
txtSanction4b.Text = ""
total = 0
End Sub
Private Sub createRecord(year As String, month As String)
Dim sqlState As String
sqlDate = "'" & month & "/*/" & year & "'"
sqlState = "SELECT [Conference/Hearing].[Case#],
[Conference/Hearing].AUID," & _
" [Conference/Hearing].[Sanction 2],
[Conference/Hearing].[Santion 2 Due Date]," & _
" [Conference/Hearing].[Sanction 2 Date
Completed], PeopleInfo.[Last Name]" & _
" FROM PeopleInfo INNER JOIN
([Conference/Hearing] INNER JOIN Incident" & _
" ON (Incident.[Case#] = [Conference/Hearing].
[Case#]) AND" & _
" ([Conference/Hearing].AUID = Incident.AUID)
AND" & _
" ([Conference/Hearing].AUID = Incident.AUID)
AND" & _
" ([Conference/Hearing].[Case#] = Incident.
[Case#])) ON" & _
" ([Conference/Hearing].AUID =
PeopleInfo.AUID) AND" & _
" (PeopleInfo.AUID = Incident.AUID)" & _
" WHERE ((([Conference/Hearing].[Sanctions?])
= Yes) AND" & _
" (([Conference/Hearing].[Santion 2 Due Date])
Like " & sqlDate & ")" & _
" AND (([Conference/Hearing].[All Sanctions
Completed])=No))" & _
" ORDER BY [Conference/Hearing].[Santion 2
Due Date]"
Set objAll = CurrentDb
Set objSanction(1) = objAll.OpenRecordset(sqlState,
dbOpenDynaset)
sqlState = "SELECT [Conference/Hearing].[Case#],
[Conference/Hearing].AUID," & _
" [Conference/Hearing].[Sanction 3],
[Conference/Hearing].[Sanction 3 Due Date]," & _
" [Conference/Hearing].[Sanction 3 Date
Completed], PeopleInfo.[Last Name]" & _
" FROM PeopleInfo INNER JOIN
([Conference/Hearing] INNER JOIN Incident" & _
" ON (Incident.[Case#] = [Conference/Hearing].
[Case#]) AND" & _
" ([Conference/Hearing].AUID = Incident.AUID)
AND" & _
" ([Conference/Hearing].AUID = Incident.AUID)
AND" & _
" ([Conference/Hearing].[Case#] = Incident.
[Case#])) ON" & _
" ([Conference/Hearing].AUID =
PeopleInfo.AUID) AND" & _
" (PeopleInfo.AUID = Incident.AUID)" & _
" WHERE ((([Conference/Hearing].[Sanctions?])
= Yes) AND" & _
" (([Conference/Hearing].[Sanction 3 Due
Date])Like " & sqlDate & ")" & _
" AND (([Conference/Hearing].[All Sanctions
Completed])=No))" & _
" ORDER BY [Conference/Hearing].[Sanction 3
Due Date]"
Set objAll = CurrentDb
Set objSanction(2) = objAll.OpenRecordset(sqlState,
dbOpenDynaset)
sqlState = "SELECT [Conference/Hearing].[Case#],
[Conference/Hearing].AUID," & _
" [Conference/Hearing].[Sanction 4],
[Conference/Hearing].[Sanction 4 Due Date]," & _
" [Conference/Hearing].[Sanction 4 Date
Completed], PeopleInfo.[Last Name]" & _
" FROM PeopleInfo INNER JOIN
([Conference/Hearing] INNER JOIN Incident" & _
" ON (Incident.[Case#] = [Conference/Hearing].
[Case#]) AND" & _
" ([Conference/Hearing].AUID = Incident.AUID)
AND" & _
" ([Conference/Hearing].AUID = Incident.AUID)
AND" & _
" ([Conference/Hearing].[Case#] = Incident.
[Case#])) ON" & _
" ([Conference/Hearing].AUID =
PeopleInfo.AUID) AND" & _
" (PeopleInfo.AUID = Incident.AUID)" & _
" WHERE ((([Conference/Hearing].[Sanctions?])
= Yes) AND" & _
" (([Conference/Hearing].[Sanction 4 Due
Date])Like " & sqlDate & ")" & _
" AND (([Conference/Hearing].[All Sanctions
Completed])=No))" & _
" ORDER BY [Conference/Hearing].[Sanction 4
Due Date]"
Set objAll = CurrentDb
Set objSanction(3) = objAll.OpenRecordset(sqlState,
dbOpenDynaset)
sqlState = "SELECT [Conference/Hearing].[Case#],
[Conference/Hearing].AUID," & _
" [Conference/Hearing].[Sanction 5],
[Conference/Hearing].[Sanction 5 Due Date]," & _
" [Conference/Hearing].[Sanction 5 Date
Completed], PeopleInfo.[Last Name]" & _
" FROM PeopleInfo INNER JOIN
([Conference/Hearing] INNER JOIN Incident" & _
" ON (Incident.[Case#] = [Conference/Hearing].
[Case#]) AND" & _
" ([Conference/Hearing].AUID = Incident.AUID)
AND" & _
" ([Conference/Hearing].AUID = Incident.AUID)
AND" & _
" ([Conference/Hearing].[Case#] = Incident.
[Case#])) ON" & _
" ([Conference/Hearing].AUID =
PeopleInfo.AUID) AND" & _
" (PeopleInfo.AUID = Incident.AUID)" & _
" WHERE ((([Conference/Hearing].[Sanctions?])
= Yes) AND" & _
" (([Conference/Hearing].[Sanction 5 Due
Date])Like " & sqlDate & ")" & _
" AND (([Conference/Hearing].[All Sanctions
Completed])=No))" & _
" ORDER BY [Conference/Hearing].[Sanction 5
Due Date]"
Set objAll = CurrentDb
Set objSanction(4) = objAll.OpenRecordset(sqlState,
dbOpenDynaset)
End Sub
