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