Stopping report

[Guest]

I have several reports that open one of several forms (depending on the
report) to allow users to select a date range/department/machine/shift for
their particular report. Each specific form is initiated by the OnOpen
function of the reports and is set to Modal so that the report will not open
until the form is closed. Mulitple reports use the same form.

In some cases, a user will start the report and, after seeing the form that
pops up, will decide that they've tried to run the wrong report. Closing
the form or entering blanks for dates still runs the query (time consuming)
for the report. Is there a way to give the user a "Cancel" button that will
close the form and stop the report/query from running? I've tried End, but
that closes Access altogether. I can't specify which Report to cancel
because each form is used by several reports.

 

[fredg]

I have several reports that open one of several forms (depending on the
report) to allow users to select a date range/department/machine/shift for
their particular report. Each specific form is initiated by the OnOpen
function of the reports and is set to Modal so that the report will not open
until the form is closed. Mulitple reports use the same form.

In some cases, a user will start the report and, after seeing the form that
pops up, will decide that they've tried to run the wrong report. Closing
the form or entering blanks for dates still runs the query (time consuming)
for the report. Is there a way to give the user a "Cancel" button that will
close the form and stop the report/query from running? I've tried End, but
that closes Access altogether. I can't specify which Report to cancel
because each form is used by several reports.

It will depend upon your version of Access.
Access 2000 or newer:
Code the Report's Open event

DoCmd.OpenForm "FormName", , , , , acDialog
If Not CurrentProject.AllForms("FormName").IsLoaded Then
MsgBox Cancelling the report"
Cancel = True
End If

===============

In Access 97, copy this function (from the Northwind.mdb sample
database) to a Module. Watch out for line wrapping.

Function IsLoaded(ByVal strFormName As String) As Integer
' Returns True if the specified form is open in Form view or
Datasheet
view.
Const conObjStateClosed = 0
Const conDesignView = 0
If SysCmd(acSysCmdGetObjectState, acForm, strFormName) <>
conObjStateClosed Then
If Forms(strFormName).CurrentView <> conDesignView Then
IsLoaded = True
End If
End If
End Function
================
Then Code the Report's Open event:
DoCmd.OpenForm "FormName", , , , , acDialog
If Not IsLoaded("FormName") Then
MsgBox "Cancelling Report"
Cancel = true
End if

 

[Allen Browne]

How about working the other way around?

Create a form that acts as a front-end to your reports. It will contain some
way the user can select the report to open (e.g. option group or combo). In
the AfterUpdate event of that control, display the various boxes that the
user can use to filter that particular report, keeping irrelevant ones
hidden. Below all that is a Preview button. When button is clicked, build up
a WhereCondition string from the controls where the user entered something,
and the OpenReport the one specified in the option group/combo.

This way:
- the user has already entered any filtering before the report is opened;
- there is only one form to open many reports (simple interface);
- nothing has to be modal;
- you don't have any jammed events in the queue.

 

[Shaun Beane]

I have to agree with Allen on this one. I've always wondered what the
benefits (if any) are of opening the form via the open event on the report.

 

[Guest]

It will depend upon your version of Access.
Access 2000 or newer:
Code the Report's Open event

DoCmd.OpenForm "FormName", , , , , acDialog
If Not CurrentProject.AllForms("FormName").IsLoaded Then
MsgBox Cancelling the report"
Cancel = True
End If

===============

In Access 97, copy this function (from the Northwind.mdb sample
database) to a Module. Watch out for line wrapping.

Function IsLoaded(ByVal strFormName As String) As Integer
' Returns True if the specified form is open in Form view or
Datasheet
view.
Const conObjStateClosed = 0
Const conDesignView = 0
If SysCmd(acSysCmdGetObjectState, acForm, strFormName) <>
conObjStateClosed Then
If Forms(strFormName).CurrentView <> conDesignView Then
IsLoaded = True
End If
End If
End Function
================
Then Code the Report's Open event:
DoCmd.OpenForm "FormName", , , , , acDialog
If Not IsLoaded("FormName") Then
MsgBox "Cancelling Report"
Cancel = true
End if

--

I am using Access 2002 (but most users are using Access 2000).

Anyway, I have set the form's Modal property to Yes so that the user cannot
continue past the form without entering criteria. The "Continue to Report"
button is essentially a "CloseForm" button, so the form is always closed
before the report generates. Wouldn't the code above would always bring up
the MsgBox?

I need the "Cancel" button that I add to close the form, but to halt the
report (and associated query) from continuing.

 

[Guest]

I guess I should have mentioned that this a single databse on a network where
several users may be using it at the same time. So far, I've resisted
loading the front-end on everyone's PC because of how widely everyone is
distributed (the back end is a centralized SQL database). To this point,
I've just dealt with the small number of overlaps on the pop-up forms. If I
create a central form, would a user change the query criteria while another
user is running a report with a differnt set of reports?

I guess a lot of semi-literate Access users like myself open the form from
the report because we just use the Switchboard Manager for the user interface
for the report selection.

 

[Shaun Beane]

True, but even with the switchboard manager you can open the form instead of
the report and then trigger the report from the form. Please don't think
I'm trying to be argumentative, just trying to see think outloud about when
to use the open event. Thank you for the feedback!

On using one form for all reports with multiple users, one user will not
affect the criteria of another if they both happen to be running the report
at the same time. Even though the database is on the network, the
"instance" of the form is still unique.

 

[Guest]

No, you're not argumentative at all...I'm just trying to understand.

I know that I can open the form from the switchboard and then open the
report from the form, but I have a group of ~10 forms that feed ~42 reports
and ~12 macros (for those users that want the numbers and not a pretty
report).

If I understand you all correctly, I would have a single form that would:
1. Allow the users to select a report or macro
2. Then update the Visible value of the appropriate filter criteria (based
on the selection in step 1)
3. Run the report or macro

Would you recommend having a table behind the form with report/macro names,
the criteria that should be visible for each, and the values the user selects
for the criteria for the queries to pull from? Or should the form be
unbound, coded to make the appropriate criteria selection visible and then
pass criteria selection to the query right from the form?

 

[Shaun Beane]

Yes, that's exactly the way I've done that in the main database that I
support. I don't use a table to store the macro/module names but I can
definitely see the benefit in that. My prompt form is pretty big, but like
you said, I enable/disable the controls based on which report the user
clicks on. I actually pass the criteria using VBA, but you could certainly
go right to the query.

 

[Allen Browne]

Yes, you've understood that correctly.

Personally, I don't bind this form to a table (like switchboard) or read the
report names from the system table (because I don't want to expose the
subreports). You could create your own table to manage this if you wanted
to.

 

[Guest]

Thanks for all of the help and support...just a couple more questions...

I've created an unbound form that has unbound controls for everything that a
user might want to filter on (like date range, machine, shift, etc.)

How do I use the information that the user enters in the unbound controls to:
1. Filter the results of query for the report
2. Display the user entry information on the report

For instance, the user enters a StartDate and EndDate for records. The
query must limit the records to info bewteen those dates and then I need to
display the dates the user requested on the report.

 

[Allen Browne]

You will have a command button on your form that actually opens the report.
In its Click event you need to build two strings: one as the WhereCondition
for OpenReport, and the other as the description for the user.

In Access 2002 and 2003, you can pass the description through the OpenArgs,
and just add a text box on the report with Control Source of:
=[Report].[OpenArgs]
In earlier versions, assign strDescrip to the Caption of a hidden label on
your form, and use the Format event of the Report Header section to read it,
and copy to a text box on the report.

This example shows how to create the 2 strings for different field types,
using the different delimiters. Note that the date range example copes with
the fact that the field may have a time component by asking for "less than
the next day".

Private Sub cmdPreview_Click()
Dim strWhere As String 'Where Condition
Dim strDescrip As String 'Description of Where Condition.
Dim lngLen As Long 'Length of string.
Const conJetDate ="\#mm\/dd\/yyyy\#" 'Format for dates.

'Text field example.
If Not IsNull(Me.txtSurname) Then
strWhere = strWhere & "([Surname] = """ & Me.txtSurname & """) AND "
strDescrip = strDescrip & "Surname of " & Me.txtSurname & ". "
End If

'Number field example.
If Not IsNull(Me.txtAmount) Then
strWhere = strWhere & "([Amount] >= " & Me.txtAmount & ") AND "
strDescrip = strDescrip & "Amount at least " & Format(Me.txtAmount,
"Currency") & ". "
End If

'Date range example
If Not IsNull(Me.txtStartDate) Then
strWhere = strWhere & "([MyDateField] >= " & _
Format(Me.txtStartDate, conJetDate) & ") AND "
strDescrip = ...
End If
If Not IsNull(Me.txtEndDate) Then
strWhere = strWhere & "([MyDateField] < " & _
Format(Me.txtEndDate + 1, conJetDate) & ") AND "
End If

'Chop of the trailing " AND "
lngLen = Len(strWhere) - 5
If lngLen > 0 Then
strWhere = Left$(strWhere, lngLen)
strDescrip = Left$(strDescrip, Len(strDescrip) - 2)
End If

'Open the report
DoCmd.OpenReport "MyReport", acViewPreview, , strWhere,
OpenArgs:=strDescrip
End Sub

 

[Guest]

OK, now I understand how to use the WhereCondition and how to use OpenArgs to
send strings through. In my example, I can send a string through OpenArgs
that says "between 10/1/04 07:00AM and 10/4/04 09:00AM".

But, I also need either the values themselves or the difference between
these two values to do some calculations in the report. I already have a
function JustMinutes that calculates the difference in minutes so that's not
the issue. I'm dividing the run time of a machine during the user entered
time by the total time available between the user entered time to get
utilization. As I see it, I can:
1. Pass both of the actual date/time values from the unbound controls and
calculate the difference in the report OR
2. Calculate the difference between the actual date/time values from the
unbound controls in another unbound control on the forms and pass it to the
report

Either way, how do I use OpenArgs to pass this value (to do calculations) in
addition to the string?

You will have a command button on your form that actually opens the report.
In its Click event you need to build two strings: one as the WhereCondition
for OpenReport, and the other as the description for the user.

In Access 2002 and 2003, you can pass the description through the OpenArgs,
and just add a text box on the report with Control Source of:
=[Report].[OpenArgs]
In earlier versions, assign strDescrip to the Caption of a hidden label on
your form, and use the Format event of the Report Header section to read it,
and copy to a text box on the report.

This example shows how to create the 2 strings for different field types,
using the different delimiters. Note that the date range example copes with
the fact that the field may have a time component by asking for "less than
the next day".

Private Sub cmdPreview_Click()
Dim strWhere As String 'Where Condition
Dim strDescrip As String 'Description of Where Condition.
Dim lngLen As Long 'Length of string.
Const conJetDate ="\#mm\/dd\/yyyy\#" 'Format for dates.

'Text field example.
If Not IsNull(Me.txtSurname) Then
strWhere = strWhere & "([Surname] = """ & Me.txtSurname & """) AND "
strDescrip = strDescrip & "Surname of " & Me.txtSurname & ". "
End If

'Number field example.
If Not IsNull(Me.txtAmount) Then
strWhere = strWhere & "([Amount] >= " & Me.txtAmount & ") AND "
strDescrip = strDescrip & "Amount at least " & Format(Me.txtAmount,
"Currency") & ". "
End If

'Date range example
If Not IsNull(Me.txtStartDate) Then
strWhere = strWhere & "([MyDateField] >= " & _
Format(Me.txtStartDate, conJetDate) & ") AND "
strDescrip = ...
End If
If Not IsNull(Me.txtEndDate) Then
strWhere = strWhere & "([MyDateField] < " & _
Format(Me.txtEndDate + 1, conJetDate) & ") AND "
End If

'Chop of the trailing " AND "
lngLen = Len(strWhere) - 5
If lngLen > 0 Then
strWhere = Left$(strWhere, lngLen)
strDescrip = Left$(strDescrip, Len(strDescrip) - 2)
End If

'Open the report
DoCmd.OpenReport "MyReport", acViewPreview, , strWhere,
OpenArgs:=strDescrip
End Sub


--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Thanks for all of the help and support...just a couple more questions...

I've created an unbound form that has unbound controls for everything that
a
user might want to filter on (like date range, machine, shift, etc.)

How do I use the information that the user enters in the unbound controls
to:
1. Filter the results of query for the report
2. Display the user entry information on the report

For instance, the user enters a StartDate and EndDate for records. The
query must limit the records to info bewteen those dates and then I need
to
display the dates the user requested on the report.

 

[Allen Browne]

You can pass as many values as you like in the OpenArgs string, delimited by
(say) ";". Use Split() to parse them.

Another alternative is to use the Open event of the report to read the
values from the form, e.g.:

If CurrentProject.AllForms("Form1").IsLoaded Then
With Forms("Form1")
Debug.Print DateDiff("n", ![StartTime], ![EndTime])
End With
End If

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

OK, now I understand how to use the WhereCondition and how to use OpenArgs
to
send strings through. In my example, I can send a string through OpenArgs
that says "between 10/1/04 07:00AM and 10/4/04 09:00AM".

But, I also need either the values themselves or the difference between
these two values to do some calculations in the report. I already have a
function JustMinutes that calculates the difference in minutes so that's
not
the issue. I'm dividing the run time of a machine during the user entered
time by the total time available between the user entered time to get
utilization. As I see it, I can:
1. Pass both of the actual date/time values from the unbound controls and
calculate the difference in the report OR
2. Calculate the difference between the actual date/time values from the
unbound controls in another unbound control on the forms and pass it to
the
report

Either way, how do I use OpenArgs to pass this value (to do calculations)
in
addition to the string?

You will have a command button on your form that actually opens the
report.
In its Click event you need to build two strings: one as the
WhereCondition
for OpenReport, and the other as the description for the user.

In Access 2002 and 2003, you can pass the description through the
OpenArgs,
and just add a text box on the report with Control Source of:
=[Report].[OpenArgs]
In earlier versions, assign strDescrip to the Caption of a hidden label
on
your form, and use the Format event of the Report Header section to read
it,
and copy to a text box on the report.

This example shows how to create the 2 strings for different field types,
using the different delimiters. Note that the date range example copes
with
the fact that the field may have a time component by asking for "less
than
the next day".

Private Sub cmdPreview_Click()
Dim strWhere As String 'Where Condition
Dim strDescrip As String 'Description of Where Condition.
Dim lngLen As Long 'Length of string.
Const conJetDate ="\#mm\/dd\/yyyy\#" 'Format for dates.

'Text field example.
If Not IsNull(Me.txtSurname) Then
strWhere = strWhere & "([Surname] = """ & Me.txtSurname & """)
AND "
strDescrip = strDescrip & "Surname of " & Me.txtSurname & ". "
End If

'Number field example.
If Not IsNull(Me.txtAmount) Then
strWhere = strWhere & "([Amount] >= " & Me.txtAmount & ") AND "
strDescrip = strDescrip & "Amount at least " &
Format(Me.txtAmount,
"Currency") & ". "
End If

'Date range example
If Not IsNull(Me.txtStartDate) Then
strWhere = strWhere & "([MyDateField] >= " & _
Format(Me.txtStartDate, conJetDate) & ") AND "
strDescrip = ...
End If
If Not IsNull(Me.txtEndDate) Then
strWhere = strWhere & "([MyDateField] < " & _
Format(Me.txtEndDate + 1, conJetDate) & ") AND "
End If

'Chop of the trailing " AND "
lngLen = Len(strWhere) - 5
If lngLen > 0 Then
strWhere = Left$(strWhere, lngLen)
strDescrip = Left$(strDescrip, Len(strDescrip) - 2)
End If

'Open the report
DoCmd.OpenReport "MyReport", acViewPreview, , strWhere,
OpenArgs:=strDescrip
End Sub

Thanks for all of the help and support...just a couple more
questions...

I've created an unbound form that has unbound controls for everything
that
a
user might want to filter on (like date range, machine, shift, etc.)

How do I use the information that the user enters in the unbound
controls
to:
1. Filter the results of query for the report
2. Display the user entry information on the report

For instance, the user enters a StartDate and EndDate for records. The
query must limit the records to info bewteen those dates and then I
need
to
display the dates the user requested on the report.

 

[Guest]

OK, now you lost me...

I added the results of my JustMinutes function to the OpenArgs so I now have
a unbound text box on my report that says "between 10/01/04 07:00AM and
10/01/04 08:00AM;60" with the ;60 being the difference.

Where do I put the Split()? The help file on the Split function in VBA is
not very good.

You can pass as many values as you like in the OpenArgs string, delimited by
(say) ";". Use Split() to parse them.

Another alternative is to use the Open event of the report to read the
values from the form, e.g.:

If CurrentProject.AllForms("Form1").IsLoaded Then
With Forms("Form1")
Debug.Print DateDiff("n", ![StartTime], ![EndTime])
End With
End If

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

OK, now I understand how to use the WhereCondition and how to use OpenArgs
to
send strings through. In my example, I can send a string through OpenArgs
that says "between 10/1/04 07:00AM and 10/4/04 09:00AM".

But, I also need either the values themselves or the difference between
these two values to do some calculations in the report. I already have a
function JustMinutes that calculates the difference in minutes so that's
not
the issue. I'm dividing the run time of a machine during the user entered
time by the total time available between the user entered time to get
utilization. As I see it, I can:
1. Pass both of the actual date/time values from the unbound controls and
calculate the difference in the report OR
2. Calculate the difference between the actual date/time values from the
unbound controls in another unbound control on the forms and pass it to
the
report

Either way, how do I use OpenArgs to pass this value (to do calculations)
in
addition to the string?

You will have a command button on your form that actually opens the
report.
In its Click event you need to build two strings: one as the
WhereCondition
for OpenReport, and the other as the description for the user.

In Access 2002 and 2003, you can pass the description through the
OpenArgs,
and just add a text box on the report with Control Source of:
=[Report].[OpenArgs]
In earlier versions, assign strDescrip to the Caption of a hidden label
on
your form, and use the Format event of the Report Header section to read
it,
and copy to a text box on the report.

This example shows how to create the 2 strings for different field types,
using the different delimiters. Note that the date range example copes
with
the fact that the field may have a time component by asking for "less
than
the next day".

Private Sub cmdPreview_Click()
Dim strWhere As String 'Where Condition
Dim strDescrip As String 'Description of Where Condition.
Dim lngLen As Long 'Length of string.
Const conJetDate ="\#mm\/dd\/yyyy\#" 'Format for dates.

'Text field example.
If Not IsNull(Me.txtSurname) Then
strWhere = strWhere & "([Surname] = """ & Me.txtSurname & """)
AND "
strDescrip = strDescrip & "Surname of " & Me.txtSurname & ". "
End If

'Number field example.
If Not IsNull(Me.txtAmount) Then
strWhere = strWhere & "([Amount] >= " & Me.txtAmount & ") AND "
strDescrip = strDescrip & "Amount at least " &
Format(Me.txtAmount,
"Currency") & ". "
End If

'Date range example
If Not IsNull(Me.txtStartDate) Then
strWhere = strWhere & "([MyDateField] >= " & _
Format(Me.txtStartDate, conJetDate) & ") AND "
strDescrip = ...
End If
If Not IsNull(Me.txtEndDate) Then
strWhere = strWhere & "([MyDateField] < " & _
Format(Me.txtEndDate + 1, conJetDate) & ") AND "
End If

'Chop of the trailing " AND "
lngLen = Len(strWhere) - 5
If lngLen > 0 Then
strWhere = Left$(strWhere, lngLen)
strDescrip = Left$(strDescrip, Len(strDescrip) - 2)
End If

'Open the report
DoCmd.OpenReport "MyReport", acViewPreview, , strWhere,
OpenArgs:=strDescrip
End Sub



Thanks for all of the help and support...just a couple more
questions...

I've created an unbound form that has unbound controls for everything
that
a
user might want to filter on (like date range, machine, shift, etc.)

How do I use the information that the user enters in the unbound
controls
to:
1. Filter the results of query for the report
2. Display the user entry information on the report

For instance, the user enters a StartDate and EndDate for records. The
query must limit the records to info bewteen those dates and then I
need
to
display the dates the user requested on the report.

 

[Allen Browne]

If you pass in a string such as:
"#1/1/2004#; #12/31/2004#; 365"
you can parse out the parts of the string as:
? Split("#1/1/2004#; #12/31/2004#; 365", ";")(1)
? Split("#1/1/2004#; #12/31/2004#; 365", ";")(2)

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

OK, now you lost me...

I added the results of my JustMinutes function to the OpenArgs so I now
have
a unbound text box on my report that says "between 10/01/04 07:00AM and
10/01/04 08:00AM;60" with the ;60 being the difference.

Where do I put the Split()? The help file on the Split function in VBA is
not very good.

You can pass as many values as you like in the OpenArgs string, delimited
by
(say) ";". Use Split() to parse them.

Another alternative is to use the Open event of the report to read the
values from the form, e.g.:

If CurrentProject.AllForms("Form1").IsLoaded Then
With Forms("Form1")
Debug.Print DateDiff("n", ![StartTime], ![EndTime])
End With
End If

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

OK, now I understand how to use the WhereCondition and how to use
OpenArgs
to
send strings through. In my example, I can send a string through
OpenArgs
that says "between 10/1/04 07:00AM and 10/4/04 09:00AM".

But, I also need either the values themselves or the difference between
these two values to do some calculations in the report. I already have
a
function JustMinutes that calculates the difference in minutes so
that's
not
the issue. I'm dividing the run time of a machine during the user
entered
time by the total time available between the user entered time to get
utilization. As I see it, I can:
1. Pass both of the actual date/time values from the unbound controls
and
calculate the difference in the report OR
2. Calculate the difference between the actual date/time values from
the
unbound controls in another unbound control on the forms and pass it to
the
report

Either way, how do I use OpenArgs to pass this value (to do
calculations)
in
addition to the string?

:

You will have a command button on your form that actually opens the
report.
In its Click event you need to build two strings: one as the
WhereCondition
for OpenReport, and the other as the description for the user.

In Access 2002 and 2003, you can pass the description through the
OpenArgs,
and just add a text box on the report with Control Source of:
=[Report].[OpenArgs]
In earlier versions, assign strDescrip to the Caption of a hidden
label
on
your form, and use the Format event of the Report Header section to
read
it,
and copy to a text box on the report.

This example shows how to create the 2 strings for different field
types,
using the different delimiters. Note that the date range example copes
with
the fact that the field may have a time component by asking for "less
than
the next day".

Private Sub cmdPreview_Click()
Dim strWhere As String 'Where Condition
Dim strDescrip As String 'Description of Where Condition.
Dim lngLen As Long 'Length of string.
Const conJetDate ="\#mm\/dd\/yyyy\#" 'Format for dates.

'Text field example.
If Not IsNull(Me.txtSurname) Then
strWhere = strWhere & "([Surname] = """ & Me.txtSurname & """)
AND "
strDescrip = strDescrip & "Surname of " & Me.txtSurname & ". "
End If

'Number field example.
If Not IsNull(Me.txtAmount) Then
strWhere = strWhere & "([Amount] >= " & Me.txtAmount & ") AND
"
strDescrip = strDescrip & "Amount at least " &
Format(Me.txtAmount,
"Currency") & ". "
End If

'Date range example
If Not IsNull(Me.txtStartDate) Then
strWhere = strWhere & "([MyDateField] >= " & _
Format(Me.txtStartDate, conJetDate) & ") AND "
strDescrip = ...
End If
If Not IsNull(Me.txtEndDate) Then
strWhere = strWhere & "([MyDateField] < " & _
Format(Me.txtEndDate + 1, conJetDate) & ") AND "
End If

'Chop of the trailing " AND "
lngLen = Len(strWhere) - 5
If lngLen > 0 Then
strWhere = Left$(strWhere, lngLen)
strDescrip = Left$(strDescrip, Len(strDescrip) - 2)
End If

'Open the report
DoCmd.OpenReport "MyReport", acViewPreview, , strWhere,
OpenArgs:=strDescrip
End Sub



Thanks for all of the help and support...just a couple more
questions...

I've created an unbound form that has unbound controls for
everything
that
a
user might want to filter on (like date range, machine, shift, etc.)

How do I use the information that the user enters in the unbound
controls
to:
1. Filter the results of query for the report
2. Display the user entry information on the report

For instance, the user enters a StartDate and EndDate for records.
The
query must limit the records to info bewteen those dates and then I
need
to
display the dates the user requested on the report.

 

[Guest]

Like this?

? Split([Report].[OpenArgs])(1)

Where do I put this? In the Control Source of an unbound text box? In the
Event Procedure of the OpenEvent of the report?

If you pass in a string such as:
"#1/1/2004#; #12/31/2004#; 365"
you can parse out the parts of the string as:
? Split("#1/1/2004#; #12/31/2004#; 365", ";")(1)
? Split("#1/1/2004#; #12/31/2004#; 365", ";")(2)

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

OK, now you lost me...

I added the results of my JustMinutes function to the OpenArgs so I now
have
a unbound text box on my report that says "between 10/01/04 07:00AM and
10/01/04 08:00AM;60" with the ;60 being the difference.

Where do I put the Split()? The help file on the Split function in VBA is
not very good.

You can pass as many values as you like in the OpenArgs string, delimited
by
(say) ";". Use Split() to parse them.

Another alternative is to use the Open event of the report to read the
values from the form, e.g.:

If CurrentProject.AllForms("Form1").IsLoaded Then
With Forms("Form1")
Debug.Print DateDiff("n", ![StartTime], ![EndTime])
End With
End If

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

OK, now I understand how to use the WhereCondition and how to use
OpenArgs
to
send strings through. In my example, I can send a string through
OpenArgs
that says "between 10/1/04 07:00AM and 10/4/04 09:00AM".

But, I also need either the values themselves or the difference between
these two values to do some calculations in the report. I already have
a
function JustMinutes that calculates the difference in minutes so
that's
not
the issue. I'm dividing the run time of a machine during the user
entered
time by the total time available between the user entered time to get
utilization. As I see it, I can:
1. Pass both of the actual date/time values from the unbound controls
and
calculate the difference in the report OR
2. Calculate the difference between the actual date/time values from
the
unbound controls in another unbound control on the forms and pass it to
the
report

Either way, how do I use OpenArgs to pass this value (to do
calculations)
in
addition to the string?

:

You will have a command button on your form that actually opens the
report.
In its Click event you need to build two strings: one as the
WhereCondition
for OpenReport, and the other as the description for the user.

In Access 2002 and 2003, you can pass the description through the
OpenArgs,
and just add a text box on the report with Control Source of:
=[Report].[OpenArgs]
In earlier versions, assign strDescrip to the Caption of a hidden
label
on
your form, and use the Format event of the Report Header section to
read
it,
and copy to a text box on the report.

This example shows how to create the 2 strings for different field
types,
using the different delimiters. Note that the date range example copes
with
the fact that the field may have a time component by asking for "less
than
the next day".

Private Sub cmdPreview_Click()
Dim strWhere As String 'Where Condition
Dim strDescrip As String 'Description of Where Condition.
Dim lngLen As Long 'Length of string.
Const conJetDate ="\#mm\/dd\/yyyy\#" 'Format for dates.

'Text field example.
If Not IsNull(Me.txtSurname) Then
strWhere = strWhere & "([Surname] = """ & Me.txtSurname & """)
AND "
strDescrip = strDescrip & "Surname of " & Me.txtSurname & ". "
End If

'Number field example.
If Not IsNull(Me.txtAmount) Then
strWhere = strWhere & "([Amount] >= " & Me.txtAmount & ") AND
"
strDescrip = strDescrip & "Amount at least " &
Format(Me.txtAmount,
"Currency") & ". "
End If

'Date range example
If Not IsNull(Me.txtStartDate) Then
strWhere = strWhere & "([MyDateField] >= " & _
Format(Me.txtStartDate, conJetDate) & ") AND "
strDescrip = ...
End If
If Not IsNull(Me.txtEndDate) Then
strWhere = strWhere & "([MyDateField] < " & _
Format(Me.txtEndDate + 1, conJetDate) & ") AND "
End If

'Chop of the trailing " AND "
lngLen = Len(strWhere) - 5
If lngLen > 0 Then
strWhere = Left$(strWhere, lngLen)
strDescrip = Left$(strDescrip, Len(strDescrip) - 2)
End If

'Open the report
DoCmd.OpenReport "MyReport", acViewPreview, , strWhere,
OpenArgs:=strDescrip
End Sub



Thanks for all of the help and support...just a couple more
questions...

I've created an unbound form that has unbound controls for
everything
that
a
user might want to filter on (like date range, machine, shift, etc.)

How do I use the information that the user enters in the unbound
controls
to:
1. Filter the results of query for the report
2. Display the user entry information on the report

For instance, the user enters a StartDate and EndDate for records.
The
query must limit the records to info bewteen those dates and then I
need
to
display the dates the user requested on the report.

 

[Allen Browne]

Where you put it depends on what you want to do with it.

Typically, you would use the Open event procedure of the report to parse the
string into your variables. From there you can perform math on the dates, or
concatenate the strings, or assign values to text boxes (typically in
Report_Header) to show them if you want.

It's your call how you use it. I was simply showing you how to pass multiple
values of different types via the OpenArgs string, and then parse them.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Like this?

? Split([Report].[OpenArgs])(1)

Where do I put this? In the Control Source of an unbound text box? In
the
Event Procedure of the OpenEvent of the report?

If you pass in a string such as:
"#1/1/2004#; #12/31/2004#; 365"
you can parse out the parts of the string as:
? Split("#1/1/2004#; #12/31/2004#; 365", ";")(1)
? Split("#1/1/2004#; #12/31/2004#; 365", ";")(2)

OK, now you lost me...

I added the results of my JustMinutes function to the OpenArgs so I now
have
a unbound text box on my report that says "between 10/01/04 07:00AM and
10/01/04 08:00AM;60" with the ;60 being the difference.

Where do I put the Split()? The help file on the Split function in VBA
is
not very good.

:

You can pass as many values as you like in the OpenArgs string,
delimited
by
(say) ";". Use Split() to parse them.

Another alternative is to use the Open event of the report to read the
values from the form, e.g.:

If CurrentProject.AllForms("Form1").IsLoaded Then
With Forms("Form1")
Debug.Print DateDiff("n", ![StartTime], ![EndTime])
End With
End If

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

OK, now I understand how to use the WhereCondition and how to use
OpenArgs
to
send strings through. In my example, I can send a string through
OpenArgs
that says "between 10/1/04 07:00AM and 10/4/04 09:00AM".

But, I also need either the values themselves or the difference
between
these two values to do some calculations in the report. I already
have
a
function JustMinutes that calculates the difference in minutes so
that's
not
the issue. I'm dividing the run time of a machine during the user
entered
time by the total time available between the user entered time to
get
utilization. As I see it, I can:
1. Pass both of the actual date/time values from the unbound
controls
and
calculate the difference in the report OR
2. Calculate the difference between the actual date/time values
from
the
unbound controls in another unbound control on the forms and pass it
to
the
report

Either way, how do I use OpenArgs to pass this value (to do
calculations)
in
addition to the string?

:

You will have a command button on your form that actually opens the
report.
In its Click event you need to build two strings: one as the
WhereCondition
for OpenReport, and the other as the description for the user.

In Access 2002 and 2003, you can pass the description through the
OpenArgs,
and just add a text box on the report with Control Source of:
=[Report].[OpenArgs]
In earlier versions, assign strDescrip to the Caption of a hidden
label
on
your form, and use the Format event of the Report Header section to
read
it,
and copy to a text box on the report.

This example shows how to create the 2 strings for different field
types,
using the different delimiters. Note that the date range example
copes
with
the fact that the field may have a time component by asking for
"less
than
the next day".

Private Sub cmdPreview_Click()
Dim strWhere As String 'Where Condition
Dim strDescrip As String 'Description of Where Condition.
Dim lngLen As Long 'Length of string.
Const conJetDate ="\#mm\/dd\/yyyy\#" 'Format for dates.

'Text field example.
If Not IsNull(Me.txtSurname) Then
strWhere = strWhere & "([Surname] = """ & Me.txtSurname &
""")
AND "
strDescrip = strDescrip & "Surname of " & Me.txtSurname &
". "
End If

'Number field example.
If Not IsNull(Me.txtAmount) Then
strWhere = strWhere & "([Amount] >= " & Me.txtAmount & ")
AND
"
strDescrip = strDescrip & "Amount at least " &
Format(Me.txtAmount,
"Currency") & ". "
End If

'Date range example
If Not IsNull(Me.txtStartDate) Then
strWhere = strWhere & "([MyDateField] >= " & _
Format(Me.txtStartDate, conJetDate) & ") AND "
strDescrip = ...
End If
If Not IsNull(Me.txtEndDate) Then
strWhere = strWhere & "([MyDateField] < " & _
Format(Me.txtEndDate + 1, conJetDate) & ") AND "
End If

'Chop of the trailing " AND "
lngLen = Len(strWhere) - 5
If lngLen > 0 Then
strWhere = Left$(strWhere, lngLen)
strDescrip = Left$(strDescrip, Len(strDescrip) - 2)
End If

'Open the report
DoCmd.OpenReport "MyReport", acViewPreview, , strWhere,
OpenArgs:=strDescrip
End Sub



message
Thanks for all of the help and support...just a couple more
questions...

I've created an unbound form that has unbound controls for
everything
that
a
user might want to filter on (like date range, machine, shift,
etc.)

How do I use the information that the user enters in the unbound
controls
to:
1. Filter the results of query for the report
2. Display the user entry information on the report

For instance, the user enters a StartDate and EndDate for
records.
The
query must limit the records to info bewteen those dates and then
I
need
to
display the dates the user requested on the report.

 

[Guest]

Great answer... that helped tremendously! One last question (hopefully):

I have a report with a subreport in it that are based on the same query.
From my unbound form, I click a button to OpenReport, passing the Where
statement with the user's filter criteria. How do I insure the same criteria
are passed to the subreport?

Where you put it depends on what you want to do with it.

Typically, you would use the Open event procedure of the report to parse the
string into your variables. From there you can perform math on the dates, or
concatenate the strings, or assign values to text boxes (typically in
Report_Header) to show them if you want.

It's your call how you use it. I was simply showing you how to pass multiple
values of different types via the OpenArgs string, and then parse them.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Like this?

? Split([Report].[OpenArgs])(1)

Where do I put this? In the Control Source of an unbound text box? In
the
Event Procedure of the OpenEvent of the report?

If you pass in a string such as:
"#1/1/2004#; #12/31/2004#; 365"
you can parse out the parts of the string as:
? Split("#1/1/2004#; #12/31/2004#; 365", ";")(1)
? Split("#1/1/2004#; #12/31/2004#; 365", ";")(2)


OK, now you lost me...

I added the results of my JustMinutes function to the OpenArgs so I now
have
a unbound text box on my report that says "between 10/01/04 07:00AM and
10/01/04 08:00AM;60" with the ;60 being the difference.

Where do I put the Split()? The help file on the Split function in VBA
is
not very good.

:

You can pass as many values as you like in the OpenArgs string,
delimited
by
(say) ";". Use Split() to parse them.

Another alternative is to use the Open event of the report to read the
values from the form, e.g.:

If CurrentProject.AllForms("Form1").IsLoaded Then
With Forms("Form1")
Debug.Print DateDiff("n", ![StartTime], ![EndTime])
End With
End If

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

OK, now I understand how to use the WhereCondition and how to use
OpenArgs
to
send strings through. In my example, I can send a string through
OpenArgs
that says "between 10/1/04 07:00AM and 10/4/04 09:00AM".

But, I also need either the values themselves or the difference
between
these two values to do some calculations in the report. I already
have
a
function JustMinutes that calculates the difference in minutes so
that's
not
the issue. I'm dividing the run time of a machine during the user
entered
time by the total time available between the user entered time to
get
utilization. As I see it, I can:
1. Pass both of the actual date/time values from the unbound
controls
and
calculate the difference in the report OR
2. Calculate the difference between the actual date/time values
from
the
unbound controls in another unbound control on the forms and pass it
to
the
report

Either way, how do I use OpenArgs to pass this value (to do
calculations)
in
addition to the string?

:

You will have a command button on your form that actually opens the
report.
In its Click event you need to build two strings: one as the
WhereCondition
for OpenReport, and the other as the description for the user.

In Access 2002 and 2003, you can pass the description through the
OpenArgs,
and just add a text box on the report with Control Source of:
=[Report].[OpenArgs]
In earlier versions, assign strDescrip to the Caption of a hidden
label
on
your form, and use the Format event of the Report Header section to
read
it,
and copy to a text box on the report.

This example shows how to create the 2 strings for different field
types,
using the different delimiters. Note that the date range example
copes
with
the fact that the field may have a time component by asking for
"less
than
the next day".

Private Sub cmdPreview_Click()
Dim strWhere As String 'Where Condition
Dim strDescrip As String 'Description of Where Condition.
Dim lngLen As Long 'Length of string.
Const conJetDate ="\#mm\/dd\/yyyy\#" 'Format for dates.

'Text field example.
If Not IsNull(Me.txtSurname) Then
strWhere = strWhere & "([Surname] = """ & Me.txtSurname &
""")
AND "
strDescrip = strDescrip & "Surname of " & Me.txtSurname &
". "
End If

'Number field example.
If Not IsNull(Me.txtAmount) Then
strWhere = strWhere & "([Amount] >= " & Me.txtAmount & ")
AND
"
strDescrip = strDescrip & "Amount at least " &
Format(Me.txtAmount,
"Currency") & ". "
End If

'Date range example
If Not IsNull(Me.txtStartDate) Then
strWhere = strWhere & "([MyDateField] >= " & _
Format(Me.txtStartDate, conJetDate) & ") AND "
strDescrip = ...
End If
If Not IsNull(Me.txtEndDate) Then
strWhere = strWhere & "([MyDateField] < " & _
Format(Me.txtEndDate + 1, conJetDate) & ") AND "
End If

'Chop of the trailing " AND "
lngLen = Len(strWhere) - 5
If lngLen > 0 Then
strWhere = Left$(strWhere, lngLen)
strDescrip = Left$(strDescrip, Len(strDescrip) - 2)
End If

'Open the report
DoCmd.OpenReport "MyReport", acViewPreview, , strWhere,
OpenArgs:=strDescrip
End Sub



message
Thanks for all of the help and support...just a couple more
questions...

I've created an unbound form that has unbound controls for
everything
that
a
user might want to filter on (like date range, machine, shift,
etc.)

How do I use the information that the user enters in the unbound
controls
to:
1. Filter the results of query for the report
2. Display the user entry information on the report

For instance, the user enters a StartDate and EndDate for
records.
The
query must limit the records to info bewteen those dates and then
I
need
to
display the dates the user requested on the report.

 

[Allen Browne]

The simplest way to do that is to base the subreport on a query that reads
the values directly from the form, i.e. the Criteria row of the query
contains things like:
[Forms].[Form1].[Text1]

If you want other alternatives, the question has been asked lots of times.
Go to groups.google.com, choose the Advanced Search, and search the group
microsoft.public.access.reports