too few parameters error on module

[dixie617]

Need a little help figuring where my error is in this code. Please take a look, I can't see it.

this code finds the case that matches the criteria, then determines if the charge sentence is to run concurrently or consecutively, if concurrent give me the highest sentence of all the sentences for the case, if consecutive then add them to the concurrent sentence for a total years

Private Sub Report_Open(Cancel As Integer)
Dim TotalMonths As Long
Dim SubTotal As Long
Dim TotalDays As Long
Dim Sent As Long
Dim rstDefCharge As Recordset
Dim rstConCons As Recordset
Dim db As DAO.Database
Dim TotalYears As Integer

Set db = DBEngine(0)(0)
Set rstConCons = db.OpenRecordset("Select * from tblConcurentConsecutiveTable where PrimaryCaseNo = tblDefChargesSentence!CaseNo and ((tblConsecutiveTable!DefendantId) = '" & (DefendantId) & "')", dbOpenSnapshot)
Set rstDefCharge = db.OpenRecordset("Select * from tblDefChargesSentence where tblConcurentConsecutiveTable!PrimaryCaseNo = tblDefChargesSentence!CaseNo and ((tblConsecutiveTable!DefendantId) = '" & (DefendantId) & "'))", dbOpenSnapshot)
DoCmd.SetWarnings (off)
SubTotal = 0
With rstConCons
Do Until .EOF

If tblConcurentConsecutiveTable![ConConsecCaseNo] = tblDefChargesSentence![CaseNo] Then
If tblDefChargesSentence!ConcurrentSentence = True Then

TotalMonths = tblDefChargesSentence!SentYrs * 12
TotalDays = tblDefChargesSentence!SentDays / 30
If TotalMonths > tblDefChargesSentence!SentMos Then
If TotalMonths > TotalDays Then

Sent = TotalMonths / 12
If Sent > SubTotal Then
SubTotal = Sent
End If
End If
End If
End If
End If

.MoveNext
Loop

Do Until .EOF

If tblConcurentConsecutiveTable!ConConsecCaseNo = tblDefChargesSentence!CaseNo Then
If tblDefChargesSentence!ConsecutiveSentence = True Then

SubTotal = tblDefChargesSentence!SentYrs + SubTotal

End If
End If

.MoveNext
Loop

TotalYears = SubTotal
End With

End Sub

 

[PHV]

1) the SQL code is faulty as you reference tables not in the from clause
2) while browsing a recordset in VBA you have to references the fields objects of the rs, not the sql names.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[dixie617]

Ok, I think I got your meaning and sorta understand, I am new to VBA so it can get confusing. Here is what I did but it is still giving me the same error I also moved to the form that this report is created from but still no go:

Set db = DBEngine(0)(0)
Set rstConCons = db.OpenRecordset("Select * from tblConcurentConsecutiveTable where PrimaryCaseNo = Me!CaseNo and ((DefendantId) = '" & (Me!DefendantId) & "')", dbOpenSnapshot)
Set rstDefCharge = db.OpenRecordset("Select * from tblDefChargesSentence where CaseNo = Me!CaseNo and ((DefendantId) = '" & (Me!DefendantId) & "'))", dbOpenSnapshot)
DoCmd.SetWarnings (off)
SubTotal = 0
With rstConCons
Do Until .EOF

If rstConCons![ConConsecCaseNo] = rstDefCharge![CaseNo] Then
If rstDefCharge!ConcurrentSentence = True Then

TotalMonths = rstDefCharge!SentYrs * 12
TotalDays = rstDefCharge!SentDays / 30
If TotalMonths > rstDefCharge!SentMos Then
If TotalMonths > TotalDays Then

Sent = TotalMonths / 12
If Sent > SubTotal Then
SubTotal = Sent
End If
End If
End If
End If
End If

.MoveNext
Loop

Do Until .EOF

If rstConCons!ConConsecCaseNo = rstDefCharge!CaseNo Then
If rstDefCharge!ConsecutiveSentence = True Then

SubTotal = rstDefCharge!SentYrs + SubTotal

End If
End If

.MoveNext
Loop

TotalYears = SubTotal
End With

 

[PHV]

Replace this:
where PrimaryCaseNo = Me!CaseNo and
By this:
where PrimaryCaseNo = " & Me!CaseNo & " and
And this:
where CaseNo = Me!CaseNo and
By this:
where CaseNo = " & Me!CaseNo & " and

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[dixie617]

Changed that now, did not get that error again instead I am getting another error, syntax error (missing operator) in query expression PrimaryCaseNo = '02 28743 and (DefendantId) = '324'; I had someone else look at and they don't know either. Thanks for your help

 

[PHV]

where PrimaryCaseNo = [tt]'"[/tt] & Replace(Me!CaseNo[tt], "'", "''"[/tt]) & [tt]"'[/tt] and

Similar thing for rstDefCharge.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[dixie617]

What does Replace do? or are you telling to replace what is there?

 

[dixie617]

This is what I did:
Set rstConCons = db.OpenRecordset("Select * from tblConcurentConsecutiveTable where PrimaryCaseNo = '" & Replace(Me!CaseNo, "'", "''") & " and ((DefendantId) = " & Str(Me!DefendantId) & ")", dbOpenSnapshot)

Set rstDefCharge = db.OpenRecordset("Select * from tblDefChargesSentence where CaseNo = '" & Replace(Me!CaseNo, "'", "''") & "' and ((DefendantId) = '" & Str(Me!DefendantId) & "'))", dbOpenSnapshot)

Now get this error: syntax error in string expression 'PrimaryCaseNo = '02 218473' and ((DefendantId) = 324)'

The caseno field is a text field and the defendantId field is a numeric field Thanks for all your help, I am learning quickly here.

 

[dixie617]

I figured it out, I was missing a couple of ' in the code, it goes through the code now with no errors. Another question, How do I get the results to display on a report that has an unbound text field named TotalYears? The report is called UCD1 and the subreport that actually displays the TotalYears is called SentenceDocTest. Do I need to put the code on the report itself?

 

[dixie617]

I have another question, you have been so helpful already, I have the code working and it does give me the correct number and displays that number on the report, but only for concurrent sentences, it does not add the sentences together for the consecutive. Any Ideas? Here is the code how it looks now:

Private Sub cmdUCD_Click()
Dim TotalMonths As Long
Dim SubTotal As Long
Dim TotalDays As Long
Dim Sent As Long
Dim rstDefCharge As Recordset
Dim rstConCons As Recordset
Dim db As DAO.Database

Set db = DBEngine(0)(0)
Set rstConCons = db.OpenRecordset("Select * from tblConcurentConsecutiveTable where PrimaryCaseNo = '" & Replace(Me!CaseNo, "'", "''") & "' and ((DefendantId) = " & Str(Me!DefendantId) & ")", dbOpenSnapshot)
Set rstDefCharge = db.OpenRecordset("Select * from tblDefChargesSentence where CaseNo = '" & Replace(Me!CaseNo, "'", "''") & "' and ((DefendantId) = " & Str(Me!DefendantId) & ")", dbOpenSnapshot)

SubTotal = 0
With rstConCons
Do Until .EOF

If rstConCons![ConConsecCaseNo] = rstDefCharge![CaseNo] Then
If rstDefCharge!ConcurrentSentence = Yes Then

Sent = rstDefCharge!SentYrs
If Sent > SubTotal Then
SubTotal = Sent
End If
Else
If rstDefCharge!ConsecutiveSentence = Yes Then

SubTotal = rstDefCharge!SentYrs + SubTotal
End If
End If
End If

.MoveNext
Loop

Me!TotalYears = SubTotal
End With


End Sub