Error with recordset code

[tpoettker]

Hi,

I am having problems with my code in MS Access. I receive an error that says "arguments are of the wrong type, are out of acceptance range, or are in conflict with one another".

Here's the code. It errors on the last line shown here:

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 = 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
Adors.Open cmd1.Execute, cnn, adOpenDynamic, adLockOptimistic

What's wrong?

Tina

 

[CajunCenturion]

Since you're using both DAO and ADO, I would remove any ambiguity between the two.

Dim db As DAO.Database
Dim recSet As DAO.Recordset

Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[tpoettker]

I made the changes (thank you for pointing that out and still received the errors.

 

[cmmrfrds]

What are you doing here?

Adors.Open cmd1.Execute, cnn, adOpenDynamic, adLockOptimistic

The open method takes a table name or sql string.

To put the resultset from the stored procedure in the recordset.
Set Adors = cmd1.Execute

 

[CajunCenturion]

On which line are you getting that error?

Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[tpoettker]

I had original coded it that way. I then receive the error "Rowset position cannot be restarted" when I try to MoveFirst the first record. Here's the code that follows:

Set Adors = cmd1.Execute

Do Until Adors.EOF = False
MsgBox "That event number does not exist in RiskMaster"
Exit Sub
Adors.Close
Loop

Adors.MoveFirst

It skips through the Do Unitl loop as long as it finds the event number and then errors on Adors.MoveFirst.

 

[tpoettker]

the first error I received (arguments....) errored on
Adors.Open cmd1.Execute, cnn, adOpenDynamic, adLockOptimistic

The second error was received on
Adors.movefirst

 

[cmmrfrds]

Set Adors = cmd1.Execute

Do Until Adors.EOF = False
MsgBox "That event number does not exist in RiskMaster"
Exit Sub
Adors.Close
Loop

The recordset is closed here so it will error.
Adors.MoveFirst

Set Adors = cmd1.Execute
If Adors.EOF = true then
msgbox "no records"
exit sub
End if
Do Until Adors.EOF = False
"put your processing for each record here"
Loop
Adors.Close

 

[tpoettker]

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

 

[cmmrfrds]

Put the Adors.MoveFirst before you start the loop.

The error is saying that the recordset cannot start over which means it is a forward only dataset. This should be fine unless for some reason you want to start at the first record again.

 

[tpoettker]

still errored. Same line of command (ADOrs.movefirst) and same error (rowset position cannot be restarted).

 

[cmmrfrds]

If you really want to start over at this point then the recordset must be scrollable.
Do While Adors.EOF = False
Adors.MoveFirst

To make a scrollable recordset try.
Set Adors = New ADODB.Recordset
set Adors.CursorLocation = adUseClient
set adors.LockType = adLockOptimistic
set adors.CursorType = adOpenStatic

Set Adors = cmd1.Execute

 

[tpoettker]

I had to remove "set" from all the statements. The "set" caused the error "invalid use of property". After I removed the "set", it ran but with the same error: "Rowset cannot be restarted".

 

[cmmrfrds]

Have you put in any debug statements, say before the movefirst to see whats in the recordset.

Do While Adors.EOF = False
'you may need to experiment - short example. Could loop through the various recordset collections at this point including Errors collection, also the properties collection to see info. about the recordset.
Debug.print "adors = " adors
Debug.print "adors rec = " adors(0)
Adors.MoveFirst

When it errors then do a "Control G" to see what gets printed in the debug window.