Question reg Form and Report Parameters

[TotallyConfused]

I am trying to follow the instructions on the Access Microsoft Office page on
Creating a Form that collects Report Parameters from the following link
office.microsoft.com/en_us/access/HA100963141033.aspx?mode=print and I am
stuck on the following step. My understanding of this is that I needd to add
the following: Between [Forms]![Parameter Collector]![Beginning Date] And
[Forms]![Parameter Collector]![Ending Date] in the query of the report.

I do not have date fields in my query for this report. Do I have to have
them? I do not need to store these dates in my db. I just need to enter
these dates in the report to be able to print or send via fax. Can you
please help on how I can revise this? What do I need to do? Thank you.


Step 5: Enter the criteria in the underlying query or stored procedure for
the report
Open the underlying query or stored procedure for the report in Design view.
Enter the criteria for the data. In the expression (expression: Any
combination of mathematical or logical operators, constants, functions, and
names of fields, controls, and properties that evaluates to a single value.
Expressions can perform calculations, manipulate characters, or test data.),
use the Forms object, the name of the form, and the names of the controls
(control: A graphical user interface object, such as a text box, check box,
scroll bar, or command button, that lets users control the program. You use
controls to display data or choices, perform an action, or make the user
interface easier to read.) in the criteria:
For example, in an Access database (.accdb or .mdb), for a form named
Parameter Collector, you use the following expression to refer to controls
named Beginning Date and Ending Date in the query:
Between [Forms]![Parameter Collector]![Beginning Date] And
[Forms]![Parameter Collector]![Ending Date]

In an Access project (Microsoft Access project: An Access file that connects
to a Microsoft SQL Server database and is used to create client/server
applications. A project file doesn't contain any data or
data-definition-based objects such as tables and views.) (.adp), you must
first explicitly name the parameters in the stored procedure, for example:
@Beginning_Date datetime, @Ending_Date datetime

You then use those parameters in the WHERE clause, for example:

WHERE Sales.ShippedDate Between @Beginning_Date And

 

[Klatuu]

It doesn't have to be dates. What fields in your query are you trying filter
on?

 

[TotallyConfused]

Oh thank you for responding. I had posted regarding this before the
holidays. I am now getting back to this. After I posted this today, I was
able to make the form work. My parameter form text boxes are referenced in
the reports text boxes control source (example: =Forms.[Parameter
Collector].txtPreStartDate & " and "). This is working fine. My
paramenter form pops up before the report open and prompts for the dates,
dates get entered into the correct dates text boxes in the report, and I am
able to print the report fine. However, except for when there is two
instances of the report the same dates get entered into the two instances of
the report. Each instance of the report will have different dates for
different pts. Can this be fixed to have the parameter form pop up twice for
each report instance? I would appreciate any help you can provide as this
would accomplish what I need for this report. Thank you in advance for any
help you can provide.

It doesn't have to be dates. What fields in your query are you trying filter
on?
--
Dave Hargis, Microsoft Access MVP

I am trying to follow the instructions on the Access Microsoft Office page on
Creating a Form that collects Report Parameters from the following link
office.microsoft.com/en_us/access/HA100963141033.aspx?mode=print and I am
stuck on the following step. My understanding of this is that I needd to add
the following: Between [Forms]![Parameter Collector]![Beginning Date] And
[Forms]![Parameter Collector]![Ending Date] in the query of the report.

I do not have date fields in my query for this report. Do I have to have
them? I do not need to store these dates in my db. I just need to enter
these dates in the report to be able to print or send via fax. Can you
please help on how I can revise this? What do I need to do? Thank you.


Step 5: Enter the criteria in the underlying query or stored procedure for
the report
Open the underlying query or stored procedure for the report in Design view.
Enter the criteria for the data. In the expression (expression: Any
combination of mathematical or logical operators, constants, functions, and
names of fields, controls, and properties that evaluates to a single value.
Expressions can perform calculations, manipulate characters, or test data.),
use the Forms object, the name of the form, and the names of the controls
(control: A graphical user interface object, such as a text box, check box,
scroll bar, or command button, that lets users control the program. You use
controls to display data or choices, perform an action, or make the user
interface easier to read.) in the criteria:
For example, in an Access database (.accdb or .mdb), for a form named
Parameter Collector, you use the following expression to refer to controls
named Beginning Date and Ending Date in the query:
Between [Forms]![Parameter Collector]![Beginning Date] And
[Forms]![Parameter Collector]![Ending Date]

In an Access project (Microsoft Access project: An Access file that connects
to a Microsoft SQL Server database and is used to create client/server
applications. A project file doesn't contain any data or
data-definition-based objects such as tables and views.) (.adp), you must
first explicitly name the parameters in the stored procedure, for example:
@Beginning_Date datetime, @Ending_Date datetime

You then use those parameters in the WHERE clause, for example:

WHERE Sales.ShippedDate Between @Beginning_Date And

 

[Klatuu]

Can you post the code where you are opening the form and report?
--
Dave Hargis, Microsoft Access MVP

Oh thank you for responding. I had posted regarding this before the
holidays. I am now getting back to this. After I posted this today, I was
able to make the form work. My parameter form text boxes are referenced in
the reports text boxes control source (example: =Forms.[Parameter
Collector].txtPreStartDate & " and "). This is working fine. My
paramenter form pops up before the report open and prompts for the dates,
dates get entered into the correct dates text boxes in the report, and I am
able to print the report fine. However, except for when there is two
instances of the report the same dates get entered into the two instances of
the report. Each instance of the report will have different dates for
different pts. Can this be fixed to have the parameter form pop up twice for
each report instance? I would appreciate any help you can provide as this
would accomplish what I need for this report. Thank you in advance for any
help you can provide.

It doesn't have to be dates. What fields in your query are you trying filter
on?
--
Dave Hargis, Microsoft Access MVP

I am trying to follow the instructions on the Access Microsoft Office page on
Creating a Form that collects Report Parameters from the following link
office.microsoft.com/en_us/access/HA100963141033.aspx?mode=print and I am
stuck on the following step. My understanding of this is that I needd to add
the following: Between [Forms]![Parameter Collector]![Beginning Date] And
[Forms]![Parameter Collector]![Ending Date] in the query of the report.

I do not have date fields in my query for this report. Do I have to have
them? I do not need to store these dates in my db. I just need to enter
these dates in the report to be able to print or send via fax. Can you
please help on how I can revise this? What do I need to do? Thank you.


Step 5: Enter the criteria in the underlying query or stored procedure for
the report
Open the underlying query or stored procedure for the report in Design view.
Enter the criteria for the data. In the expression (expression: Any
combination of mathematical or logical operators, constants, functions, and
names of fields, controls, and properties that evaluates to a single value.
Expressions can perform calculations, manipulate characters, or test data.),
use the Forms object, the name of the form, and the names of the controls
(control: A graphical user interface object, such as a text box, check box,
scroll bar, or command button, that lets users control the program. You use
controls to display data or choices, perform an action, or make the user
interface easier to read.) in the criteria:
For example, in an Access database (.accdb or .mdb), for a form named
Parameter Collector, you use the following expression to refer to controls
named Beginning Date and Ending Date in the query:
Between [Forms]![Parameter Collector]![Beginning Date] And
[Forms]![Parameter Collector]![Ending Date]

In an Access project (Microsoft Access project: An Access file that connects
to a Microsoft SQL Server database and is used to create client/server
applications. A project file doesn't contain any data or
data-definition-based objects such as tables and views.) (.adp), you must
first explicitly name the parameters in the stored procedure, for example:
@Beginning_Date datetime, @Ending_Date datetime

You then use those parameters in the WHERE clause, for example:

WHERE Sales.ShippedDate Between @Beginning_Date And

 

[TotallyConfused]

Thank you for responding. There is not much code. I followed the
instructions from the Microsoft Office paper, "Use Parameters in queries and
Reports - Create a form that collects report parameters"
(http://office.microsoft.com/en-us/access/HA100963141033.aspx). According to
this, there is a macro group that controls the Parameter Collector form. The
macro consists of the following Macros for the Parameter Collector form

Macro Name = Open Dialog - Action = Open Form - Form Name = Parameter
Collector - View = Form - Data Mode = Edit - Window Mode = Dialog

Condition = Not IsLoaded("Parameter Collector") - Action = CancelEvent

Macro Name = Close Dialog - Action = Close - Object Type = Form - Object
Name = Parameter Collector - Save = No

Macro Name = OK - Action = SetValue - Item = [Visible] - Expression = No
Macro Name = Cancel - Action = Close - Object Type = Form - Object Name =
Parameter Collector - Save = No

This function was also added to a Module:

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

This is the only code in the actual Report:

Private Sub Report_Open(Cancel As Integer)
DoCmd.OpenForm "Parameter Collector", acNormal
End Sub







Private Sub Report_Open(Cancel As Integer)
DoCmd.OpenForm "Parameter Collector", acNormal
End Sub

Can you post the code where you are opening the form and report?
--
Dave Hargis, Microsoft Access MVP

Oh thank you for responding. I had posted regarding this before the
holidays. I am now getting back to this. After I posted this today, I was
able to make the form work. My parameter form text boxes are referenced in
the reports text boxes control source (example: =Forms.[Parameter
Collector].txtPreStartDate & " and "). This is working fine. My
paramenter form pops up before the report open and prompts for the dates,
dates get entered into the correct dates text boxes in the report, and I am
able to print the report fine. However, except for when there is two
instances of the report the same dates get entered into the two instances of
the report. Each instance of the report will have different dates for
different pts. Can this be fixed to have the parameter form pop up twice for
each report instance? I would appreciate any help you can provide as this
would accomplish what I need for this report. Thank you in advance for any
help you can provide.

It doesn't have to be dates. What fields in your query are you trying filter
on?
--
Dave Hargis, Microsoft Access MVP


:

I am trying to follow the instructions on the Access Microsoft Office page on
Creating a Form that collects Report Parameters from the following link
office.microsoft.com/en_us/access/HA100963141033.aspx?mode=print and I am
stuck on the following step. My understanding of this is that I needd to add
the following: Between [Forms]![Parameter Collector]![Beginning Date] And
[Forms]![Parameter Collector]![Ending Date] in the query of the report.

I do not have date fields in my query for this report. Do I have to have
them? I do not need to store these dates in my db. I just need to enter
these dates in the report to be able to print or send via fax. Can you
please help on how I can revise this? What do I need to do? Thank you.


Step 5: Enter the criteria in the underlying query or stored procedure for
the report
Open the underlying query or stored procedure for the report in Design view.
Enter the criteria for the data. In the expression (expression: Any
combination of mathematical or logical operators, constants, functions, and
names of fields, controls, and properties that evaluates to a single value.
Expressions can perform calculations, manipulate characters, or test data.),
use the Forms object, the name of the form, and the names of the controls
(control: A graphical user interface object, such as a text box, check box,
scroll bar, or command button, that lets users control the program. You use
controls to display data or choices, perform an action, or make the user
interface easier to read.) in the criteria:
For example, in an Access database (.accdb or .mdb), for a form named
Parameter Collector, you use the following expression to refer to controls
named Beginning Date and Ending Date in the query:
Between [Forms]![Parameter Collector]![Beginning Date] And
[Forms]![Parameter Collector]![Ending Date]

In an Access project (Microsoft Access project: An Access file that connects
to a Microsoft SQL Server database and is used to create client/server
applications. A project file doesn't contain any data or
data-definition-based objects such as tables and views.) (.adp), you must
first explicitly name the parameters in the stored procedure, for example:
@Beginning_Date datetime, @Ending_Date datetime

You then use those parameters in the WHERE clause, for example:

WHERE Sales.ShippedDate Between @Beginning_Date And

 

[Klatuu]

The first thing I noticed is you are using spaces in names. It is always a
bad idea, but in this case, you can bypass that by putting the name in
brackets:

Form Name = [Parameter Collector]

Where you have it in quotes, leave it as it is.
--
Dave Hargis, Microsoft Access MVP

Thank you for responding. There is not much code. I followed the
instructions from the Microsoft Office paper, "Use Parameters in queries and
Reports - Create a form that collects report parameters"
(http://office.microsoft.com/en-us/access/HA100963141033.aspx). According to
this, there is a macro group that controls the Parameter Collector form. The
macro consists of the following Macros for the Parameter Collector form

Macro Name = Open Dialog - Action = Open Form - Form Name = Parameter
Collector - View = Form - Data Mode = Edit - Window Mode = Dialog

Condition = Not IsLoaded("Parameter Collector") - Action = CancelEvent

Macro Name = Close Dialog - Action = Close - Object Type = Form - Object
Name = Parameter Collector - Save = No

Macro Name = OK - Action = SetValue - Item = [Visible] - Expression = No
Macro Name = Cancel - Action = Close - Object Type = Form - Object Name =
Parameter Collector - Save = No

This function was also added to a Module:

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

This is the only code in the actual Report:

Private Sub Report_Open(Cancel As Integer)
DoCmd.OpenForm "Parameter Collector", acNormal
End Sub







Private Sub Report_Open(Cancel As Integer)
DoCmd.OpenForm "Parameter Collector", acNormal
End Sub

Can you post the code where you are opening the form and report?
--
Dave Hargis, Microsoft Access MVP

Oh thank you for responding. I had posted regarding this before the
holidays. I am now getting back to this. After I posted this today, I was
able to make the form work. My parameter form text boxes are referenced in
the reports text boxes control source (example: =Forms.[Parameter
Collector].txtPreStartDate & " and "). This is working fine. My
paramenter form pops up before the report open and prompts for the dates,
dates get entered into the correct dates text boxes in the report, and I am
able to print the report fine. However, except for when there is two
instances of the report the same dates get entered into the two instances of
the report. Each instance of the report will have different dates for
different pts. Can this be fixed to have the parameter form pop up twice for
each report instance? I would appreciate any help you can provide as this
would accomplish what I need for this report. Thank you in advance for any
help you can provide.

:

It doesn't have to be dates. What fields in your query are you trying filter
on?
--
Dave Hargis, Microsoft Access MVP


:

I am trying to follow the instructions on the Access Microsoft Office page on
Creating a Form that collects Report Parameters from the following link
office.microsoft.com/en_us/access/HA100963141033.aspx?mode=print and I am
stuck on the following step. My understanding of this is that I needd to add
the following: Between [Forms]![Parameter Collector]![Beginning Date] And
[Forms]![Parameter Collector]![Ending Date] in the query of the report.

I do not have date fields in my query for this report. Do I have to have
them? I do not need to store these dates in my db. I just need to enter
these dates in the report to be able to print or send via fax. Can you
please help on how I can revise this? What do I need to do? Thank you.


Step 5: Enter the criteria in the underlying query or stored procedure for
the report
Open the underlying query or stored procedure for the report in Design view.
Enter the criteria for the data. In the expression (expression: Any
combination of mathematical or logical operators, constants, functions, and
names of fields, controls, and properties that evaluates to a single value.
Expressions can perform calculations, manipulate characters, or test data.),
use the Forms object, the name of the form, and the names of the controls
(control: A graphical user interface object, such as a text box, check box,
scroll bar, or command button, that lets users control the program. You use
controls to display data or choices, perform an action, or make the user
interface easier to read.) in the criteria:
For example, in an Access database (.accdb or .mdb), for a form named
Parameter Collector, you use the following expression to refer to controls
named Beginning Date and Ending Date in the query:
Between [Forms]![Parameter Collector]![Beginning Date] And
[Forms]![Parameter Collector]![Ending Date]

In an Access project (Microsoft Access project: An Access file that connects
to a Microsoft SQL Server database and is used to create client/server
applications. A project file doesn't contain any data or
data-definition-based objects such as tables and views.) (.adp), you must
first explicitly name the parameters in the stored procedure, for example:
@Beginning_Date datetime, @Ending_Date datetime

You then use those parameters in the WHERE clause, for example:

WHERE Sales.ShippedDate Between @Beginning_Date And

 

[TotallyConfused]

Okay Thank you. I fixed the Form name. But you did not answer my question
about how to get the Parameter Form to prompt for each instance of the report
when there are more than one instance. Right now it only prompts once and if
there are more than one instances of the report it generates the same dates
to both reports. I need to somehow get the parameter form to synchronize
with the pt id or something like that? Is this possible? Can you please
help with this? Thank you in advance.

The first thing I noticed is you are using spaces in names. It is always a
bad idea, but in this case, you can bypass that by putting the name in
brackets:

Form Name = [Parameter Collector]

Where you have it in quotes, leave it as it is.
--
Dave Hargis, Microsoft Access MVP

Thank you for responding. There is not much code. I followed the
instructions from the Microsoft Office paper, "Use Parameters in queries and
Reports - Create a form that collects report parameters"
(http://office.microsoft.com/en-us/access/HA100963141033.aspx). According to
this, there is a macro group that controls the Parameter Collector form. The
macro consists of the following Macros for the Parameter Collector form

Macro Name = Open Dialog - Action = Open Form - Form Name = Parameter
Collector - View = Form - Data Mode = Edit - Window Mode = Dialog

Condition = Not IsLoaded("Parameter Collector") - Action = CancelEvent

Macro Name = Close Dialog - Action = Close - Object Type = Form - Object
Name = Parameter Collector - Save = No

Macro Name = OK - Action = SetValue - Item = [Visible] - Expression = No
Macro Name = Cancel - Action = Close - Object Type = Form - Object Name =
Parameter Collector - Save = No

This function was also added to a Module:

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

This is the only code in the actual Report:

Private Sub Report_Open(Cancel As Integer)
DoCmd.OpenForm "Parameter Collector", acNormal
End Sub







Private Sub Report_Open(Cancel As Integer)
DoCmd.OpenForm "Parameter Collector", acNormal
End Sub

Can you post the code where you are opening the form and report?
--
Dave Hargis, Microsoft Access MVP


:

Oh thank you for responding. I had posted regarding this before the
holidays. I am now getting back to this. After I posted this today, I was
able to make the form work. My parameter form text boxes are referenced in
the reports text boxes control source (example: =Forms.[Parameter
Collector].txtPreStartDate & " and "). This is working fine. My
paramenter form pops up before the report open and prompts for the dates,
dates get entered into the correct dates text boxes in the report, and I am
able to print the report fine. However, except for when there is two
instances of the report the same dates get entered into the two instances of
the report. Each instance of the report will have different dates for
different pts. Can this be fixed to have the parameter form pop up twice for
each report instance? I would appreciate any help you can provide as this
would accomplish what I need for this report. Thank you in advance for any
help you can provide.

:

It doesn't have to be dates. What fields in your query are you trying filter
on?
--
Dave Hargis, Microsoft Access MVP


:

I am trying to follow the instructions on the Access Microsoft Office page on
Creating a Form that collects Report Parameters from the following link
office.microsoft.com/en_us/access/HA100963141033.aspx?mode=print and I am
stuck on the following step. My understanding of this is that I needd to add
the following: Between [Forms]![Parameter Collector]![Beginning Date] And
[Forms]![Parameter Collector]![Ending Date] in the query of the report.

I do not have date fields in my query for this report. Do I have to have
them? I do not need to store these dates in my db. I just need to enter
these dates in the report to be able to print or send via fax. Can you
please help on how I can revise this? What do I need to do? Thank you.


Step 5: Enter the criteria in the underlying query or stored procedure for
the report
Open the underlying query or stored procedure for the report in Design view.
Enter the criteria for the data. In the expression (expression: Any
combination of mathematical or logical operators, constants, functions, and
names of fields, controls, and properties that evaluates to a single value.
Expressions can perform calculations, manipulate characters, or test data.),
use the Forms object, the name of the form, and the names of the controls
(control: A graphical user interface object, such as a text box, check box,
scroll bar, or command button, that lets users control the program. You use
controls to display data or choices, perform an action, or make the user
interface easier to read.) in the criteria:
For example, in an Access database (.accdb or .mdb), for a form named
Parameter Collector, you use the following expression to refer to controls
named Beginning Date and Ending Date in the query:
Between [Forms]![Parameter Collector]![Beginning Date] And
[Forms]![Parameter Collector]![Ending Date]

In an Access project (Microsoft Access project: An Access file that connects
to a Microsoft SQL Server database and is used to create client/server
applications. A project file doesn't contain any data or
data-definition-based objects such as tables and views.) (.adp), you must
first explicitly name the parameters in the stored procedure, for example:
@Beginning_Date datetime, @Ending_Date datetime

You then use those parameters in the WHERE clause, for example:

WHERE Sales.ShippedDate Between @Beginning_Date And

 

[Klatuu]

Sorry, missed that.

Handling multiple instances of a form takes some advanced VBA. Since you
are using macros, I don't know if you have the experience to do that. In
case you are interested, here is a link to info on how to do that. Read it
carefully, there are some gotchas there:

http://allenbrowne.com/ser-35.html

Otherwise, it may be easier to make a duplicate of your form and give it a
different name.

--
Dave Hargis, Microsoft Access MVP

Okay Thank you. I fixed the Form name. But you did not answer my question
about how to get the Parameter Form to prompt for each instance of the report
when there are more than one instance. Right now it only prompts once and if
there are more than one instances of the report it generates the same dates
to both reports. I need to somehow get the parameter form to synchronize
with the pt id or something like that? Is this possible? Can you please
help with this? Thank you in advance.

The first thing I noticed is you are using spaces in names. It is always a
bad idea, but in this case, you can bypass that by putting the name in
brackets:

Form Name = [Parameter Collector]

Where you have it in quotes, leave it as it is.
--
Dave Hargis, Microsoft Access MVP

Thank you for responding. There is not much code. I followed the
instructions from the Microsoft Office paper, "Use Parameters in queries and
Reports - Create a form that collects report parameters"
(http://office.microsoft.com/en-us/access/HA100963141033.aspx). According to
this, there is a macro group that controls the Parameter Collector form. The
macro consists of the following Macros for the Parameter Collector form

Macro Name = Open Dialog - Action = Open Form - Form Name = Parameter
Collector - View = Form - Data Mode = Edit - Window Mode = Dialog

Condition = Not IsLoaded("Parameter Collector") - Action = CancelEvent

Macro Name = Close Dialog - Action = Close - Object Type = Form - Object
Name = Parameter Collector - Save = No

Macro Name = OK - Action = SetValue - Item = [Visible] - Expression = No
Macro Name = Cancel - Action = Close - Object Type = Form - Object Name =
Parameter Collector - Save = No

This function was also added to a Module:

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

This is the only code in the actual Report:

Private Sub Report_Open(Cancel As Integer)
DoCmd.OpenForm "Parameter Collector", acNormal
End Sub







Private Sub Report_Open(Cancel As Integer)
DoCmd.OpenForm "Parameter Collector", acNormal
End Sub


:

Can you post the code where you are opening the form and report?
--
Dave Hargis, Microsoft Access MVP


:

Oh thank you for responding. I had posted regarding this before the
holidays. I am now getting back to this. After I posted this today, I was
able to make the form work. My parameter form text boxes are referenced in
the reports text boxes control source (example: =Forms.[Parameter
Collector].txtPreStartDate & " and "). This is working fine. My
paramenter form pops up before the report open and prompts for the dates,
dates get entered into the correct dates text boxes in the report, and I am
able to print the report fine. However, except for when there is two
instances of the report the same dates get entered into the two instances of
the report. Each instance of the report will have different dates for
different pts. Can this be fixed to have the parameter form pop up twice for
each report instance? I would appreciate any help you can provide as this
would accomplish what I need for this report. Thank you in advance for any
help you can provide.

:

It doesn't have to be dates. What fields in your query are you trying filter
on?
--
Dave Hargis, Microsoft Access MVP


:

I am trying to follow the instructions on the Access Microsoft Office page on
Creating a Form that collects Report Parameters from the following link
office.microsoft.com/en_us/access/HA100963141033.aspx?mode=print and I am
stuck on the following step. My understanding of this is that I needd to add
the following: Between [Forms]![Parameter Collector]![Beginning Date] And
[Forms]![Parameter Collector]![Ending Date] in the query of the report.

I do not have date fields in my query for this report. Do I have to have
them? I do not need to store these dates in my db. I just need to enter
these dates in the report to be able to print or send via fax. Can you
please help on how I can revise this? What do I need to do? Thank you.


Step 5: Enter the criteria in the underlying query or stored procedure for
the report
Open the underlying query or stored procedure for the report in Design view.
Enter the criteria for the data. In the expression (expression: Any
combination of mathematical or logical operators, constants, functions, and
names of fields, controls, and properties that evaluates to a single value.
Expressions can perform calculations, manipulate characters, or test data.),
use the Forms object, the name of the form, and the names of the controls
(control: A graphical user interface object, such as a text box, check box,
scroll bar, or command button, that lets users control the program. You use
controls to display data or choices, perform an action, or make the user
interface easier to read.) in the criteria:
For example, in an Access database (.accdb or .mdb), for a form named
Parameter Collector, you use the following expression to refer to controls
named Beginning Date and Ending Date in the query:
Between [Forms]![Parameter Collector]![Beginning Date] And
[Forms]![Parameter Collector]![Ending Date]

In an Access project (Microsoft Access project: An Access file that connects
to a Microsoft SQL Server database and is used to create client/server
applications. A project file doesn't contain any data or
data-definition-based objects such as tables and views.) (.adp), you must
first explicitly name the parameters in the stored procedure, for example:
@Beginning_Date datetime, @Ending_Date datetime

You then use those parameters in the WHERE clause, for example:

WHERE Sales.ShippedDate Between @Beginning_Date And

 

[TotallyConfused]

Thank you. What do you mean give my form a different name (duplicate)? How
would that work?

Sorry, missed that.

Handling multiple instances of a form takes some advanced VBA. Since you
are using macros, I don't know if you have the experience to do that. In
case you are interested, here is a link to info on how to do that. Read it
carefully, there are some gotchas there:

http://allenbrowne.com/ser-35.html

Otherwise, it may be easier to make a duplicate of your form and give it a
different name.

--
Dave Hargis, Microsoft Access MVP

Okay Thank you. I fixed the Form name. But you did not answer my question
about how to get the Parameter Form to prompt for each instance of the report
when there are more than one instance. Right now it only prompts once and if
there are more than one instances of the report it generates the same dates
to both reports. I need to somehow get the parameter form to synchronize
with the pt id or something like that? Is this possible? Can you please
help with this? Thank you in advance.

The first thing I noticed is you are using spaces in names. It is always a
bad idea, but in this case, you can bypass that by putting the name in
brackets:

Form Name = [Parameter Collector]

Where you have it in quotes, leave it as it is.
--
Dave Hargis, Microsoft Access MVP


:

Thank you for responding. There is not much code. I followed the
instructions from the Microsoft Office paper, "Use Parameters in queries and
Reports - Create a form that collects report parameters"
(http://office.microsoft.com/en-us/access/HA100963141033.aspx). According to
this, there is a macro group that controls the Parameter Collector form. The
macro consists of the following Macros for the Parameter Collector form

Macro Name = Open Dialog - Action = Open Form - Form Name = Parameter
Collector - View = Form - Data Mode = Edit - Window Mode = Dialog

Condition = Not IsLoaded("Parameter Collector") - Action = CancelEvent

Macro Name = Close Dialog - Action = Close - Object Type = Form - Object
Name = Parameter Collector - Save = No

Macro Name = OK - Action = SetValue - Item = [Visible] - Expression = No
Macro Name = Cancel - Action = Close - Object Type = Form - Object Name =
Parameter Collector - Save = No

This function was also added to a Module:

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

This is the only code in the actual Report:

Private Sub Report_Open(Cancel As Integer)
DoCmd.OpenForm "Parameter Collector", acNormal
End Sub







Private Sub Report_Open(Cancel As Integer)
DoCmd.OpenForm "Parameter Collector", acNormal
End Sub


:

Can you post the code where you are opening the form and report?
--
Dave Hargis, Microsoft Access MVP


:

Oh thank you for responding. I had posted regarding this before the
holidays. I am now getting back to this. After I posted this today, I was
able to make the form work. My parameter form text boxes are referenced in
the reports text boxes control source (example: =Forms.[Parameter
Collector].txtPreStartDate & " and "). This is working fine. My
paramenter form pops up before the report open and prompts for the dates,
dates get entered into the correct dates text boxes in the report, and I am
able to print the report fine. However, except for when there is two
instances of the report the same dates get entered into the two instances of
the report. Each instance of the report will have different dates for
different pts. Can this be fixed to have the parameter form pop up twice for
each report instance? I would appreciate any help you can provide as this
would accomplish what I need for this report. Thank you in advance for any
help you can provide.

:

It doesn't have to be dates. What fields in your query are you trying filter
on?
--
Dave Hargis, Microsoft Access MVP


:

I am trying to follow the instructions on the Access Microsoft Office page on
Creating a Form that collects Report Parameters from the following link
office.microsoft.com/en_us/access/HA100963141033.aspx?mode=print and I am
stuck on the following step. My understanding of this is that I needd to add
the following: Between [Forms]![Parameter Collector]![Beginning Date] And
[Forms]![Parameter Collector]![Ending Date] in the query of the report.

I do not have date fields in my query for this report. Do I have to have
them? I do not need to store these dates in my db. I just need to enter
these dates in the report to be able to print or send via fax. Can you
please help on how I can revise this? What do I need to do? Thank you.


Step 5: Enter the criteria in the underlying query or stored procedure for
the report
Open the underlying query or stored procedure for the report in Design view.
Enter the criteria for the data. In the expression (expression: Any
combination of mathematical or logical operators, constants, functions, and
names of fields, controls, and properties that evaluates to a single value.
Expressions can perform calculations, manipulate characters, or test data.),
use the Forms object, the name of the form, and the names of the controls
(control: A graphical user interface object, such as a text box, check box,
scroll bar, or command button, that lets users control the program. You use
controls to display data or choices, perform an action, or make the user
interface easier to read.) in the criteria:
For example, in an Access database (.accdb or .mdb), for a form named
Parameter Collector, you use the following expression to refer to controls
named Beginning Date and Ending Date in the query:
Between [Forms]![Parameter Collector]![Beginning Date] And
[Forms]![Parameter Collector]![Ending Date]

In an Access project (Microsoft Access project: An Access file that connects
to a Microsoft SQL Server database and is used to create client/server
applications. A project file doesn't contain any data or
data-definition-based objects such as tables and views.) (.adp), you must
first explicitly name the parameters in the stored procedure, for example:
@Beginning_Date datetime, @Ending_Date datetime

You then use those parameters in the WHERE clause, for example:

WHERE Sales.ShippedDate Between @Beginning_Date And

 

[Klatuu]

If your form is named frmFormBoo
make a copy and name it frmFormBoo1

This will mean you will need two macros or conditions in the macro to
determine which form to run.
--
Dave Hargis, Microsoft Access MVP

Thank you. What do you mean give my form a different name (duplicate)? How
would that work?

Sorry, missed that.

Handling multiple instances of a form takes some advanced VBA. Since you
are using macros, I don't know if you have the experience to do that. In
case you are interested, here is a link to info on how to do that. Read it
carefully, there are some gotchas there:

http://allenbrowne.com/ser-35.html

Otherwise, it may be easier to make a duplicate of your form and give it a
different name.

--
Dave Hargis, Microsoft Access MVP

Okay Thank you. I fixed the Form name. But you did not answer my question
about how to get the Parameter Form to prompt for each instance of the report
when there are more than one instance. Right now it only prompts once and if
there are more than one instances of the report it generates the same dates
to both reports. I need to somehow get the parameter form to synchronize
with the pt id or something like that? Is this possible? Can you please
help with this? Thank you in advance.

:

The first thing I noticed is you are using spaces in names. It is always a
bad idea, but in this case, you can bypass that by putting the name in
brackets:

Form Name = [Parameter Collector]

Where you have it in quotes, leave it as it is.
--
Dave Hargis, Microsoft Access MVP


:

Thank you for responding. There is not much code. I followed the
instructions from the Microsoft Office paper, "Use Parameters in queries and
Reports - Create a form that collects report parameters"
(http://office.microsoft.com/en-us/access/HA100963141033.aspx). According to
this, there is a macro group that controls the Parameter Collector form. The
macro consists of the following Macros for the Parameter Collector form

Macro Name = Open Dialog - Action = Open Form - Form Name = Parameter
Collector - View = Form - Data Mode = Edit - Window Mode = Dialog

Condition = Not IsLoaded("Parameter Collector") - Action = CancelEvent

Macro Name = Close Dialog - Action = Close - Object Type = Form - Object
Name = Parameter Collector - Save = No

Macro Name = OK - Action = SetValue - Item = [Visible] - Expression = No
Macro Name = Cancel - Action = Close - Object Type = Form - Object Name =
Parameter Collector - Save = No

This function was also added to a Module:

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

This is the only code in the actual Report:

Private Sub Report_Open(Cancel As Integer)
DoCmd.OpenForm "Parameter Collector", acNormal
End Sub







Private Sub Report_Open(Cancel As Integer)
DoCmd.OpenForm "Parameter Collector", acNormal
End Sub


:

Can you post the code where you are opening the form and report?
--
Dave Hargis, Microsoft Access MVP


:

Oh thank you for responding. I had posted regarding this before the
holidays. I am now getting back to this. After I posted this today, I was
able to make the form work. My parameter form text boxes are referenced in
the reports text boxes control source (example: =Forms.[Parameter
Collector].txtPreStartDate & " and "). This is working fine. My
paramenter form pops up before the report open and prompts for the dates,
dates get entered into the correct dates text boxes in the report, and I am
able to print the report fine. However, except for when there is two
instances of the report the same dates get entered into the two instances of
the report. Each instance of the report will have different dates for
different pts. Can this be fixed to have the parameter form pop up twice for
each report instance? I would appreciate any help you can provide as this
would accomplish what I need for this report. Thank you in advance for any
help you can provide.

:

It doesn't have to be dates. What fields in your query are you trying filter
on?
--
Dave Hargis, Microsoft Access MVP


:

I am trying to follow the instructions on the Access Microsoft Office page on
Creating a Form that collects Report Parameters from the following link
office.microsoft.com/en_us/access/HA100963141033.aspx?mode=print and I am
stuck on the following step. My understanding of this is that I needd to add
the following: Between [Forms]![Parameter Collector]![Beginning Date] And
[Forms]![Parameter Collector]![Ending Date] in the query of the report.

I do not have date fields in my query for this report. Do I have to have
them? I do not need to store these dates in my db. I just need to enter
these dates in the report to be able to print or send via fax. Can you
please help on how I can revise this? What do I need to do? Thank you.


Step 5: Enter the criteria in the underlying query or stored procedure for
the report
Open the underlying query or stored procedure for the report in Design view.
Enter the criteria for the data. In the expression (expression: Any
combination of mathematical or logical operators, constants, functions, and
names of fields, controls, and properties that evaluates to a single value.
Expressions can perform calculations, manipulate characters, or test data.),
use the Forms object, the name of the form, and the names of the controls
(control: A graphical user interface object, such as a text box, check box,
scroll bar, or command button, that lets users control the program. You use
controls to display data or choices, perform an action, or make the user
interface easier to read.) in the criteria:
For example, in an Access database (.accdb or .mdb), for a form named
Parameter Collector, you use the following expression to refer to controls
named Beginning Date and Ending Date in the query:
Between [Forms]![Parameter Collector]![Beginning Date] And
[Forms]![Parameter Collector]![Ending Date]

In an Access project (Microsoft Access project: An Access file that connects
to a Microsoft SQL Server database and is used to create client/server
applications. A project file doesn't contain any data or
data-definition-based objects such as tables and views.) (.adp), you must
first explicitly name the parameters in the stored procedure, for example:
@Beginning_Date datetime, @Ending_Date datetime

You then use those parameters in the WHERE clause, for example:

WHERE Sales.ShippedDate Between @Beginning_Date And

 

[TotallyConfused]

Sorry, if I sound so naive, but I desperately need this to work by the end of
the week. Trying to decide which way to go macro or tackle VBA. By making a
copy of the form, would that work if there are more than 2 instances of the
report?

If your form is named frmFormBoo
make a copy and name it frmFormBoo1

This will mean you will need two macros or conditions in the macro to
determine which form to run.
--
Dave Hargis, Microsoft Access MVP

Thank you. What do you mean give my form a different name (duplicate)? How
would that work?

Sorry, missed that.

Handling multiple instances of a form takes some advanced VBA. Since you
are using macros, I don't know if you have the experience to do that. In
case you are interested, here is a link to info on how to do that. Read it
carefully, there are some gotchas there:

http://allenbrowne.com/ser-35.html

Otherwise, it may be easier to make a duplicate of your form and give it a
different name.

--
Dave Hargis, Microsoft Access MVP


:

Okay Thank you. I fixed the Form name. But you did not answer my question
about how to get the Parameter Form to prompt for each instance of the report
when there are more than one instance. Right now it only prompts once and if
there are more than one instances of the report it generates the same dates
to both reports. I need to somehow get the parameter form to synchronize
with the pt id or something like that? Is this possible? Can you please
help with this? Thank you in advance.

:

The first thing I noticed is you are using spaces in names. It is always a
bad idea, but in this case, you can bypass that by putting the name in
brackets:

Form Name = [Parameter Collector]

Where you have it in quotes, leave it as it is.
--
Dave Hargis, Microsoft Access MVP


:

Thank you for responding. There is not much code. I followed the
instructions from the Microsoft Office paper, "Use Parameters in queries and
Reports - Create a form that collects report parameters"
(http://office.microsoft.com/en-us/access/HA100963141033.aspx). According to
this, there is a macro group that controls the Parameter Collector form. The
macro consists of the following Macros for the Parameter Collector form

Macro Name = Open Dialog - Action = Open Form - Form Name = Parameter
Collector - View = Form - Data Mode = Edit - Window Mode = Dialog

Condition = Not IsLoaded("Parameter Collector") - Action = CancelEvent

Macro Name = Close Dialog - Action = Close - Object Type = Form - Object
Name = Parameter Collector - Save = No

Macro Name = OK - Action = SetValue - Item = [Visible] - Expression = No
Macro Name = Cancel - Action = Close - Object Type = Form - Object Name =
Parameter Collector - Save = No

This function was also added to a Module:

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

This is the only code in the actual Report:

Private Sub Report_Open(Cancel As Integer)
DoCmd.OpenForm "Parameter Collector", acNormal
End Sub







Private Sub Report_Open(Cancel As Integer)
DoCmd.OpenForm "Parameter Collector", acNormal
End Sub


:

Can you post the code where you are opening the form and report?
--
Dave Hargis, Microsoft Access MVP


:

Oh thank you for responding. I had posted regarding this before the
holidays. I am now getting back to this. After I posted this today, I was
able to make the form work. My parameter form text boxes are referenced in
the reports text boxes control source (example: =Forms.[Parameter
Collector].txtPreStartDate & " and "). This is working fine. My
paramenter form pops up before the report open and prompts for the dates,
dates get entered into the correct dates text boxes in the report, and I am
able to print the report fine. However, except for when there is two
instances of the report the same dates get entered into the two instances of
the report. Each instance of the report will have different dates for
different pts. Can this be fixed to have the parameter form pop up twice for
each report instance? I would appreciate any help you can provide as this
would accomplish what I need for this report. Thank you in advance for any
help you can provide.

:

It doesn't have to be dates. What fields in your query are you trying filter
on?
--
Dave Hargis, Microsoft Access MVP


:

I am trying to follow the instructions on the Access Microsoft Office page on
Creating a Form that collects Report Parameters from the following link
office.microsoft.com/en_us/access/HA100963141033.aspx?mode=print and I am
stuck on the following step. My understanding of this is that I needd to add
the following: Between [Forms]![Parameter Collector]![Beginning Date] And
[Forms]![Parameter Collector]![Ending Date] in the query of the report.

I do not have date fields in my query for this report. Do I have to have
them? I do not need to store these dates in my db. I just need to enter
these dates in the report to be able to print or send via fax. Can you
please help on how I can revise this? What do I need to do? Thank you.


Step 5: Enter the criteria in the underlying query or stored procedure for
the report
Open the underlying query or stored procedure for the report in Design view.
Enter the criteria for the data. In the expression (expression: Any
combination of mathematical or logical operators, constants, functions, and
names of fields, controls, and properties that evaluates to a single value.
Expressions can perform calculations, manipulate characters, or test data.),
use the Forms object, the name of the form, and the names of the controls
(control: A graphical user interface object, such as a text box, check box,
scroll bar, or command button, that lets users control the program. You use
controls to display data or choices, perform an action, or make the user
interface easier to read.) in the criteria:
For example, in an Access database (.accdb or .mdb), for a form named
Parameter Collector, you use the following expression to refer to controls
named Beginning Date and Ending Date in the query:
Between [Forms]![Parameter Collector]![Beginning Date] And
[Forms]![Parameter Collector]![Ending Date]

In an Access project (Microsoft Access project: An Access file that connects
to a Microsoft SQL Server database and is used to create client/server
applications. A project file doesn't contain any data or
data-definition-based objects such as tables and views.) (.adp), you must
first explicitly name the parameters in the stored procedure, for example:
@Beginning_Date datetime, @Ending_Date datetime

You then use those parameters in the WHERE clause, for example:

WHERE Sales.ShippedDate Between @Beginning_Date And

 

[Klatuu]

It would only work for two instances. If you need more, you will have to go
the VBA route. It puzzles me that more than one instance of a report would
be run at one time.
--
Dave Hargis, Microsoft Access MVP

Sorry, if I sound so naive, but I desperately need this to work by the end of
the week. Trying to decide which way to go macro or tackle VBA. By making a
copy of the form, would that work if there are more than 2 instances of the
report?

If your form is named frmFormBoo
make a copy and name it frmFormBoo1

This will mean you will need two macros or conditions in the macro to
determine which form to run.
--
Dave Hargis, Microsoft Access MVP

Thank you. What do you mean give my form a different name (duplicate)? How
would that work?

:

Sorry, missed that.

Handling multiple instances of a form takes some advanced VBA. Since you
are using macros, I don't know if you have the experience to do that. In
case you are interested, here is a link to info on how to do that. Read it
carefully, there are some gotchas there:

http://allenbrowne.com/ser-35.html

Otherwise, it may be easier to make a duplicate of your form and give it a
different name.

--
Dave Hargis, Microsoft Access MVP


:

Okay Thank you. I fixed the Form name. But you did not answer my question
about how to get the Parameter Form to prompt for each instance of the report
when there are more than one instance. Right now it only prompts once and if
there are more than one instances of the report it generates the same dates
to both reports. I need to somehow get the parameter form to synchronize
with the pt id or something like that? Is this possible? Can you please
help with this? Thank you in advance.

:

The first thing I noticed is you are using spaces in names. It is always a
bad idea, but in this case, you can bypass that by putting the name in
brackets:

Form Name = [Parameter Collector]

Where you have it in quotes, leave it as it is.
--
Dave Hargis, Microsoft Access MVP


:

Thank you for responding. There is not much code. I followed the
instructions from the Microsoft Office paper, "Use Parameters in queries and
Reports - Create a form that collects report parameters"
(http://office.microsoft.com/en-us/access/HA100963141033.aspx). According to
this, there is a macro group that controls the Parameter Collector form. The
macro consists of the following Macros for the Parameter Collector form

Macro Name = Open Dialog - Action = Open Form - Form Name = Parameter
Collector - View = Form - Data Mode = Edit - Window Mode = Dialog

Condition = Not IsLoaded("Parameter Collector") - Action = CancelEvent

Macro Name = Close Dialog - Action = Close - Object Type = Form - Object
Name = Parameter Collector - Save = No

Macro Name = OK - Action = SetValue - Item = [Visible] - Expression = No
Macro Name = Cancel - Action = Close - Object Type = Form - Object Name =
Parameter Collector - Save = No

This function was also added to a Module:

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

This is the only code in the actual Report:

Private Sub Report_Open(Cancel As Integer)
DoCmd.OpenForm "Parameter Collector", acNormal
End Sub







Private Sub Report_Open(Cancel As Integer)
DoCmd.OpenForm "Parameter Collector", acNormal
End Sub


:

Can you post the code where you are opening the form and report?
--
Dave Hargis, Microsoft Access MVP


:

Oh thank you for responding. I had posted regarding this before the
holidays. I am now getting back to this. After I posted this today, I was
able to make the form work. My parameter form text boxes are referenced in
the reports text boxes control source (example: =Forms.[Parameter
Collector].txtPreStartDate & " and "). This is working fine. My
paramenter form pops up before the report open and prompts for the dates,
dates get entered into the correct dates text boxes in the report, and I am
able to print the report fine. However, except for when there is two
instances of the report the same dates get entered into the two instances of
the report. Each instance of the report will have different dates for
different pts. Can this be fixed to have the parameter form pop up twice for
each report instance? I would appreciate any help you can provide as this
would accomplish what I need for this report. Thank you in advance for any
help you can provide.

:

It doesn't have to be dates. What fields in your query are you trying filter
on?
--
Dave Hargis, Microsoft Access MVP


:

I am trying to follow the instructions on the Access Microsoft Office page on
Creating a Form that collects Report Parameters from the following link
office.microsoft.com/en_us/access/HA100963141033.aspx?mode=print and I am
stuck on the following step. My understanding of this is that I needd to add
the following: Between [Forms]![Parameter Collector]![Beginning Date] And
[Forms]![Parameter Collector]![Ending Date] in the query of the report.

I do not have date fields in my query for this report. Do I have to have
them? I do not need to store these dates in my db. I just need to enter
these dates in the report to be able to print or send via fax. Can you
please help on how I can revise this? What do I need to do? Thank you.


Step 5: Enter the criteria in the underlying query or stored procedure for
the report
Open the underlying query or stored procedure for the report in Design view.
Enter the criteria for the data. In the expression (expression: Any
combination of mathematical or logical operators, constants, functions, and
names of fields, controls, and properties that evaluates to a single value.
Expressions can perform calculations, manipulate characters, or test data.),
use the Forms object, the name of the form, and the names of the controls
(control: A graphical user interface object, such as a text box, check box,
scroll bar, or command button, that lets users control the program. You use
controls to display data or choices, perform an action, or make the user
interface easier to read.) in the criteria:
For example, in an Access database (.accdb or .mdb), for a form named
Parameter Collector, you use the following expression to refer to controls
named Beginning Date and Ending Date in the query:
Between [Forms]![Parameter Collector]![Beginning Date] And
[Forms]![Parameter Collector]![Ending Date]

In an Access project (Microsoft Access project: An Access file that connects
to a Microsoft SQL Server database and is used to create client/server
applications. A project file doesn't contain any data or
data-definition-based objects such as tables and views.) (.adp), you must
first explicitly name the parameters in the stored procedure, for example:
@Beginning_Date datetime, @Ending_Date datetime

You then use those parameters in the WHERE clause, for example:

WHERE Sales.ShippedDate Between @Beginning_Date And