How to write a VBA procedure to output a Report based on parameters?

[Jason Hamilton]

Greets,

I am wondering how I can go about creating a VBA procedure that will accept a parameter, provide that parameter to a query and then output to HTML a form based on that query.

The report "YourSelection" is created with a record source "Query1". Query1 will prompt the user to select a userID number and return a recordset of all records in the db with that ID. I want to be able to save that report as HTML without having to use the propmt.

Here's what I have so far, perhaps someone can help explain why it isn't working as I expected it to.

Public Function Test(ByVal selection As Integer) As Boolean
    Dim qdf As QueryDef
 
    Set qdf = CurrentDb.QueryDefs("Query1")
    qdf.Parameters(0) = selection
 
    DoCmd.OutputTo acOutputReport, "YourSelection", _    
        acFormatHTML, "request.html"
 
    qdf.Close
    Set qdf = Nothing
 
    ReturnTest = True
End Function
 

Two things are not working here. The first is that I still get prompted to provide the parameter to Query1. Second, no matter what the function always returns False. Though the second issue isn't really that big, if anyone has any ideas on that, I'd appreciate those as well.

Thanks a lot,
Jason

[Stewart Ross]

At least as an initial comment I can explain why your parameter query is not apparently working. It's because there is no actual relation at all between the Querydef object you have initialised and set in lines 4 and 5, and the (independent) copy of the same query which is actually used as the recordsource of the report opened in line 7.

The local object variable qdf is not the recordsource of the report. It's just a local copy of the query that happens to underlie your report. You are actually creating a separate copy of the querydef concerned, and unfortunately there is no way I know of you can assign the parameter-supplied qdf variable to your report through VBA code as it stands. The recordsource of your report, which you can set using VBA, is simply the name of an existing query, or an SQL statement which itself provides the equivalent query. It is not a querydef object as such.

This is one of those occasions when the apparent advantages of allowing users to enter parameter values directly into a query are more than offset by the problems of using the same query to base a report upon.

One way to handle this is to remove the parameters from the query and instead use an unbound form to allow the user to select the relevant parameters for the report. You can use the form's On Load event to populate the various unbound controls with default values for the user should the user not wish to make selections before opening the report.

Using the values of the controls on the form it is straightforward to construct a filter string in VBA which can be applied as an argument of the OpenReport metod to restrict the records to the range concerned. The filter string is really an SQL WHERE clause but without the keyword WHERE.

Another way is to use the form controls as parameters of your report's recordsource query in the same way as the user-entered parameters would be, but this is less flexible because the query itself will only function without requiring parameter subtitution if the form concerned is open at the time.

The reason your function is returning False is that you have a misnamed (and untyped) variable reference at line 13

ReturnTest = True

Your function is actually named Test.

The VBA compiler would have warned you of this undeclared variable if you were using the Option Explicit compiler directive at the top of your code module (in which case all instances of undeclared variables would be treated as errors).

-Stewart