Query parameter problem - SOLVED

[sschrupp]

Hi everyone. I'm making some changes to a database that currently has users manually entering a date into the Query By Example interface. I decided to replace all of that with a form containing a field for them to enter a date. So instead of:

#8/17/2007#

there is now:

[Forms]![frmChecks]![txtCheckDate]

There were a bunch of queries that were like this and all of them work just fine except one. The one that's not working is actually being accessed in Visual Basic. It gives me a "Run-Time error '3061'. Too few parameters. Expected 1." Seems like it's not recognizing that it's using the date field on the form. The query is a simple select query with the date's criteria pointing to the txtCheckDate field on the form. Here is the code currently:

    Dim db As Database
    Dim rs As Recordset
    Dim LastName
    Dim Response

    Set db = CurrentDb()
    '** The Set rs below is where it gets the error. 
    Set rs = db.OpenRecordset("qryCheckNoUpdate", dbOpenDynaset)

    LastName = rs.[Agent #]
        
        Do Until begckno = endckno + 1
            With rs
                rs.Edit
                !Check_Number = begckno
                .UPDATE
                LastName = rs.[Agent #]
                .MoveNext
                If rs.EOF Then .MoveFirst
                If LastName = rs.[Agent #] Then begckno = begckno Else begckno = begckno + 1
            End With
        Loop
    MsgBox ("Last check number processed was " & begckno - 1 & ".  Please click OK and close this form.")
    
    rs.Close

I've played around with it to try and get it to work, but I'm starting to think I'll need to do some major adjustments to the code and use a SQL statement and stuff.

Please and thank you!

Shane

 

[giacomo]

so does it work when you run the query itself, not in VBA?

 

[sschrupp]

Yes, when I run the query in Access it runs just fine. It's when the VBA code tries to open the query that it expects a parameter and bombs out.

 

[SydneyGeek]

DAO is fussy about crieria and parameters. You need to evaluate them specifically. Use this instead:

    Dim db As DAO.Database
    Dim qd As DAO.QueryDef
    Dim pm As DAO.Parameter
    Dim rs As DAO.Recordset
    Dim LastName
    Dim Response

    Set db = CurrentDb()
    '** The Set rs below is where it gets the error.
    '** You need to specifically evaluate parameters in DAO
    Set qd = db.QueryDefs("qryCheckNoUpdate")
    For Each pm In qd.Parameters
        pm.Value = Eval(pm.Name)
    Next pm
    Set rs = qd.OpenRecordset

    LastName = rs.[Agent #]
        
        Do Until begckno = endckno + 1
            With rs
                rs.Edit
                !Check_Number = begckno
                .Update
                LastName = rs.[Agent #]
                .MoveNext
                If rs.EOF Then .MoveFirst
                If LastName = rs.[Agent #] Then begckno = begckno Else begckno = begckno + 1
            End With
        Loop
    MsgBox ("Last check number processed was " & begckno - 1 & ".  Please click OK and close this form.")
    
    rs.Close

Denis

 

[sschrupp]

Woohoo, that did the trick! I have yet to work with parameters and querydefs like that so this has been a great learning experience. Thanks so much, Denis!

Shane

 

[SydneyGeek]

You're welcome

Denis