[AccessD] Automating parameterized action queries.....DATE?!

Ken Ismert KIsmert at TexasSystems.com
Fri Mar 11 12:20:59 CST 2005


Sander:

Your problem is with your date delimiters: use #00:00:00# instead of
"00:00:00". This, by the way, is Microsoft Day 0, which is 12/30/1899.

You will have to change this segment of your code, as well:
   dtmNewBusinessDate = InputBox("Enter the new business date:")

You will need something like:
   Dim sNewDate As String
   sNewDate = InputBox("Enter the new business date:")
   If IsDate(sNewDate) Then
      mdtmNewBusinessDate = CDate(sNewDate)
   End If

Also, a suggestion:

If you use:
   Public oRptParam As NEW cReportParams

Then you can get rid of this business in your functions:
   If oRptParam Is Nothing Then
      Set oRptParam = New cReportParams
      blnDestroyRepParam = True
   Else
      blnDestroyRepParam = False
   End If
   ...
   If blnDestroyRepParam Then
      Set oRptParam = Nothing
   End If

The New keyword sets oRptParam to a new instance of cReportParams
automatically on first reference. A single instance of the object will
remain active until you exit or set it to nothing. This seems to be a good
fit for your code here. I have used this technique for years in many Access
programs with no ill effects. It is a documented and valid part of the VBA
spec, and it has a place in your toolbox.

-Ken

-----Original Message-----
From: Sad Der [mailto:accessd666 at yahoo.com]
Sent: Friday, March 11, 2005 3:36 AM
To: Access Developers discussion and problem solving
Subject: RE: [AccessD] Automating parameterized action
queries.....DATE?!


Hi group,

after almost 3 hours I give up. I'm using Ken's
solution and it works great. I'm using for all my
params....off course then I crashed into a F#$@ng date
field.

Can anybody tell me why code does not return a date?
What am I missing?

Module: mParams
Option Compare Database
Option Explicit
   Public oRptParam As cReportParams

Public Function ParamVersion() As Variant
   'Dim oRptParam As cReportParams
   Dim blnDestroyRepParam As Boolean

   If oRptParam Is Nothing Then
      Set oRptParam = New cReportParams
      blnDestroyRepParam = True
   Else
      blnDestroyRepParam = False
   End If

   ParamVersion = oRptParam.Version
   If blnDestroyRepParam Then
      Set oRptParam = Nothing
   End If
End Function

Public Function ParamNewBusinessDate() As Date
   'Dim oRptParam As cReportParams
   Dim blnDestroyRepParam As Boolean

   If oRptParam Is Nothing Then
      Set oRptParam = New cReportParams
      blnDestroyRepParam = True
   Else
      blnDestroyRepParam = False
   End If
'????????????????????????????????????????????????????
'===> Somehow ParamNewBusinessDate stays 00:00:00????
'????????????????????????????????????????????????????
   ParamNewBusinessDate = oRptParam.NewBusinessDate
   If blnDestroyRepParam Then
      Set oRptParam = Nothing
   End If
End Function

CLASS cReportParams
Option Compare Database
Option Explicit

Private mVersion As String
Private mdtmNewBusinessDate As Variant
'This parameter checks if the query is run via a form!
Private mFormDriven As Boolean


Property Get Version() As String
   Dim strVersion As String

   If mFormDriven Then
      Version = mVersion
   Else
      If mVersion = "" Then
         strVersion = InputBox("Enter the version:")
         If strVersion > "" Then
            mVersion = strVersion
         End If
         Version = mVersion
      Else
         Version = mVersion
      End If
   End If
End Property

Property Let Version(VersionIn As String)
   mVersion = VersionIn
End Property
Property Get NewBusinessDate() As Variant
   Dim dtmNewBusinessDate As Date

   If mFormDriven Then
      NewBusinessDate = mdtmNewBusinessDate
   Else
      If mdtmNewBusinessDate = "00:00:00" Then
'I fill the InputBox with the value 22/02/2005
         dtmNewBusinessDate = InputBox("Enter the new
business date:")
         If Not dtmNewBusinessDate = "00:00:00" Then
            mdtmNewBusinessDate = dtmNewBusinessDate
         End If
      Else
         NewBusinessDate = mdtmNewBusinessDate
      End If
   End If
End Property

Property Let NewBusinessDate(NewBusinessDateIn As
Variant)
   mdtmNewBusinessDate = NewBusinessDateIn
End Property

Property Let FormDriven(FormDrivenIN As Boolean)
   mFormDriven = FormDrivenIN
End Property

Property Get FormDriven() As Boolean
   FormDriven = mFormDriven
End Property

Private Sub Class_Initialize()
   mVersion = ""
   mdtmNewBusinessDate = "00:00:00"
   mFormDriven = False
End Sub

Private Sub Class_Terminate()
   mVersion = ""
   mdtmNewBusinessDate = "00:00:00"
   mFormDriven = False
End Sub

-----------------------------------------------------
If somebody needs more details....please ask!

Thnx.

Regards,

Sander
--- Ken Ismert <KIsmert at TexasSystems.com> wrote:
> 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
>
>
>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>

__________________________________________________
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around
http://mail.yahoo.com






More information about the AccessD mailing list