Results 1 to 6 of 6
  1. #1
    Join Date
    Sep 2005
    Posts
    240

    Unanswered: Help: Too few paramters expected

    Hi,

    I am trying to create a system where a search form loads, and checks the query behind it to either display a record(s) if it is found, or send them straight to a form to add a record.
    This is the code:

    Code:
    Dim db As Database
    Dim rst As DAO.Recordset
    
    Set db = CurrentDb()
    Set rst = db.OpenRecordset("qrySearch", dbOpenDynaset, dbSeeChanges)
    
        If rst.EOF Then
            MsgBox "No employers found.  Please add this employer.", vbOKOnly, "RECEPE"
            DoCmd.OpenForm "frmAddEmp"
        Else
            DoCmd.OpenForm "frmListEmp"
        End If
    I have a query already for the search, but is based on parameters.
    Here is the query:

    Code:
    SELECT dbo_tblEmployer.*
    FROM dbo_tblEmployer
    WHERE ((([Emp_Name] & "") Like IIf([Forms]![frmSearch]![txtEmpName] Is Null,"*",[Forms]![frmSearch]![txtEmpName] & "*")) AND (([Addr4] & "") Like IIf([Forms]![frmSearch]![txtTown] Is Null,"*",[Forms]![frmSearch]![txtTown] & "*")) AND (([Addr5] & "") Like IIf([Forms]![frmSearch]![txtCounty] Is Null,"*",[Forms]![frmSearch]![txtCounty] & "*")) AND (([Country] & "") Like IIf([Forms]![frmSearch]![txtCountry] Is Null,"*",[Forms]![frmSearch]![txtCountry] & "*")) AND (([PostCode] & "") Like IIf([Forms]![frmSearch]![txtPCode] Is Null,"*",[Forms]![frmSearch]![txtPCode] & "*")) AND (([BArea] & "") Like IIf([Forms]![frmSearch]![cmboBArea] Is Null,"*",[Forms]![frmSearch]![cmboBArea] & "*")) AND ((dbo_tblEmployer.On_Stop)=IIf([Forms]![frmSearch]![chkOnStop]<>0,1,0)) AND (([No_of_Employees] & "") Like IIf([Forms]![frmSearch]![txtNoEmps] Is Null,"*",[Forms]![frmSearch]![txtNoEmps] & "*")));
    Will I have to come up with another method, or is there a way using my existing query?

    Thanks

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Hi

    1) Open your query in design view and click Query -> Parameters
    2) Enter [Forms]![frmSearch]![txtEmpName] as the parameter name and then select the datatype (presumably Text)
    3) You now need a QueryDef object in your code. Instantiate it to the query and then set the parameter value e.g.
    Code:
    Dim QDef as DAO.QueryDef
    Set QDef = Currentdb.QueryDefs("MyQuery")
    QDef.Parameters("[Forms]![frmSearch]![txtEmpName]").Value = [Forms]![frmSearch]![txtEmpName]
    4) Repeat.
    5) Use the OpenRecordset method of the querydef object rather than the database object to open your recordset.
    6) You could also look up NZ() to get rid of all those Iifs.
    HTH
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Sep 2005
    Posts
    240
    Thanks, pootle. Although I now have another issue.

    I decided to change the IIf statements to Nz statements like you mentioned (why didn't I think of that ).

    Anyway when running it, it brings back no results at all even though the data is in the table. The query is:

    Code:
    PARAMETERS [Forms]![frmSearch]![txtEmpName] Text ( 255 ), [Forms]![frmSearch]![txtTown] Text ( 255 ), [Forms]![frmSearch]![txtCounty] Text ( 255 ), [Forms]![frmSearch]![txtCountry] Text ( 255 ), [Forms]![frmSearch]![txtPCode] Text ( 255 ), [Forms]![frmSearch]![cmboBArea] Text ( 255 );
    SELECT dbo_tblEmployer.*
    FROM dbo_tblEmployer
    WHERE ((([Emp_Name] & "") Like Nz("[Forms]![frmSearch]![txtEmpName]","*")) AND (([Addr4] & "") Like Nz("[Forms]![frmSearch]![txtTown]","*")) AND (([Addr5] & "") Like Nz("[Forms]![frmSearch]![txtCounty]","*")) AND (([Country] & "") Like Nz("[Forms]![frmSearch]![txtCountry]","*")) AND (([PostCode] & "") Like Nz("[Forms]![frmSearch]![txtPCode]","*")) AND (([BArea] & "") Like Nz("[Forms]![frmSearch]![cmboBArea]","*")));
    Last edited by KevCB226; 09-11-06 at 10:47.

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    What happens if you run it from the query window by double clicking rather than by code?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by pootle flump
    What happens if you run it from the query window by double clicking rather than by code?
    Hmm - the answer is it returns no records.
    Code:
    Like Nz("[Forms]![frmSearch]![txtEmpName]","*")
    should be
    Code:
    Like Nz([Forms]![frmSearch]![txtEmpName],"*")
    Testimonial:
    pootle flump
    ur codings are working excelent.

  6. #6
    Join Date
    Sep 2005
    Posts
    240
    Quote Originally Posted by pootle flump
    should be
    Code:
    Like Nz([Forms]![frmSearch]![txtEmpName],"*")
    I already tried that, and it comes back with no results. And when running the query from the query window it brings back nothing.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •