Run-time error '3061' Too few parameters. 1

[skabutch]

I am using MS Access 2003 and attempting to create a search form. I am a real greenhorn with vba code and so I found code from a book and cd I purchased. I copied the code and attempted to modify it for my needs. However, when I run the code I receive the visual basic error message:
Run-time error '3061 Too few parameters. Expected 1 and I am thrown into debug mode which goes to the following vba code line:

'Open a recordset to see if any rows returned with this filter
Set rst = DBEngine(0)(0).OpenRecordset("SELECT * FROM qryComparablesDrillDown WHERE " & varWhere)
' See if found none
If rst.RecordCount = 0 Then
MsgBox "No Contacts meet your criteria.", vbInformation, gstrAppTitle
'Clean up recordset
rst.Close
Set rst = Nothing
Exit Sub
End If

qryComparablesDrillDown is a query that has no specified parameters to provide, so I am completely stumped. I realize I have not provided enough information to solve the problem, but any advice or clues would be much appreciated.

Thank you in advance.

 

[PHV]

What is the value of varWhere ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Gruuuu]

This usually indicates a typo in the SQL. Check your varWhere value. You might by missing single quotes around your search value, or misspelled a field name

 

[skabutch]

Thanks for both of your thoughtful responses. I will review the code for typos.

In regard to the question of the value of varWhere. As the filter is being built, the criterion are stored in varWhere.

 

[PHV]

the criterion are stored in varWhere
So, what is the result of:
MsgBox varWhere

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[skabutch]

PHV,
My apologies, I don't quite understand the question, but below is the code associated with the Search button.

Private Sub cmdSearch_Click()
Dim varWhere As Variant, varDateSearch As Variant
Dim rst As DAO.Recordset

' Initialize to Null
varWhere = Null
varDateSearch = Null

' First, validate the dates
' If there's something in Contact Date From
If Not IsNothing(Me.txtTranDateStart) Then
' First, make sure it's a valid date
If Not IsDate(Me.txtTranDateStart) Then
' Nope, warn them and bail
MsgBox "The value in Contact From is not a valid date.", vbCritical, gstrAppTitle
Exit Sub
End If
' Now see if they specified a "to" date
If Not IsNothing(Me.txtTranDateEnd) Then
' First, make sure it's a valid date
If Not IsDate(Me.txtTranDateEnd) Then
' Nope, warn them and bail
MsgBox "The value in Contact To is not a valid date.", vbCritical, gstrAppTitle
Exit Sub
End If
' Got two dates, now make sure "to" is >= "from"
If Me.txtTranDateEnd < Me.txtTranDateStart Then
MsgBox "Contact To date must be greater than or equal to Contact From date.", _
vbCritical, gstrAppTitle
Exit Sub
End If
End If
Else
' No "from" but did they specify a "to"?
If Not IsNothing(Me.txtTranDateEnd) Then
' Make sure it's a valid date
If Not IsDate(Me.txtTranDateEnd) Then
' Nope, warn them and bail
MsgBox "The value in Contact To is not a valid date.", vbCritical, gstrAppTitle
Exit Sub
End If
End If
End If

' OK, start building the filter

' If specified a sold or leased value
If Not IsNothing(Me.cmdSoldorLeased) Then
' .. build the predicate
varWhere = "[SoldorLeased?] = '" & Me.cmdSoldorLeased & "'"
End If


' If specified a property city value
If Not IsNothing(Me.cmbPropertyCity) Then
' .. build the predicate
varWhere = (varWhere + " AND ") & "[PropertyCity] LIKE '" & Me.cmbPropertyCity & "'"
End If


' Do Property Type next
If Not IsNothing(Me.cmbPropertyType) Then
' .. build the predicate
' Note: taking advantage of Null propogation
' so we don't have to test for any previous predicate
varWhere = (varWhere + " AND ") & "[PropertyType] LIKE '" & Me.cmbPropertyType & "*'"
End If


' Check Transaction Date From first
If Not IsNothing(Me.txtTranDateStart) Then
' .. build the predicate
varDateSearch = "tblComparables.Transaction Date >= #" & Me.txtTranDateStart & "#"
End If
' Now do Transaction Date To
If Not IsNothing(Me.txtTranDateEnd) Then
' .. add to the predicate, but add one because ContactDateTime includes
' a date AND a time
varDateSearch = (varDateSearch + " AND ") & _
"tblComparables.Transaction Date < #" & CDate(Me.txtTranDateEnd) + 1 & "#"
End If

' Did we build any date filter?
If Not IsNothing(varDateSearch) Then
' OK, add to the overall filter
' Must use a subquery here because the value is in a linking table...
varWhere = (varWhere + " AND ") & _
"[PropID] IN (SELECT PropID FROM qryComparablesDrillDown " & _
"WHERE " & varDateSearch & ")"
End If


' Check to see that we built a filter
If IsNothing(varWhere) Then
MsgBox "You must enter at least one search criteria.", vbInformation, gstrAppTitle
Exit Sub
End If

'Open a recordset to see if any rows returned with this filter
'Set rst = DBEngine(0)(0).OpenRecordset("SELECT * FROM qryComparablesDrillDown WHERE " & varWhere)
' See if found none
'If rst.RecordCount = 0 Then
'MsgBox "No Contacts meet your criteria.", vbInformation, gstrAppTitle
'Clean up recordset
'rst.Close
'Set rst = Nothing
'Exit Sub
'End If

' Hide me to fix later focus problems
Me.Visible = False
' Move to last to find out how many
'rst.MoveLast

' Open Comparables filtered
' Note: if form already open, this just applies the filter
DoCmd.OpenForm "frmComparablesDrillDown", WhereCondition:=varWhere
' Make sure focus is on contacts
Forms!frmComparablesDrillDown.SetFocus

' Done
DoCmd.Close acForm, Me.Name
' Clean up recordset
'rst.Close
'Set rst = Nothing


End Sub

 

[PHV]

Replace this:
"tblComparables.Transaction Date
with this (at least 2 times)
"tblComparables.[Transaction Date]

' Must use a subquery here because the value is in a linking table...
Really ?
Can't you join on PropID ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[skabutch]

PHV,
I took your suggestion and replaced the "tblComparables.Transaction Date with "tblComparables.[Transaction Date] and that resolved the error in question. I will also try the join on PropID as you suggested. Thank you so very much for your advice; it saved me hours of head scratching!

 

[SkipVought]

skabutch,

Since PHV "saved me hours of head scratching," it would be most fitting to...
[blue]
Thank PHV
for this valuable post![/blue]

These [purple]Little Purple Stars[/purple] identify threads for Tek-Tips browsers, as ones that have been helpful. They also act as a token "Thank You" to the one or ones who posted valuable information. And they identify the recipient as a grateful


Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]