[AccessD] Automating parameterized action queries.....

Ken Ismert KIsmert at TexasSystems.com
Tue Mar 8 12:46:32 CST 2005


Sander,

There is a class-based approach:

Define a class, CReportParms, with two variant properties, Version and
StartDate:

   Private mVersion As Variant
   Private mStartDate As Variant

   Property Get Version() As Variant
      Version = mVersion
   End Property
   Property Let Version(VersionIn As Variant)
      mVersion = VersionIn
   End Property

   ' (Similar Get/Let for StartDate)

   Private Sub Class_Initialize()
      mVersion = Null
      mStartDate = Null
   End Sub

Declare it as global in one of your modules:

   Public gRptParms As New CReportParms

Provide two global functions to use in your queries in place of your
parameters:

   Public Function ParmVersion() As Variant
      Parms = gRptParms.Version
   End Function
   Public Function ParmStartDate() As Variant
      Parms = gRptParms.StartDate
   End Function

You can set these properties in your form before you run your report:

   gRptParms.Version = txtVersion
   gRptParms.StartDate = txtStartDate

If you can calculate reasonable values for Version and StartDate, you can do
so in the CReportParms Initialize event. Alternately, you can prompt the
user in the class when the queries are run standalone:

   Property Get Version() As Variant
      Dim sVersion As String
      If IsNull(mVersion) Then
          sVersion = InputBox("Enter Version:")
          If sVersion > "" Then
             mVersion = CLng(sVersion)
          End If
      End If
      Version = mVersion
   End Property

You could also have a third property, FormDriven, that, when set to True,
doesn't prompt the user. Only your frmReport form will set this property,
and clear it upon exit. That way, the user is always prompted when running
the queries stand-alone.

-Ken

> Hi group,
>
> my story of the queries continues. I need to
> run a set of action queries. This works fine.
>
> Several queries have parameters. So that means
> the user has to watch the screen for a
> parameter to pop-up. Is it possible to add
> some parameter fields in the form to fill
> the required parameters...WITHOUT changing
> the query it self?
>
> So I have a parameters in my query:
> [Enter version:]
> [Enter startdate:]
>
> And I do not want to change these to:
> forms!frmReport!txtVersion
> forms!frmReport!txtStartDate






More information about the AccessD mailing list