Too Few Parameters Error

[Chris]

The following is a sub I created. I am getting an error on
the Select Statement I am using that stats "Too Few
Parameters. Expected 1."

As far as I can tell my Select statement has the correct
sytax and when I run the SQL Select statement in a regular
query window it works fine.

Any help is appreciated.

Thanks,

-Chris

Sub Compare_Loan()

Dim D As Database, R As Form, T As Recordset, C As
Recordset
Dim strHud As Integer
Dim strIED As Integer
Dim strHID As Integer
Dim strFID As Integer
Dim strTax As Integer
Dim strFull As Integer
Dim strMessage As Variant
Dim strTotal As Integer

Set D = CurrentDb
'Set R = [Forms]![frmMain]
Set R = [Forms]![frmMain]
Set T = D.OpenRecordset("tblCompare")


DoCmd.SetWarnings False
DoCmd.OpenQuery ("qryDelCompare")
DoCmd.SetWarnings True

T.AddNew
T![Loan_Num] = R![Loan_Number]
T.Update

strHud = 0
strIED = 0
strHID = 0
strFID = 0
strTax = 0
strFull = 0

Set C = D.OpenRecordset("Select * from [tblMain] Where
[Loan_Number] = R![Loan_Number]);", dbOpenSnapshot)


If C![Hud-1] = True Then
strHud = strHud + 1
If C![Initial_Escrow_Disclosure] = True Then
strIED = strIED + 1
If C![Hazard_Insurance_Declaration] = True Then
strHID = strHID + 1
If C![Flood_Insurance_Declaration] = True Then
strFID = strFID + 1
If C![Tax_Certification] = True Then
strTax = strTax + 1
If C![Full_File] = True Then
strFull = strFull + 1

End If
End If
End If
End If
End If
End If


strTotal = strHud + strIED + strHID + strFID + strTax +
strFull

If strTotal > 0 Then

If strHud > 0 Then
strMessage = strMessage + "Hud-1 "
If strIED > 0 Then
strMessage = strMessage + "Initial Escrow Disclosure "
If strHID > 0 Then
strMessage = strMessage + "Hazard Insurance
Declaration "
If strFID > 0 Then
strMessage = strMessage + "Flood Insurance
Declaration "
If strTax > 0 Then
strMessage = strMessage + "Tax Certification "
If strFull > 0 Then
strMessage = strMessage + "Full File "
End If
End If
End If
End If
End If
End If
End If


Dim UserSelection1

UserSelection1 = MsgBox(("The Documents you Requested " &
strMessage & " Have Been Previously Requested and are in
the File Cabinet. Do you want to re-request them?"),
vbYesNo)

Select Case UserSelection1
Case 6 'if yes is selected
GoTo Continue
Case 7 'if no is selected
DoCmd.Quit
End Select


Continue:
Exit Sub

End Sub

 

[Chris Nebinger]

Set C = D.OpenRecordset("Select * from [tblMain] Where
[Loan_Number] =" & R![Loan_Number], dbOpenSnapshot)



Chris Nebinger

 

[Joe Fallon]

Even if you get the syntax right, you may still have the same error.

Access can figure out what the parameter is when the query is run in the
grid. Then Access will inform the Jet Engine what it is.

However, when the same query is run in code, you must tell the Jet engine
what the
parameter is yourself.

This is the slickest way to do it:

With queries that contain parameters that are all references to
controls on open forms, you can simulate the expression service that Access
provides when the queries are run through the user interface, as follows:

Set db = CurrentDb
Set qdf = db.QueryDefs("MyQuery")
Dim prm As Parameter

For Each prm In qdf.Parameters
prm.Value = Eval(prm.Name)
Next prm

Set rs = qdf.OpenRecordset(dbOpenDynaset)
' or qdf.Execute dbFailOnError

--
Joe Fallon
Access MVP

Set C = D.OpenRecordset("Select * from [tblMain] Where
[Loan_Number] =" & R![Loan_Number], dbOpenSnapshot)



Chris Nebinger

-----Original Message-----
The following is a sub I created. I am getting an error on
the Select Statement I am using that stats "Too Few
Parameters. Expected 1."

As far as I can tell my Select statement has the correct
sytax and when I run the SQL Select statement in a regular
query window it works fine.

Any help is appreciated.

Thanks,

-Chris

Sub Compare_Loan()

Dim D As Database, R As Form, T As Recordset, C As
Recordset
Dim strHud As Integer
Dim strIED As Integer
Dim strHID As Integer
Dim strFID As Integer
Dim strTax As Integer
Dim strFull As Integer
Dim strMessage As Variant
Dim strTotal As Integer

Set D = CurrentDb
'Set R = [Forms]![frmMain]
Set R = [Forms]![frmMain]
Set T = D.OpenRecordset("tblCompare")


DoCmd.SetWarnings False
DoCmd.OpenQuery ("qryDelCompare")
DoCmd.SetWarnings True

T.AddNew
T![Loan_Num] = R![Loan_Number]
T.Update

strHud = 0
strIED = 0
strHID = 0
strFID = 0
strTax = 0
strFull = 0

Set C = D.OpenRecordset("Select * from [tblMain] Where
[Loan_Number] = R![Loan_Number]);", dbOpenSnapshot)


If C![Hud-1] = True Then
strHud = strHud + 1
If C![Initial_Escrow_Disclosure] = True Then
strIED = strIED + 1
If C![Hazard_Insurance_Declaration] = True Then
strHID = strHID + 1
If C![Flood_Insurance_Declaration] = True Then
strFID = strFID + 1
If C![Tax_Certification] = True Then
strTax = strTax + 1
If C![Full_File] = True Then
strFull = strFull + 1

End If
End If
End If
End If
End If
End If


strTotal = strHud + strIED + strHID + strFID + strTax +
strFull

If strTotal > 0 Then

If strHud > 0 Then
strMessage = strMessage + "Hud-1 "
If strIED > 0 Then
strMessage = strMessage + "Initial Escrow Disclosure "
If strHID > 0 Then
strMessage = strMessage + "Hazard Insurance
Declaration "
If strFID > 0 Then
strMessage = strMessage + "Flood Insurance
Declaration "
If strTax > 0 Then
strMessage = strMessage + "Tax Certification "
If strFull > 0 Then
strMessage = strMessage + "Full File "
End If
End If
End If
End If
End If
End If
End If


Dim UserSelection1

UserSelection1 = MsgBox(("The Documents you Requested " &
strMessage & " Have Been Previously Requested and are in
the File Cabinet. Do you want to re-request them?"),
vbYesNo)

Select Case UserSelection1
Case 6 'if yes is selected
GoTo Continue
Case 7 'if no is selected
DoCmd.Quit
End Select


Continue:
Exit Sub

End Sub

.