Running a parameter query through code.

[DJ44]

I have parameterized query that is to be the recordsource for a particular form.

The form is opened, and the query is executed, when a command button is clicked. ON the "on Click' command, I have the the following code -- passing the required parameter as the OpenArgs :

iWorkUnitID = 1
    
    stDocName = "yfrmFishPriorities"
    DoCmd.OpenForm stDocName, , , , , , iWorkUnitID

On the forms On_Open property, I have the following:

Private Sub Form_Open(Cancel As Integer)

           
Set Me.Recordset = FishPriority(OpenArgs)

End Sub


Function FishPriority(iWorkUnitID As Integer) As Recordset

     Dim qd As QueryDef
     Dim dbsCurrent  As Database
     
     Set dbsCurrent = CurrentDb()
     Set qd = dbsCurrent.QueryDefs("zqryPriorScore")
     
     qd.Parameters("Workunit") = iWorkUnitID
     Set FishPriority = qd.OpenRecordset

End Function

The parameter is passed, but the query is not run (apparently) an not set as the recordsource for the newly opened form.

Any ideas?

All help is appreciated.

DJ

 

[Tim K.]

Your code should work out well. Why don't you feed a fixed value to replace the OpenArgs like this.

Private Sub Form_Open(Cancel As Integer)

Set Me.Recordset = FishPriority(1)

End Sub

 

[DJ44]

Thanks for the reply.

I am using the OpenArgs because ( I failed to mention) the cmd button is one of many on a swithboard screen that allows the user to select various " workunits" --each one delivering a different parameter for the Funciton.

When I execute the code, I still get the request for the parameter "WorkUnit" --- which is what I thought the line

qd.Parameters("WorkUnit") = iWorkUnitID[/Code} accomplished.

Also I get a "data type mismatch error" on the [Code]
Set FishPriority = qd.OpenRecordset

which is defined as a Recordset?????

Still puzzled!!! DJ

 

[Tim K.]

So I guess the zqryPriorScore query is not yet parameterized. To do so, in the Query Design view, go to Query menu>Parameters>type in Workunit in Parameter column and select Integer in Data type column.

And change the function from:

Function FishPriority(iWorkUnitID As Integer) As Recordset

To:

Function FishPriority(iWorkUnitID As Integer) As Object

 

[Tim K.]

Thanks Pat.

I agree with you about use Where cluase to handle his situation.