Too few parameters

[cooldisk2005]

All,
With the following code I am getting a "too few parameters" error:

strSQL = "SELECT tbl_showmoney.regID, tbl_showmoney.dateEntered, tbl_showmoney.name, tbl_showmoney.company, tbl_showmoney.mailingAddress, tbl_showmoney.city, tbl_showmoney.stateCan, tbl_showmoney.zipCode, tbl_showmoney.country, tbl_showmoney.telephoneNo, tbl_showmoney.faxNo, tbl_showmoney.email, tbl_showmoney.contype, tbl_showmoney.metotalhvac, tbl_showmoney.metotalcustser, tbl_showmoney.metotaldynamic, tbl_showmoney.metotalret1, tbl_showmoney.metotalret2, tbl_showmoney.meatt1, tbl_showmoney.meatt1pos, tbl_showmoney.meatt2, tbl_showmoney.meatt2pos, "
strSQL = strSQL & "tbl_showmoney.meatt3, tbl_showmoney.meatt3pos, tbl_showmoney.meatt4, tbl_showmoney.meatt4pos, tbl_showmoney.meatt5, tbl_showmoney.meatt5pos, tbl_showmoney.meatt6, tbl_showmoney.meatt6pos, tbl_showmoney.meatt7, tbl_showmoney.meatt7pos, tbl_showmoney.meatt8, tbl_showmoney.meatt8pos, tbl_showmoney.totalvendorrep, tbl_showmoney.cvenatnd1, tbl_showmoney.cvenatnd2, tbl_showmoney.cvenatnd3, tbl_showmoney.vensetupreq, tbl_showmoney.conhotel, tbl_showmoney.crm1arrival, tbl_showmoney.crmm1departure, tbl_showmoney.crm1bedsz, tbl_showmoney.crm1type, tbl_showmoney.crm1guest1, tbl_showmoney.crm1guest2, tbl_showmoney.crm2arrival, tbl_showmoney.crm2departure, tbl_showmoney.crm2bedsz, tbl_showmoney.crm2type, tbl_showmoney.crm2guest1, "
strSQL = strSQL & "tbl_showmoney.crm2guest2, tbl_showmoney.crm3arrival, tbl_showmoney.crm3departure, tbl_showmoney.crm3bedsz, tbl_showmoney.crm3type, tbl_showmoney.crm3guest1, tbl_showmoney.crm3guest2, tbl_showmoney.crm4arrival, tbl_showmoney.crm4departure, tbl_showmoney.crm4bedsz, tbl_showmoney.crm4type, tbl_showmoney.crm4guest1, tbl_showmoney.crm4guest2, tbl_showmoney.conspreq, tbl_showmoney.conregcontact, tbl_showmoney.concc, tbl_showmoney.conacct, a.concardholder, "
strSQL = strSQL & "tbl_showmoney.conexpdate, tbl_showmoney.status "
strSQL = strSQL & "FROM tbl_showmoney"

strWhere = "WHERE"

strOrder = "ORDER BY tbl_showmoney.regID;"


'Set the WHERE clause for the QueryDef if information has been entered into a field on the form
If Not IsNull(Me.name1) Then '<--If the textbox txtFName contains no data THEN do nothing
strWhere = strWhere & " (tbl_showmoney.name) Like '*" & Me.name1 & "*'  AND" '<--otherwise, apply the LIKE statment to the QueryDef
End If

If Not IsNull(Me.company) Then
strWhere = strWhere & " (tbl_showmoney.company) Like '*" & Me.company & "*'  AND"
End If

If Not IsNull(Me.contype) Then
strWhere = strWhere & " (tbl_showmoney.contype) Like '*" & Me.contype & "*'  AND"
End If

'Remove the last AND from the SQL statment
strWhere = Mid(strWhere, 1, Len(strWhere) - 5)

'Pass the QueryDef to the query
Set qryDef = dbNm.QueryDefs("qry_showmoney4")
   qryDef.SQL = strSQL & " " & strWhere & "" & strOrder
   
'Open the Query
    
    Dim dbs As DAO.Database, rst As DAO.Recordset
Dim rsCnt As Integer 'the counter
 
Set dbs = CurrentDb()
Set rst = dbs.OpenRecordset("qry_showmoney4", dbOpenDynaset)
 
With rst
  If .RecordCount > 0 Then 'What you want done
    DoCmd.OpenForm ("frm_masterListSearch"), acNormal, "[regID]"
    DoCmd.Close acForm, "frm_search4"
    
    
  Else
    MsgBox "There is no data that meets your criteria.  Please try again.", vbOKOnly, "No Data Found" 'What you want done
    name1.Value = ""
    company.Value = ""
    contype.Value = ""
    name1.SetFocus
  End If
End With

Set dbs = Nothing
Set rst = Nothing

When the error comes up it highlights this section:

Set rst = dbs.OpenRecordset("qry_showmoney4", dbOpenDynaset)

Can someone help me with this problem?

 

[dhookom]

Can we assume that qryDef and dbNm are Dim'd and set to nothing elsewhere?

You had "a.concardholder," in your SQL with nothing aliased as "a". I would also be concerned that names, company names, or contact types might have an apostrophe in the values.

Did you try go to your queries and open qry_showmoney4 or add a line to display the sql in the debug window?

You might also protect your code in the event that nothing is added to your where clause.

Are you using the querydef for something else or is it to just find if the query returns any records. Seems like a lot of work when a DCount() would suffice.

I don't care for really long lines of code and the table name shouldn't be necessary in the select statement. Also, Name is not a good name for a field since every object in Access has a Name property:

strSQL = "SELECT regID, dateEntered, [name], company, mailingAddress, " & _ 
    "city, stateCan, zipCode, country, telephoneNo, faxNo, email, " & _
    "contype, metotalhvac, metotalcustser, metotaldynamic, metotalret1, " & _
    "metotalret2, meatt1, meatt1pos, meatt2, meatt2pos, "
strSQL = strSQL & "meatt3, meatt3pos, meatt4, meatt4pos, meatt5, meatt5pos, " & _
    "meatt6, meatt6pos, meatt7, meatt7pos, meatt8, meatt8pos, totalvendorrep, " & _
    "cvenatnd1, cvenatnd2, cvenatnd3, vensetupreq, conhotel, crm1arrival, " & _
    "crmm1departure, crm1bedsz, crm1type, crm1guest1, crm1guest2, " & _
    "crm2arrival, crm2departure, crm2bedsz, crm2type, crm2guest1, "
strSQL = strSQL & "crm2guest2, crm3arrival, crm3departure, crm3bedsz, " & _
    "crm3type, crm3guest1, crm3guest2, crm4arrival, crm4departure, crm4bedsz, " & _
    "crm4type, crm4guest1, crm4guest2, conspreq, conregcontact, concc, " & _
    "conacct, concardholder, conexpdate, status "
strSQL = strSQL & "FROM tbl_showmoney"
strWhere = "WHERE"
strOrder = "ORDER BY regID;"

'Set the WHERE clause for the QueryDef if information has _
    been entered into a field on the form
'If the textbox txtFName contains no data THEN do nothing
If Not IsNull(Me.name1) Then 
    'otherwise, apply the LIKE statment to the QueryDef
    strWhere = strWhere & " ([name]) Like ""*" & Me.name1 & "*""  AND" 
End If

If Not IsNull(Me.company) Then
    strWhere = strWhere & " (company) Like ""*" & Me.company & "*""  AND"
End If

If Not IsNull(Me.contype) Then
    strWhere = strWhere & " (contype) Like ""*" & Me.contype & "*""  AND"
End If

'Remove the last AND from the SQL statement
If Len(strWhere) > 5 Then
    strWhere = Mid(strWhere, 1, Len(strWhere) - 5)
End If

'Pass the QueryDef to the query
Set qryDef = dbNm.QueryDefs("qry_showmoney4")
    qryDef.SQL = strSQL & " " & strWhere & "" & strOrder

'Open the Query

Dim dbs As DAO.Database, rst As DAO.Recordset
Dim rsCnt As Integer 'the counter

Set dbs = CurrentDb()
Set rst = dbs.OpenRecordset("qry_showmoney4", dbOpenDynaset)

With rst
  If .RecordCount > 0 Then 'What you want done
    DoCmd.OpenForm ("frm_masterListSearch"), acNormal, "[regID]"
    DoCmd.Close acForm, "frm_search4"
  Else
    MsgBox "There is no data that meets your criteria.  Please try again.", & _
      vbOKOnly, "No Data Found" 'What you want done
    name1.Value = ""
    company.Value = ""
    contype.Value = ""
    name1.SetFocus
  End If
End With

Set dbs = Nothing
Set rst = Nothing

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[cooldisk2005]

dhookum,
Once you again you have come to my rescue. I have applied your suggestions and they worked!!!!

Thank you so much!!!!