Report_Open Problems

[tpoettker]

I have a report that uses an inputbox to request an event number. It then calls a stored procedure using the event_number as a parameter and brings back a recordset. I use the data to populate a table and run the report.

This works fine. The problem lies when after the table is populated and before the report displays, an inputbox is displayed asking for the event number again. This is not the same inputbox I created with code.

Below is the code. How can I get the second inputbox from appearing?

Option Compare Database
Option Explicit

Private Sub Report_Open(Cancel As Integer)
On Error GoTo Err_Run_Duplicate_Event_Report_Click

Dim stDocName As String
Dim cnn As New ADODB.Connection
Dim cmd1 As New ADODB.Command
Dim Adors As ADODB.Recordset
Dim sqltext As String
Dim db As Database
Dim recSet As Recordset
Dim Patient_DOB As Date
Dim CurDate As Date

Set db = CurrentDb()
Set recSet = db.OpenRecordset("tbl_BehaviorHealth")

sqltext = InputBox("Enter event number")
If sqltext <> "" Then
Set Adors = New ADODB.Recordset
' Open a connection using an ODBC DSN.
cnn.ConnectionString = "Provider=SQLOLEDB; Data Source=bjcsql04; Initial Catalog=RiskMaster;User Id=rmsysadm; Password=riskm1"
cnn.Open

cmd1.ActiveConnection = cnn
cmd1.CommandType = adCmdStoredProc
cmd1.CommandText = "BehaviorHealthRpt"
cmd1.Parameters(1) = sqltext
cmd1.CommandTimeout = 90
Set Adors = cmd1.Execute

Adors.MoveFirst

If recSet.RecordCount > 0 Then
recSet.Delete
End If

recSet.AddNew
recSet("Event_Nbr") = Adors(0)
recSet("Date_Of_Event_Month") = Right(Adors(10), 2)
recSet("Date_Of_Event_Day") = Mid(Adors(10), 5, 2)
recSet("Date_Of_Event_Year") = Left(Adors(10), 4)
recSet("Time_Of_Event_Hour") = Left(Adors(11), 2)
recSet("Time_Of_Event_Minute") = Right(Adors(11), 2)
recSet("EventLocation") = Adors(12)
recSet("MedicalRec") = Adors(9)
recSet("ReportedByName") = Adors(13)
recSet("ReportedByPhone") = Adors(14)
recSet("Event_Descript") = Adors(15)

If Adors(18) = "Male" Then
recSet("PIGenderMale") = 1
Else
recSet("PIGenderFemale") = 1
End If

Patient_DOB = DateSerial(Left(Adors(7), 4), Mid(Adors(7), 5, 2), Right(Adors(7), 2))
CurDate = Date
recSet("PIBirthAge") = DateDiff("yyyy", Patient_DOB, CurDate)

Select Case Adors(1)
Case "Accident"
recSet("Injury") = 1
recSet("Injury_Accident") = 1
Case "Other Inflicted"
recSet("Injury") = 1
recSet("Injury_OtherInflicted") = 1
Case "Self Inflicted"
recSet("Injury") = 1
recSet("Injury_ConsumerInflicted") = 1
recSet("Injury_SelfInflicted") = 1
Case "Staff Inflicted"
recSet("Injury") = 1
recSet("Injury_StaffInflicted") = 1
Case "Unknown"
recSet("Injury") = 1
recSet("Injury_Unknown") = 1
End Select

Select Case Adors(17)
Case "DE01", "DE02", "DE03", "DE04", "DE05"
recSet("ConsumerDeath") = 1
Select Case Adors(17)
Case "DE01"
recSet("AccidentDeath") = 1
Case "DE02"
recSet("Homicide") = 1
Case "DE03"
recSet("Natural") = 1
Case "DE04"
recSet("Suicide") = 1
Case "DE05"
recSet("Unknown") = 1
End Select

Case "BE08"
recSet("Elopement") = 1
Case "BE41", "BE42"
recSet("Abuse") = 1
recSet("VerbalAbuse") = 1
Case "BE30", "BE31"
recSet("Abuse") = 1
recSet("PhysicalAbuse") = 1
Case "BE32", "BE33"
recSet("Abuse") = 1
recSet("SexualAbuse") = 1
Case "BE44"
recSet("MisUseFunds") = 1
Case Else
recSet("Other") = 1
End Select

Do While Not Adors.EOF
Select Case Adors(4)
Case "Patient"
recSet("PatientName") = Adors(3) + " " + Adors(2)
Case "Witness"
If IsNull(recSet("WitnessFirstName1")) Then
recSet("WitnessFirstName1") = Adors(2)
recSet("WitnessLastName1") = Adors(3)
Else
If IsNull(recSet("WitnessFirstName2")) Then
recSet("WitnessFirstName2") = Adors(2)
recSet("WitnessLastName2") = Adors(3)
Else
If IsNull(recSet("WitnessFirstName3")) Then
recSet("WitnessFirstName3") = Adors(2)
recSet("WitnessLastName3") = Adors(3)
End If
End If
End If
Case "Other Person"
If Not IsNull(Adors(8)) Then
Select Case Adors(8)
Case "Family/Guardian"
recSet("Family") = 1
recSet("PN_FamilyName") = Adors(2) + " " + Adors(3)
Case "Physician"
recSet("Physician") = 1
recSet("PN_PhysicianName") = Adors(2) + " " + Adors(3)
Case "Law Enforcement"
recSet("LawEnforce") = 1
recSet("PN_LawName") = Adors(2) + " " + Adors(3)
Case "DSS-Children's Division"
recSet("DSS") = 1
recSet("PN_DSSName") = Adors(2) + " " + Adors(3)
Case "Division of Senior Services"
recSet("SeniorService") = 1
recSet("PN_SeniorServicesName") = Adors(2) + " " + Adors(3)
Case "Dept. of Mental Health"
recSet("MentalHealth") = 1
recSet("PN_MentalHealthName") = Adors(2) + " " + Adors(3)
Case "911"
recSet("Y911") = 1
recSet("PN_911Name") = Adors(2) + " " + Adors(3)
Case "Other"
recSet("Other1") = 1
recSet("PN_Other1Name") = Adors(2) + " " + Adors(3)
Case "Other2"
recSet("Other2") = 1
recSet("PN_Other2Name") = Adors(2) + " " + Adors(3)
Case "Other3"
recSet("Other3") = 1
recSet("PN_Other3Name") = Adors(2) + " " + Adors(3)
Case "Other4"
recSet("Other4") = 1
recSet("PN_Other4Name") = Adors(2) + " " + Adors(3)
End Select
Else
If Adors(6) = "OtherEmployee" Then
If IsNull(recSet("EmployeeName1")) Then
recSet("EmployeeName1") = Adors(2) + " " + Adors(3)
Else
If IsNull(recSet("EmployeeName2")) Then
recSet("EmployeeName2") = Adors(2) + " " + Adors(3)
Else
If IsNull(recSet("EmployeeName3")) Then
recSet("EmployeeName3") = Adors(2) + " " + Adors(3)
End If
End If
End If
End If
End If
End Select
Adors.MoveNext
Loop
recSet.Update
End If

Exit_Run_Duplicate_Event_Report_Click:
Exit Sub

Err_Run_Duplicate_Event_Report_Click:
MsgBox Err.Description
Resume Exit_Run_Duplicate_Event_Report_Click
End Sub

Private Sub Report_Page()

End Sub

 

[BuGlen]

I'm not sure what is causing the problem, but I do have a suggestion that will fix it. Rather than put all your code in the Report object, it might be better to create an input form for the user and use DoCmd.OpenReport to open the report after the table has been populated by the results from the stored procedure.

Sometimes object events don't occur as expected (especially when there is a data logic problem), so it's often preferable to keep the control of program flow through use of user forms and modules.

Hope this helps.

- Glen

Know thy data.