Too Few Parameters error

[Christine Vollberg via AccessMonster.com]

Need help with a recordset error, may be going about this wrong but here is
what I need it to do, find all the records for a given defendant that are
concurrent and then give me the max of those records, then find any that
are consecutive and add it to that max. Then that number populates a text
box on my report. It will not do a thing but give this error of too few
parameters. Sorry for the length I wanted you to see the whole module.

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
'Dim sSQL As String

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

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

 

[Guest]

Sorry, syntax was incorrect -should be
docmd.SetWarnings(off)

 

[Christine Vollberg via AccessMonster.com]

What does turning off the warning to do with a parameter error?

 

[Tim Ferguson]

It will not do a thing but give this error of too few
parameters.

Too Few Parameters is practically always a symptom of a misspelled field
or function in the query. Do you get the right answers when you run the
query in the query designer?

Looking at your SQL, there are some obvious problems:

"Select *
from tblConcurentConsecutiveTable
where PrimaryCaseNo = tblDefChargesSentence!CaseNo"


"Select *
from tblDefChargesSentence
where tblConcurentConsecutiveTable!PrimaryCaseNo =
tblDefChargesSentence!CaseNo
and tblConsecutiveTable!DefendantId = DefendantId"


First of all, SQL does not recognise a ! notation;

Second, the tblDefChargesSentence is not referenced anywhere -- I guess
you forgot to add in a joined table in the FROM clause.

The best advice would be to get the whole query right in the query
designer first, using the grid, and then copy the SQL view into the VBA
(or just use the saved query itself, which might run faster by a matter
of microseconds).

Hope that helps


Tim F

 

[John Nurick]

Set rstConCons = db.OpenRecordset("Select * from
tblConcurentConsecutiveTable where PrimaryCaseNo = tblDefChargesSentence!
CaseNo", dbOpenSnapshot)

This looks wonky. Is tblDefChargesSentence a table or a form? What are
you expecting
tblDefChargesSentence!CaseNo
to give you?

If it's a form, the Formname!Controlname syntax isn't available in SQL
statements you pass to OpenRecordset, so you need to insert the actual
values you need. If it's a form, try something like this (assuming that
the case number is a text field, omit the apostrophes if it isn't):

Set rstConCons = db.OpenRecordset( _
"SELECT * FROM tblConcurentConsecutiveTable " _
& "WHERE PrimaryCaseNo = '" _
& Forms("tblDefChargesSentence").Controls("CaseNo") _
& "';", dbOpenSnapshot)

If it's a table, you'll need to join the two tables in the query.

The subsequent SQL statement has similar problems.

 

[Guest]

Absolutely nothing. I don't know how my response got attached to this
thread. This response was to a wholly different question.

 

[Christine Vollberg via AccessMonster.com]

I should never has posted that code, I have fixed and used the correct
syntax thanks for the help.