Tripped up on Query parameter

[funderburgh]

I have developed a query to determine a student's class ranking for use while printing a transcript. The graduating year is a parameter.

I currently have the criteria for the parameter set to a combo box in the form that executes the report, but I am getting an error. Here is my code:

Private Sub Report_Load()
Dim dbs As DAO.Database
Dim rsRankQuery As DAO.Recordset
Dim intCount As Integer
Dim strRank As String
strRank = "Not Applicable"

MsgBox [Forms]![Main Navigation]![Print Menu]![SchoolYear]
' (this is the value of the criteria in the query)

Set dbs = CurrentDb
Set rsRankQuery = dbs.OpenRecordset("Class Rank", dbOpenDynaset)

The Set command returns the following error: "Run Time 3601 Too Few Parameters, Expected 1.

I don't get the message when I hard code the criteria. The MsgBox returns the correct data, and with the form open and filled in the Query runs correctly when it is executed stand alone.

I am also using this exact criteria syntax in other queries successfully, but none in a Set command.

Any help is welcome. Thanks

 

[pbaldy]

This is your problem:

http://support.microsoft.com/default.aspx?scid=kb;en-us;209203

A way around it is to change the criteria to:

Eval('[Forms]![name_of_form].[name_of_control_on_form]')

 

[HiTechCoach]

You can not use a form reference in a query that is opened as a recordset without some special handling.


Other options to handle this:

You can built the SQL in your VBA code and substitute the value into the SQL. It will be just liek it you hard coded the value.

strSQL = "Select ... Where [MyField] = " & Forms!myForm.myControlName  

Set rsRankQuery = dbs.OpenRecordset(strSQL, dbOpenDynaset)

or use Eval() like this:

Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim param As DAO.Parameter
Dim r As DAO.Recordset
Dim str, strSQL As String
  
'Set up error handler
On Error GoTo Err_proc
  Set db = DBEngine(0)(0)
  Set qdf = db.QueryDefs(("qryEmailForPeriod")
  For Each param in qdf.Parameters
    param.Value = Eval(param.name)
  Next 
  
  'define the recordset to open
  'open the Recordset
  Set r = qdf.OpenRecordset(dbOpenSnapshot)
  
  ' You code here
  
  ' Remember to clean up objects and set object variables to Nothing

 

[funderburgh]

I am really greatful for the help. My SQL is a bit too long to put in the code, so I am going to try the Eval expression.

I have a follow-up question on a related issue. My VBA Access manual says that the Load event of a report happens after the data is populated. I need to use the database fields for do my work on this snippet, but I get errors trying to reference database or report fields.

Am I in the wrong event?

Thanks

 

[pbaldy]

A report has no load event (2007 does, but...). I typically use the format event of whatever section I'm dealing with to act on controls/data in that section.

 

[funderburgh]

Thanks, I'm on 2007 and my manual is right. I was trying to access data that is not on the report. I have it now, you're terriffic, thanks.