I received the error "Rowset position cannot be restarted" on the adors.movefirst line of code. Here is the code in it's entirity:
Private Sub cmdOK_Click()
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 DAO.Database
Dim recSet As DAO.Recordset
Dim Patient_DOB As Date
Dim CurDate As Date
Set db = CurrentDb()
Set recSet = db.OpenRecordset("tbl_BehaviorHealth")
sqltext = txtEventNumber
If sqltext <> "" Then
' 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 = New ADODB.Recordset
Set Adors = cmd1.Execute
If Adors.EOF = True Then
MsgBox "That event number does not exist in RiskMaster"
Exit Sub
Adors.Close
End If
Do While Adors.EOF = False
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
If Not IsNull(Adors(17)) Then
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
End If
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
recSet.Update
Loop
stDocName = "rpt_BehaviorHealth"
DoCmd.OpenReport stDocName, acViewPreview
Loop
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