Macro for form for collecting report parameters

  • Thread starter Thread starter Owl
  • Start date Start date
O

Owl

I created a DialogBox form to collect parameters for a report. The macro has
3 little yellow triangles denoting a problem on the last lines 3, 4 and 5.
Please could somebody tell me how to fix this.

Query: Q04_ForReportWithDialogBox
Report: R04_WithDialogBox
Form: FD_ParameterCollector
Module: Module1
Macro: M01_ParameterCollector

MACRO
FIRST LINE
Macro name: Open Dialog
Condition: (Blank)
Action: OpenForm
Arguments: FD_ParameterCollector, Form

SECOND LINE
Macro name: (Blank)
Condition: NotIsLoaded(“FD_ParameterCollectorâ€)
Action: CancelEvent
Arguments: (Blank)

THIRD LINE
Macro name: Close Dialog
Condition: (Blank)
Action: Close
Arguments: FD_ParameterCollector, Form

FOURTH LINE
Macro name: OK
Condition: (Blank)
Action: SetValue
Arguments: [Visible], No

FIFTH LINE
Macro name: Cancel
Condition:
Action: Close
Arguments: FD_ParameterCollector, Form

The module is as follows:
Function IsLoaded(ByVal strFormName As String) As Boolean

Dim oAccessObject As AccessObject
Set oAccessObject = CurrentProject.AllForms(strFormName)

If oAccessObject.IsLoaded Then
If oAccessObject.CurrentView <> acCurViewDesign Then
IsLoaded = True
End If
End If
End Function

Thanks for any help.
 
Owl,

No, the little triangles do not denote a problem. They denote that these
actions would not work if the database was not deployed to a Trusted
Location. If you have the folder set up as a Trusted Location, you're all
right.
 
Thanks for the reply. I don't understand because not only do I have the
folder that the file is in as a trusted location, but I also have all macros
enabled. I have just checked that both of these facts are still correct and
they are.

Steve Schapel said:
Owl,

No, the little triangles do not denote a problem. They denote that these
actions would not work if the database was not deployed to a Trusted
Location. If you have the folder set up as a Trusted Location, you're all
right.

--
Steve Schapel, Microsoft Access MVP


Owl said:
I created a DialogBox form to collect parameters for a report. The macro
has
3 little yellow triangles denoting a problem on the last lines 3, 4 and 5.
Please could somebody tell me how to fix this.

Query: Q04_ForReportWithDialogBox
Report: R04_WithDialogBox
Form: FD_ParameterCollector
Module: Module1
Macro: M01_ParameterCollector

MACRO
FIRST LINE
Macro name: Open Dialog
Condition: (Blank)
Action: OpenForm
Arguments: FD_ParameterCollector, Form

SECOND LINE
Macro name: (Blank)
Condition: NotIsLoaded(“FD_ParameterCollectorâ€)
Action: CancelEvent
Arguments: (Blank)

THIRD LINE
Macro name: Close Dialog
Condition: (Blank)
Action: Close
Arguments: FD_ParameterCollector, Form

FOURTH LINE
Macro name: OK
Condition: (Blank)
Action: SetValue
Arguments: [Visible], No

FIFTH LINE
Macro name: Cancel
Condition:
Action: Close
Arguments: FD_ParameterCollector, Form

The module is as follows:
Function IsLoaded(ByVal strFormName As String) As Boolean

Dim oAccessObject As AccessObject
Set oAccessObject = CurrentProject.AllForms(strFormName)

If oAccessObject.IsLoaded Then
If oAccessObject.CurrentView <> acCurViewDesign Then
IsLoaded = True
End If
End If
End Function

Thanks for any help.
Click to expand...
Click to expand...
 
I click on the report and the parameter form opens. I fill in the details (a
from time, a to time, two text entries and a check box) and click on OK and
it takes me to the report, but the report shows Error and not any data - or I
click on Cancel and a message appears telling me it can't find the Cancel
macro in the macro group. I have checked the name and details of the macro
time and time again, and it is as the message refers to it as. The only
thing I can see that is wrong is those 3 little yellow triangles.
 
As Steve has already explained, there is nothing "wrong" denoted by the
warning flags. They are there because that macro line (.e.g SetValue) calls
a process whichis not considered "trusted" outside a Trusted Location and
they would appear there in any event, Trusted Location or Not.
Unfortunately, the "warning" aspect of those flags looks a lot like
"problem" flag. And that can be misleading.

Look at the calls earlier in your macro. One thing I've learned about macros
is that you don't necessarily get any indication that a line has failed to
produce the results you expect, even if it doesn't. Lacking true
error-handling and debuggin makes it more difficult, but one trick I've
employed is to insert MsgBox statements along the way in a macro to display
values for TempVars, etc. That does give you some indication as to whether
your macro is actually returning the values you think it should.

HTH

George
 
Owl,

In addition to George's comments, I would also say that if the report opens,
but you see #Error in controls on the report, and/or the expected data is
not returned, then this is unlikely to have anything to do with the macro
itself. It is more related to the design of the report or the query that
the report is based on.

If possible, could you look at the SQL view of the query that the report is
based on, and copy/paste into your reply here?

Also, please give the details of your macro.
 
Thanks for sticking with me, Steve.

Here is the SQL of the query:

SELECT T0.T0_ID, T0.ItemDate, T0.Entry, T0.SubEntry, T0.Closed, T0.Note
FROM T0
WHERE (((T0.ItemDate) Between [Forms]![FD_ParameterCollector]![From date]
And [Forms]![FD_ParameterCollector]![To date]) AND
((T0.Entry)=[Forms]![FD_ParameterCollector].[Entry]) AND
((T0.SubEntry)=[Forms]![FD_ParameterCollector].[SubEntry]) AND
((T0.Closed)=[Forms]![FD_ParameterCollector].[Closed]))
ORDER BY T0.ItemDate, T0.Entry, T0.SubEntry;

Here is the message I get when I press Cancel on the macro:

Microsoft Access can’t find the macro ‘Cancel
M01_ParameterCollector.Cancel’ in the macro group ‘M01_ParameterCollector.’

You used the macrogroupname.macroname syntax to specify a macro. You then
tried to run the macro (directly or indirectly), or you used the Run Macro
method to run the macro. However, the macro you specified isn’t in this
macro group.

Create the macro in the macro group, specify the correct macro, or specify
the correct macro name.

then when I press OK:

Cannot open or run macro ‘M01_ParameterCollector’ because it is not valid.

The full macro details including the OK and Cancel buttons are in the first
thread of this post.

I don’t understand any of what George means by:

“Lacking true error-handling and debuggin makes it more difficult, but one
trick I've
employed is to insert MsgBox statements along the way in a macro to display
values for TempVars, etc.â€
 
Further to the above:

The form has the following unbound text fields and its source is the query
(Q04_ForReportWithDialogBox):

From date
To date
Entry
SubEntry

and the following Yes/No field

Closed

and an OK command button
and a Cancel command button

OnClick for the OK command button is:

M01_ParameterCollector.OK

OnClick for the Cancel command button is:

M01_ParameterCollector.Cancel


Owl said:
Thanks for sticking with me, Steve.

Here is the SQL of the query:

SELECT T0.T0_ID, T0.ItemDate, T0.Entry, T0.SubEntry, T0.Closed, T0.Note
FROM T0
WHERE (((T0.ItemDate) Between [Forms]![FD_ParameterCollector]![From date]
And [Forms]![FD_ParameterCollector]![To date]) AND
((T0.Entry)=[Forms]![FD_ParameterCollector].[Entry]) AND
((T0.SubEntry)=[Forms]![FD_ParameterCollector].[SubEntry]) AND
((T0.Closed)=[Forms]![FD_ParameterCollector].[Closed]))
ORDER BY T0.ItemDate, T0.Entry, T0.SubEntry;

Here is the message I get when I press Cancel on the macro:

Microsoft Access can’t find the macro ‘Cancel
M01_ParameterCollector.Cancel’ in the macro group ‘M01_ParameterCollector.’

You used the macrogroupname.macroname syntax to specify a macro. You then
tried to run the macro (directly or indirectly), or you used the Run Macro
method to run the macro. However, the macro you specified isn’t in this
macro group.

Create the macro in the macro group, specify the correct macro, or specify
the correct macro name.

then when I press OK:

Cannot open or run macro ‘M01_ParameterCollector’ because it is not valid.

The full macro details including the OK and Cancel buttons are in the first
thread of this post.

I don’t understand any of what George means by:

“Lacking true error-handling and debuggin makes it more difficult, but one
trick I've
employed is to insert MsgBox statements along the way in a macro to display
values for TempVars, etc.â€


Steve Schapel said:
Owl,

In addition to George's comments, I would also say that if the report opens,
but you see #Error in controls on the report, and/or the expected data is
not returned, then this is unlikely to have anything to do with the macro
itself. It is more related to the design of the report or the query that
the report is based on.

If possible, could you look at the SQL view of the query that the report is
based on, and copy/paste into your reply here?

Also, please give the details of your macro.
Click to expand...
Click to expand...
 
Yet further to the above:

I thought to magnify the "errored" report that comes up and it only has the
Closed field which is a Yes/No field. I would imagine that this means that
the Closed field is the only error somewhere along the line. Is it possibly
that what is entered in the Criteria of the field Closed of the query is
wrong? Here is what I entered there:

[Forms]![FD_ParameterCollector].[Closed]

Owl said:
Further to the above:

The form has the following unbound text fields and its source is the query
(Q04_ForReportWithDialogBox):

From date
To date
Entry
SubEntry

and the following Yes/No field

Closed

and an OK command button
and a Cancel command button

OnClick for the OK command button is:

M01_ParameterCollector.OK

OnClick for the Cancel command button is:

M01_ParameterCollector.Cancel


Owl said:
Thanks for sticking with me, Steve.

Here is the SQL of the query:

SELECT T0.T0_ID, T0.ItemDate, T0.Entry, T0.SubEntry, T0.Closed, T0.Note
FROM T0
WHERE (((T0.ItemDate) Between [Forms]![FD_ParameterCollector]![From date]
And [Forms]![FD_ParameterCollector]![To date]) AND
((T0.Entry)=[Forms]![FD_ParameterCollector].[Entry]) AND
((T0.SubEntry)=[Forms]![FD_ParameterCollector].[SubEntry]) AND
((T0.Closed)=[Forms]![FD_ParameterCollector].[Closed]))
ORDER BY T0.ItemDate, T0.Entry, T0.SubEntry;

Here is the message I get when I press Cancel on the macro:

Microsoft Access can’t find the macro ‘Cancel
M01_ParameterCollector.Cancel’ in the macro group ‘M01_ParameterCollector.’

You used the macrogroupname.macroname syntax to specify a macro. You then
tried to run the macro (directly or indirectly), or you used the Run Macro
method to run the macro. However, the macro you specified isn’t in this
macro group.

Create the macro in the macro group, specify the correct macro, or specify
the correct macro name.

then when I press OK:

Cannot open or run macro ‘M01_ParameterCollector’ because it is not valid.

The full macro details including the OK and Cancel buttons are in the first
thread of this post.

I don’t understand any of what George means by:

“Lacking true error-handling and debuggin makes it more difficult, but one
trick I've
employed is to insert MsgBox statements along the way in a macro to display
values for TempVars, etc.â€


Steve Schapel said:
Owl,

In addition to George's comments, I would also say that if the report opens,
but you see #Error in controls on the report, and/or the expected data is
not returned, then this is unlikely to have anything to do with the macro
itself. It is more related to the design of the report or the query that
the report is based on.

If possible, could you look at the SQL view of the query that the report is
based on, and copy/paste into your reply here?

Also, please give the details of your macro.

--
Steve Schapel, Microsoft Access MVP


As Steve has already explained, there is nothing "wrong" denoted by the
warning flags. They are there because that macro line (.e.g SetValue)
calls a process whichis not considered "trusted" outside a Trusted
Location and they would appear there in any event, Trusted Location or
Not. Unfortunately, the "warning" aspect of those flags looks a lot like
"problem" flag. And that can be misleading.

Look at the calls earlier in your macro. One thing I've learned about
macros is that you don't necessarily get any indication that a line has
failed to produce the results you expect, even if it doesn't. Lacking true
error-handling and debuggin makes it more difficult, but one trick I've
employed is to insert MsgBox statements along the way in a macro to
display values for TempVars, etc. That does give you some indication as to
whether your macro is actually returning the values you think it should.

HTH

George





I click on the report and the parameter form opens. I fill in the details
(a
from time, a to time, two text entries and a check box) and click on OK
and
it takes me to the report, but the report shows Error and not any data -
or I
click on Cancel and a message appears telling me it can't find the Cancel
macro in the macro group. I have checked the name and details of the
macro
time and time again, and it is as the message refers to it as. The only
thing I can see that is wrong is those 3 little yellow triangles.
Click to expand...
Click to expand...
Click to expand...
 
Owl,

Thanks for the very clear information.

Unfortunately I cannot see anything out of kilter so far.

Can you go to the design view of the report, and look at the Control Source
property of all the data controls on the form, and make sure that they all
correctly show the names of fields from the query?
 
I did as you said and every single one is correct.

Steve Schapel said:
Owl,

Thanks for the very clear information.

Unfortunately I cannot see anything out of kilter so far.

Can you go to the design view of the report, and look at the Control Source
property of all the data controls on the form, and make sure that they all
correctly show the names of fields from the query?

--
Steve Schapel, Microsoft Access MVP


Owl said:
Yet further to the above:

I thought to magnify the "errored" report that comes up and it only has
the
Closed field which is a Yes/No field. I would imagine that this means
that
the Closed field is the only error somewhere along the line. Is it
possibly
that what is entered in the Criteria of the field Closed of the query is
wrong? Here is what I entered there:

[Forms]![FD_ParameterCollector].[Closed]
Click to expand...
Click to expand...
 
Owl,

When you look at the On Click event property of the 'Cancel' button, do you
see:
M01_ParameterCollector.Cancel
.... or do you see something else?

Also, does it make any difference if you leave the Object Type and Object
Name arguments blank for the Cancel macro?

--
Steve Schapel, Microsoft Access MVP


Owl said:
I did as you said and every single one is correct.

Steve Schapel said:
Owl,

Thanks for the very clear information.

Unfortunately I cannot see anything out of kilter so far.

Can you go to the design view of the report, and look at the Control
Source
property of all the data controls on the form, and make sure that they
all
correctly show the names of fields from the query?

--
Steve Schapel, Microsoft Access MVP


Owl said:
Yet further to the above:

I thought to magnify the "errored" report that comes up and it only has
the
Closed field which is a Yes/No field. I would imagine that this means
that
the Closed field is the only error somewhere along the line. Is it
possibly
that what is entered in the Criteria of the field Closed of the query
is
wrong? Here is what I entered there:

[Forms]![FD_ParameterCollector].[Closed]
Click to expand...
Click to expand...
Click to expand...
 
Yes, I see M01_ParameterCollector.Cancel.

However, it doesn't make any difference if I take out the Object Type and
Object
Name arguments blank for the Cancel macro. Does that mean that we have
isolated the problem (or one of them)?

Is it possible that more code is needed to for the Yes/No field in the SQL
and/or query?

Thank you for your time and patience.

Steve Schapel said:
Owl,

When you look at the On Click event property of the 'Cancel' button, do you
see:
M01_ParameterCollector.Cancel
.... or do you see something else?

Also, does it make any difference if you leave the Object Type and Object
Name arguments blank for the Cancel macro?

--
Steve Schapel, Microsoft Access MVP


Owl said:
I did as you said and every single one is correct.

Steve Schapel said:
Owl,

Thanks for the very clear information.

Unfortunately I cannot see anything out of kilter so far.

Can you go to the design view of the report, and look at the Control
Source
property of all the data controls on the form, and make sure that they
all
correctly show the names of fields from the query?

--
Steve Schapel, Microsoft Access MVP


Yet further to the above:

I thought to magnify the "errored" report that comes up and it only has
the
Closed field which is a Yes/No field. I would imagine that this means
that
the Closed field is the only error somewhere along the line. Is it
possibly
that what is entered in the Criteria of the field Closed of the query
is
wrong? Here is what I entered there:

[Forms]![FD_ParameterCollector].[Closed]
Click to expand...
Click to expand...
Click to expand...
 
Owl,

I am very sorry, I am unable to see any reason for this problem.

If you would care to zip up a suitably cut-down copy of the database, and
email it to me, I can have a look at it.
steves at mvps dot org
 
Thank you very much. I will do that. It is a sample file, so zipped it
should be small enough. I will email it to you right now.
 
Owl,

I have only had a chance for a very quick look so far. But I found that
there is something wrong with the entry of the macro names in the On Click
property of the buttons, and re-entering the macro names in there seems to
solve at least part of the problem.
 
Thank you for your time and determination. I appreciate it tremendously.

As a result of what you have said, and what I have subsequently found on
Microsoft Help, I am wondering if I don’t simply need to change the Sandbox
Registry Value from a 3 to a 2. What do you think?
 
Owl,

I think this is an illogical problem that requires an illogical response.
Something has gone haywire with the assignment of your macros to the event
properties. I can't see any reason for it, but stuff like that happens
sometimes. Re-entering the macro names seems to re-set them. Probably you
need to compact/repair your file more often as changes are being made.
Sorry, that's about as precise as I can be.
 
Thanks very much indeed for your time and for trying. I really do appreciate
it. Actually it is a brandnew database and I have tried the same in several
other brandnew databases and got the same result. I am going to try what I
suggested in my previous response. My guess is that it is the Sandbox
problem as a result of having just upgraded from 2003 to 2007.
 
Back
Top