Report parameters

S

Saylindara

I am tentatively dipping my toe in the water. I would like to add parameters
to several of my reports and have successfully followed the directions posted
by fredg on 1/8/2009 on a simple test report using one parameter.
(Ultimately my reports will have 2 or more parameters, when I daresay I will
get my knickers in a knot.) I would also like users to have the option to
view the whole report (i.e. no parameters) as well if they want to. Is that
possible?
 
A

Al Campagna

Saylindara,
Yes, it is possible.
But, there are many ways to handle parameters. I'm not sure
what you've done with Fred in the past, but...
If you have a specific question about a specific parameter,
that would be best to post. But here's a sample...

Say you had a parameter, in your query, that asked for a Company Name.
And you had the following Companies in the table.
Smith
Smith & Sons
Smithers Products

In the CompanyName column you had the following criteria...
= Like [Enter Company Name]
if you enter "Smith" at the parameter prompt... only Smith will be returned
(Exact match)

In the CompanyName column you had the following criteria...
(using a global * variable)
= Like [Enter Company Name] & "*"
if you enter "Smi" at the parameter prompt... Smith and Smith & Sons would
be will be returned.
(begins with... and after that I don't care)

In the CompanyName column you had the following criteria...
= Like "*" & [Enter Company Name] & "*"
if you enter "mit" at the parameter prompt... Smith, Smith & Sons, and
Smithers Products will be returned.
(Has this string anywhere within the Company Name)
***But*** if prompt is null (left blank), will return all Company Names.
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."
 
S

Saylindara

I've been having trouble posting a reply and getting 'Service temporarily
unavailable' messages. I don't know if anyone else has had the same problem.

I should have explained the problem better.

I created an unbound form: Paramform. Then I added a combobox: cboFindName.
The combobox showed a list of employee names with the bound field EmployeeID.
Then I added a command button and coded the ClickEvent: Me.Visible=False.

In the EmployeeID field of the query I wrote: forms!ParamForm!cboFindName.

I coded the report’s OpenEvent: DoCmd.OpenForm “ParamFormâ€,,,,,acDialog and
the CloseEvent: DoCmd.Close acForm, “ParamFormâ€.

I would also like to have an option on the unbound form to print all the
records.


Al Campagna said:
Saylindara,
Yes, it is possible.
But, there are many ways to handle parameters. I'm not sure
what you've done with Fred in the past, but...
If you have a specific question about a specific parameter,
that would be best to post. But here's a sample...

Say you had a parameter, in your query, that asked for a Company Name.
And you had the following Companies in the table.
Smith
Smith & Sons
Smithers Products

In the CompanyName column you had the following criteria...
= Like [Enter Company Name]
if you enter "Smith" at the parameter prompt... only Smith will be returned
(Exact match)

In the CompanyName column you had the following criteria...
(using a global * variable)
= Like [Enter Company Name] & "*"
if you enter "Smi" at the parameter prompt... Smith and Smith & Sons would
be will be returned.
(begins with... and after that I don't care)

In the CompanyName column you had the following criteria...
= Like "*" & [Enter Company Name] & "*"
if you enter "mit" at the parameter prompt... Smith, Smith & Sons, and
Smithers Products will be returned.
(Has this string anywhere within the Company Name)
***But*** if prompt is null (left blank), will return all Company Names.
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."



Saylindara said:
I am tentatively dipping my toe in the water. I would like to add
parameters
to several of my reports and have successfully followed the directions
posted
by fredg on 1/8/2009 on a simple test report using one parameter.
(Ultimately my reports will have 2 or more parameters, when I daresay I
will
get my knickers in a knot.) I would also like users to have the option to
view the whole report (i.e. no parameters) as well if they want to. Is
that
possible?
 
A

Al Campagna

Saylindara,
You should have the ParamForm open, and an EmpID (or none) selected
BEFORE you run the report. The report should be run from a
ParamForm button, and the form should remain open while the report is run.

Instead of using a query parameter, use the Where condition of the
OpenReport method to report on just 1 Employee or All... depending
on the value in cboEmpID.

Private Sub RunReportButton_Click()
If IsNull(cboEmpID) Then
DoCmd.OpenReport "rptReport", acViewPreview
Else
DoCmd.OpenReport "rptReport", acViewPreview,, "EmpID = " & Me.EmpID)
End If
End Sub

To be sure that cboEmpID actually contains the correct "bound" value,
add a text
control to the form with
= cboEmpID
calculation in it's ControlSource. That will show you what the real vs
displayed
cboEmpID value is.
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."


Saylindara said:
I've been having trouble posting a reply and getting 'Service temporarily
unavailable' messages. I don't know if anyone else has had the same
problem.

I should have explained the problem better.

I created an unbound form: Paramform. Then I added a combobox:
cboFindName.
The combobox showed a list of employee names with the bound field
EmployeeID.
Then I added a command button and coded the ClickEvent: Me.Visible=False.

In the EmployeeID field of the query I wrote: forms!ParamForm!cboFindName.

I coded the report's OpenEvent: DoCmd.OpenForm "ParamForm",,,,,acDialog
and
the CloseEvent: DoCmd.Close acForm, "ParamForm".

I would also like to have an option on the unbound form to print all the
records.


Al Campagna said:
Saylindara,
Yes, it is possible.
But, there are many ways to handle parameters. I'm not sure
what you've done with Fred in the past, but...
If you have a specific question about a specific parameter,
that would be best to post. But here's a sample...

Say you had a parameter, in your query, that asked for a Company
Name.
And you had the following Companies in the table.
Smith
Smith & Sons
Smithers Products

In the CompanyName column you had the following criteria...
= Like [Enter Company Name]
if you enter "Smith" at the parameter prompt... only Smith will be
returned
(Exact match)

In the CompanyName column you had the following criteria...
(using a global * variable)
= Like [Enter Company Name] & "*"
if you enter "Smi" at the parameter prompt... Smith and Smith & Sons
would
be will be returned.
(begins with... and after that I don't care)

In the CompanyName column you had the following criteria...
= Like "*" & [Enter Company Name] & "*"
if you enter "mit" at the parameter prompt... Smith, Smith & Sons, and
Smithers Products will be returned.
(Has this string anywhere within the Company Name)
***But*** if prompt is null (left blank), will return all Company Names.
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your
life."



Saylindara said:
I am tentatively dipping my toe in the water. I would like to add
parameters
to several of my reports and have successfully followed the directions
posted
by fredg on 1/8/2009 on a simple test report using one parameter.
(Ultimately my reports will have 2 or more parameters, when I daresay I
will
get my knickers in a knot.) I would also like users to have the option
to
view the whole report (i.e. no parameters) as well if they want to. Is
that
possible?
 
S

Saylindara

I'm having a lot of trouble posting replies. I don't know if there is
something wrong with my PC or it's a more general problem.

I'm sorry to be so dense but I'm not sure where I put

Private Sub RunReportButton_Click()
If IsNull(cboEmpID) Then
DoCmd.OpenReport "rptReport", acViewPreview
Else
DoCmd.OpenReport "rptReport", acViewPreview,, "EmpID = " & Me.EmpID)
End If
End Sub

or what I have to delete.

Here's hoping this post goes through.

To be sure that cboEmpID actually contains the correct "bound" value,
add a text
control to the form with
= cboEmpID
calculation in it's ControlSource. That will show you what the real vs
displayed
cboEmpID value is.
--
hth
Al Campagna
Microsoft Access MVP


Al Campagna said:
Saylindara,
You should have the ParamForm open, and an EmpID (or none) selected
BEFORE you run the report. The report should be run from a
ParamForm button, and the form should remain open while the report is run.

Instead of using a query parameter, use the Where condition of the
OpenReport method to report on just 1 Employee or All... depending
on the value in cboEmpID.

Private Sub RunReportButton_Click()
If IsNull(cboEmpID) Then
DoCmd.OpenReport "rptReport", acViewPreview
Else
DoCmd.OpenReport "rptReport", acViewPreview,, "EmpID = " & Me.EmpID)
End If
End Sub

To be sure that cboEmpID actually contains the correct "bound" value,
add a text
control to the form with
= cboEmpID
calculation in it's ControlSource. That will show you what the real vs
displayed
cboEmpID value is.
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."


Saylindara said:
I've been having trouble posting a reply and getting 'Service temporarily
unavailable' messages. I don't know if anyone else has had the same
problem.

I should have explained the problem better.

I created an unbound form: Paramform. Then I added a combobox:
cboFindName.
The combobox showed a list of employee names with the bound field
EmployeeID.
Then I added a command button and coded the ClickEvent: Me.Visible=False.

In the EmployeeID field of the query I wrote: forms!ParamForm!cboFindName.

I coded the report's OpenEvent: DoCmd.OpenForm "ParamForm",,,,,acDialog
and
the CloseEvent: DoCmd.Close acForm, "ParamForm".

I would also like to have an option on the unbound form to print all the
records.


Al Campagna said:
Saylindara,
Yes, it is possible.
But, there are many ways to handle parameters. I'm not sure
what you've done with Fred in the past, but...
If you have a specific question about a specific parameter,
that would be best to post. But here's a sample...

Say you had a parameter, in your query, that asked for a Company
Name.
And you had the following Companies in the table.
Smith
Smith & Sons
Smithers Products

In the CompanyName column you had the following criteria...
= Like [Enter Company Name]
if you enter "Smith" at the parameter prompt... only Smith will be
returned
(Exact match)

In the CompanyName column you had the following criteria...
(using a global * variable)
= Like [Enter Company Name] & "*"
if you enter "Smi" at the parameter prompt... Smith and Smith & Sons
would
be will be returned.
(begins with... and after that I don't care)

In the CompanyName column you had the following criteria...
= Like "*" & [Enter Company Name] & "*"
if you enter "mit" at the parameter prompt... Smith, Smith & Sons, and
Smithers Products will be returned.
(Has this string anywhere within the Company Name)
***But*** if prompt is null (left blank), will return all Company Names.
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your
life."



I am tentatively dipping my toe in the water. I would like to add
parameters
to several of my reports and have successfully followed the directions
posted
by fredg on 1/8/2009 on a simple test report using one parameter.
(Ultimately my reports will have 2 or more parameters, when I daresay I
will
get my knickers in a knot.) I would also like users to have the option
to
view the whole report (i.e. no parameters) as well if they want to. Is
that
possible?
 
A

Al Campagna

Saylindara,
I'm using my own object names... please use your own real names in
any code you use.

In form design mode, select your RunReportButton's OnClick property.
Put your cursor in the text field next to the OnClick event.
Usng the arrow on the right, select [Event Procedure]
Click on the 3 dots to the right (...)
You'll now be in the form's module where you'll see...

Private Sub RunReport_OnClick()

End Sub

Put the code between the lines...

Private Sub RunReport_OnClick()
If IsNull(cboEmpID) Then
DoCmd.OpenReport "rptReport", acViewPreview
Else
DoCmd.OpenReport "rptReport", acViewPreview,, "EmpID = " & Me.EmpID)
End If
End Sub

Test by setting a cboEmpID value, or deleting any cboEmpID value...
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."




Saylindara said:
I'm having a lot of trouble posting replies. I don't know if there is
something wrong with my PC or it's a more general problem.

I'm sorry to be so dense but I'm not sure where I put

Private Sub RunReportButton_Click()
If IsNull(cboEmpID) Then
DoCmd.OpenReport "rptReport", acViewPreview
Else
DoCmd.OpenReport "rptReport", acViewPreview,, "EmpID = " & Me.EmpID)
End If
End Sub

or what I have to delete.

Here's hoping this post goes through.

To be sure that cboEmpID actually contains the correct "bound" value,
add a text
control to the form with
= cboEmpID
calculation in it's ControlSource. That will show you what the real vs
displayed
cboEmpID value is.
--
hth
Al Campagna
Microsoft Access MVP


Al Campagna said:
Saylindara,
You should have the ParamForm open, and an EmpID (or none) selected
BEFORE you run the report. The report should be run from a
ParamForm button, and the form should remain open while the report is
run.

Instead of using a query parameter, use the Where condition of the
OpenReport method to report on just 1 Employee or All... depending
on the value in cboEmpID.

Private Sub RunReportButton_Click()
If IsNull(cboEmpID) Then
DoCmd.OpenReport "rptReport", acViewPreview
Else
DoCmd.OpenReport "rptReport", acViewPreview,, "EmpID = " &
Me.EmpID)
End If
End Sub

To be sure that cboEmpID actually contains the correct "bound" value,
add a text
control to the form with
= cboEmpID
calculation in it's ControlSource. That will show you what the real vs
displayed
cboEmpID value is.
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your
life."


Saylindara said:
I've been having trouble posting a reply and getting 'Service
temporarily
unavailable' messages. I don't know if anyone else has had the same
problem.

I should have explained the problem better.

I created an unbound form: Paramform. Then I added a combobox:
cboFindName.
The combobox showed a list of employee names with the bound field
EmployeeID.
Then I added a command button and coded the ClickEvent:
Me.Visible=False.

In the EmployeeID field of the query I wrote:
forms!ParamForm!cboFindName.

I coded the report's OpenEvent: DoCmd.OpenForm "ParamForm",,,,,acDialog
and
the CloseEvent: DoCmd.Close acForm, "ParamForm".

I would also like to have an option on the unbound form to print all
the
records.


:

Saylindara,
Yes, it is possible.
But, there are many ways to handle parameters. I'm not sure
what you've done with Fred in the past, but...
If you have a specific question about a specific parameter,
that would be best to post. But here's a sample...

Say you had a parameter, in your query, that asked for a Company
Name.
And you had the following Companies in the table.
Smith
Smith & Sons
Smithers Products

In the CompanyName column you had the following criteria...
= Like [Enter Company Name]
if you enter "Smith" at the parameter prompt... only Smith will be
returned
(Exact match)

In the CompanyName column you had the following criteria...
(using a global * variable)
= Like [Enter Company Name] & "*"
if you enter "Smi" at the parameter prompt... Smith and Smith & Sons
would
be will be returned.
(begins with... and after that I don't care)

In the CompanyName column you had the following criteria...
= Like "*" & [Enter Company Name] & "*"
if you enter "mit" at the parameter prompt... Smith, Smith & Sons, and
Smithers Products will be returned.
(Has this string anywhere within the Company Name)
***But*** if prompt is null (left blank), will return all Company
Names.
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your
life."



I am tentatively dipping my toe in the water. I would like to add
parameters
to several of my reports and have successfully followed the
directions
posted
by fredg on 1/8/2009 on a simple test report using one parameter.
(Ultimately my reports will have 2 or more parameters, when I
daresay I
will
get my knickers in a knot.) I would also like users to have the
option
to
view the whole report (i.e. no parameters) as well if they want to.
Is
that
possible?
 
S

Saylindara

I added this to the command button OnClick event:

Private Sub Command2_Click()
If IsNull(cboFindName) Then
DoCmd.OpenReport "r_EventsAttendedByEmployee", acViewPreview
Else
DoCmd.OpenReport "r_EventsAttendedByEmployee", , EmployeeID = "&
Me.EmployeeID)
End If
End Sub

This opened the form when the combobox was blank but when I tried it with
employee names the form opens but is blank. I had already checked as you
recommended that I had the correct bound column (such a good idea as it
showed I hadn't and I was able to correct it). I then thought I had a
brainwave (I should have known better) that it was because I hadn't got
employeeID on the form, so I added that in the details section but it didn't
make any difference. Should I be putting something in the query? I tried what
I had originally 'forms!PramForm!cboFindName' but a form popped up asking me
for parameters.

I know you must be getting fed up with me now but any further help would be
greatly appreciated.

Al Campagna said:
Saylindara,
I'm using my own object names... please use your own real names in
any code you use.

In form design mode, select your RunReportButton's OnClick property.
Put your cursor in the text field next to the OnClick event.
Usng the arrow on the right, select [Event Procedure]
Click on the 3 dots to the right (...)
You'll now be in the form's module where you'll see...

Private Sub RunReport_OnClick()

End Sub

Put the code between the lines...

Private Sub RunReport_OnClick()
If IsNull(cboEmpID) Then
DoCmd.OpenReport "rptReport", acViewPreview
Else
DoCmd.OpenReport "rptReport", acViewPreview,, "EmpID = " & Me.EmpID)
End If
End Sub

Test by setting a cboEmpID value, or deleting any cboEmpID value...
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."




Saylindara said:
I'm having a lot of trouble posting replies. I don't know if there is
something wrong with my PC or it's a more general problem.

I'm sorry to be so dense but I'm not sure where I put

Private Sub RunReportButton_Click()
If IsNull(cboEmpID) Then
DoCmd.OpenReport "rptReport", acViewPreview
Else
DoCmd.OpenReport "rptReport", acViewPreview,, "EmpID = " & Me.EmpID)
End If
End Sub

or what I have to delete.

Here's hoping this post goes through.

To be sure that cboEmpID actually contains the correct "bound" value,
add a text
control to the form with
= cboEmpID
calculation in it's ControlSource. That will show you what the real vs
displayed
cboEmpID value is.
--
hth
Al Campagna
Microsoft Access MVP


Al Campagna said:
Saylindara,
You should have the ParamForm open, and an EmpID (or none) selected
BEFORE you run the report. The report should be run from a
ParamForm button, and the form should remain open while the report is
run.

Instead of using a query parameter, use the Where condition of the
OpenReport method to report on just 1 Employee or All... depending
on the value in cboEmpID.

Private Sub RunReportButton_Click()
If IsNull(cboEmpID) Then
DoCmd.OpenReport "rptReport", acViewPreview
Else
DoCmd.OpenReport "rptReport", acViewPreview,, "EmpID = " &
Me.EmpID)
End If
End Sub

To be sure that cboEmpID actually contains the correct "bound" value,
add a text
control to the form with
= cboEmpID
calculation in it's ControlSource. That will show you what the real vs
displayed
cboEmpID value is.
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your
life."


I've been having trouble posting a reply and getting 'Service
temporarily
unavailable' messages. I don't know if anyone else has had the same
problem.

I should have explained the problem better.

I created an unbound form: Paramform. Then I added a combobox:
cboFindName.
The combobox showed a list of employee names with the bound field
EmployeeID.
Then I added a command button and coded the ClickEvent:
Me.Visible=False.

In the EmployeeID field of the query I wrote:
forms!ParamForm!cboFindName.

I coded the report's OpenEvent: DoCmd.OpenForm "ParamForm",,,,,acDialog
and
the CloseEvent: DoCmd.Close acForm, "ParamForm".

I would also like to have an option on the unbound form to print all
the
records.


:

Saylindara,
Yes, it is possible.
But, there are many ways to handle parameters. I'm not sure
what you've done with Fred in the past, but...
If you have a specific question about a specific parameter,
that would be best to post. But here's a sample...

Say you had a parameter, in your query, that asked for a Company
Name.
And you had the following Companies in the table.
Smith
Smith & Sons
Smithers Products

In the CompanyName column you had the following criteria...
= Like [Enter Company Name]
if you enter "Smith" at the parameter prompt... only Smith will be
returned
(Exact match)

In the CompanyName column you had the following criteria...
(using a global * variable)
= Like [Enter Company Name] & "*"
if you enter "Smi" at the parameter prompt... Smith and Smith & Sons
would
be will be returned.
(begins with... and after that I don't care)

In the CompanyName column you had the following criteria...
= Like "*" & [Enter Company Name] & "*"
if you enter "mit" at the parameter prompt... Smith, Smith & Sons, and
Smithers Products will be returned.
(Has this string anywhere within the Company Name)
***But*** if prompt is null (left blank), will return all Company
Names.
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your
life."



I am tentatively dipping my toe in the water. I would like to add
parameters
to several of my reports and have successfully followed the
directions
posted
by fredg on 1/8/2009 on a simple test report using one parameter.
(Ultimately my reports will have 2 or more parameters, when I
daresay I
will
get my knickers in a knot.) I would also like users to have the
option
to
view the whole report (i.e. no parameters) as well if they want to.
Is
that
possible?
 
A

Al Campagna

Saylindara,
I may have confused you with my previous code...
As we discussed, you shouldn't be filtering the report by EmployeeName.
We should use the unique EmpID value.
Your combo (cboEmpID) on the ParamForm should return a legitimate
EmpID, and make sure you use cboEmpID value in your Open Report
Where argument.

Notice in this code that the EmployeeID field of your query is filtered
by
the cboEmpID on form ParamForm.
The form must be open when the report is run.
Remove any criteria you have hard wired in the query itself.
Notice the Refresh I've added.
Each DoCmd should all be on one line.

Private Sub Command2_Click()
Refresh
If IsNull(cboFindName) Then
DoCmd.OpenReport "r_EventsAttendedByEmployee", acViewPreview
Else
DoCmd.OpenReport "r_EventsAttendedByEmployee", , EmployeeID = "&
Me.cboEmpID)
End If
End Sub

Doing it this way, we don't hard-wire the query to any specific filter.
Another button on your form could filter the same query and report
by some City value, or some State value, or whatever we want.

Have patience... we will get this resolved
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."


Saylindara said:
I added this to the command button OnClick event:

Private Sub Command2_Click()
If IsNull(cboFindName) Then
DoCmd.OpenReport "r_EventsAttendedByEmployee", acViewPreview
Else
DoCmd.OpenReport "r_EventsAttendedByEmployee", , EmployeeID = "&
Me.EmployeeID)
End If
End Sub

This opened the form when the combobox was blank but when I tried it with
employee names the form opens but is blank. I had already checked as you
recommended that I had the correct bound column (such a good idea as it
showed I hadn't and I was able to correct it). I then thought I had a
brainwave (I should have known better) that it was because I hadn't got
employeeID on the form, so I added that in the details section but it
didn't
make any difference. Should I be putting something in the query? I tried
what
I had originally 'forms!PramForm!cboFindName' but a form popped up asking
me
for parameters.

I know you must be getting fed up with me now but any further help would
be
greatly appreciated.

Al Campagna said:
Saylindara,
I'm using my own object names... please use your own real names in
any code you use.

In form design mode, select your RunReportButton's OnClick property.
Put your cursor in the text field next to the OnClick event.
Usng the arrow on the right, select [Event Procedure]
Click on the 3 dots to the right (...)
You'll now be in the form's module where you'll see...

Private Sub RunReport_OnClick()

End Sub

Put the code between the lines...

Private Sub RunReport_OnClick()
If IsNull(cboEmpID) Then
DoCmd.OpenReport "rptReport", acViewPreview
Else
DoCmd.OpenReport "rptReport", acViewPreview,, "EmpID = " &
Me.EmpID)
End If
End Sub

Test by setting a cboEmpID value, or deleting any cboEmpID value...
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your
life."




Saylindara said:
I'm having a lot of trouble posting replies. I don't know if there is
something wrong with my PC or it's a more general problem.

I'm sorry to be so dense but I'm not sure where I put

Private Sub RunReportButton_Click()
If IsNull(cboEmpID) Then
DoCmd.OpenReport "rptReport", acViewPreview
Else
DoCmd.OpenReport "rptReport", acViewPreview,, "EmpID = " & Me.EmpID)
End If
End Sub

or what I have to delete.

Here's hoping this post goes through.

To be sure that cboEmpID actually contains the correct "bound" value,
add a text
control to the form with
= cboEmpID
calculation in it's ControlSource. That will show you what the real vs
displayed
cboEmpID value is.
--
hth
Al Campagna
Microsoft Access MVP


:

Saylindara,
You should have the ParamForm open, and an EmpID (or none)
selected
BEFORE you run the report. The report should be run from a
ParamForm button, and the form should remain open while the report is
run.

Instead of using a query parameter, use the Where condition of the
OpenReport method to report on just 1 Employee or All... depending
on the value in cboEmpID.

Private Sub RunReportButton_Click()
If IsNull(cboEmpID) Then
DoCmd.OpenReport "rptReport", acViewPreview
Else
DoCmd.OpenReport "rptReport", acViewPreview,, "EmpID = " &
Me.EmpID)
End If
End Sub

To be sure that cboEmpID actually contains the correct "bound"
value,
add a text
control to the form with
= cboEmpID
calculation in it's ControlSource. That will show you what the real
vs
displayed
cboEmpID value is.
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your
life."


I've been having trouble posting a reply and getting 'Service
temporarily
unavailable' messages. I don't know if anyone else has had the same
problem.

I should have explained the problem better.

I created an unbound form: Paramform. Then I added a combobox:
cboFindName.
The combobox showed a list of employee names with the bound field
EmployeeID.
Then I added a command button and coded the ClickEvent:
Me.Visible=False.

In the EmployeeID field of the query I wrote:
forms!ParamForm!cboFindName.

I coded the report's OpenEvent: DoCmd.OpenForm
"ParamForm",,,,,acDialog
and
the CloseEvent: DoCmd.Close acForm, "ParamForm".

I would also like to have an option on the unbound form to print all
the
records.


:

Saylindara,
Yes, it is possible.
But, there are many ways to handle parameters. I'm not sure
what you've done with Fred in the past, but...
If you have a specific question about a specific parameter,
that would be best to post. But here's a sample...

Say you had a parameter, in your query, that asked for a
Company
Name.
And you had the following Companies in the table.
Smith
Smith & Sons
Smithers Products

In the CompanyName column you had the following criteria...
= Like [Enter Company Name]
if you enter "Smith" at the parameter prompt... only Smith will be
returned
(Exact match)

In the CompanyName column you had the following criteria...
(using a global * variable)
= Like [Enter Company Name] & "*"
if you enter "Smi" at the parameter prompt... Smith and Smith &
Sons
would
be will be returned.
(begins with... and after that I don't care)

In the CompanyName column you had the following criteria...
= Like "*" & [Enter Company Name] & "*"
if you enter "mit" at the parameter prompt... Smith, Smith & Sons,
and
Smithers Products will be returned.
(Has this string anywhere within the Company Name)
***But*** if prompt is null (left blank), will return all Company
Names.
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in
your
life."



message
I am tentatively dipping my toe in the water. I would like to add
parameters
to several of my reports and have successfully followed the
directions
posted
by fredg on 1/8/2009 on a simple test report using one parameter.
(Ultimately my reports will have 2 or more parameters, when I
daresay I
will
get my knickers in a knot.) I would also like users to have the
option
to
view the whole report (i.e. no parameters) as well if they want
to.
Is
that
possible?
 
S

Saylindara

For some reason my posts have not been accepted for several days - I don't
know if this one will be accepted either but I'm giving it another try.
Because of this I had to go to another forum to fine tune what you started. I
just wanted you to know you really helped me with my problem and to thank you
for taking the trouble.

Al Campagna said:
Saylindara,
I may have confused you with my previous code...
As we discussed, you shouldn't be filtering the report by EmployeeName.
We should use the unique EmpID value.
Your combo (cboEmpID) on the ParamForm should return a legitimate
EmpID, and make sure you use cboEmpID value in your Open Report
Where argument.

Notice in this code that the EmployeeID field of your query is filtered
by
the cboEmpID on form ParamForm.
The form must be open when the report is run.
Remove any criteria you have hard wired in the query itself.
Notice the Refresh I've added.
Each DoCmd should all be on one line.

Private Sub Command2_Click()
Refresh
If IsNull(cboFindName) Then
DoCmd.OpenReport "r_EventsAttendedByEmployee", acViewPreview
Else
DoCmd.OpenReport "r_EventsAttendedByEmployee", , EmployeeID = "&
Me.cboEmpID)
End If
End Sub

Doing it this way, we don't hard-wire the query to any specific filter.
Another button on your form could filter the same query and report
by some City value, or some State value, or whatever we want.

Have patience... we will get this resolved
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."


Saylindara said:
I added this to the command button OnClick event:

Private Sub Command2_Click()
If IsNull(cboFindName) Then
DoCmd.OpenReport "r_EventsAttendedByEmployee", acViewPreview
Else
DoCmd.OpenReport "r_EventsAttendedByEmployee", , EmployeeID = "&
Me.EmployeeID)
End If
End Sub

This opened the form when the combobox was blank but when I tried it with
employee names the form opens but is blank. I had already checked as you
recommended that I had the correct bound column (such a good idea as it
showed I hadn't and I was able to correct it). I then thought I had a
brainwave (I should have known better) that it was because I hadn't got
employeeID on the form, so I added that in the details section but it
didn't
make any difference. Should I be putting something in the query? I tried
what
I had originally 'forms!PramForm!cboFindName' but a form popped up asking
me
for parameters.

I know you must be getting fed up with me now but any further help would
be
greatly appreciated.

Al Campagna said:
Saylindara,
I'm using my own object names... please use your own real names in
any code you use.

In form design mode, select your RunReportButton's OnClick property.
Put your cursor in the text field next to the OnClick event.
Usng the arrow on the right, select [Event Procedure]
Click on the 3 dots to the right (...)
You'll now be in the form's module where you'll see...

Private Sub RunReport_OnClick()

End Sub

Put the code between the lines...

Private Sub RunReport_OnClick()
If IsNull(cboEmpID) Then
DoCmd.OpenReport "rptReport", acViewPreview
Else
DoCmd.OpenReport "rptReport", acViewPreview,, "EmpID = " &
Me.EmpID)
End If
End Sub

Test by setting a cboEmpID value, or deleting any cboEmpID value...
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your
life."




I'm having a lot of trouble posting replies. I don't know if there is
something wrong with my PC or it's a more general problem.

I'm sorry to be so dense but I'm not sure where I put

Private Sub RunReportButton_Click()
If IsNull(cboEmpID) Then
DoCmd.OpenReport "rptReport", acViewPreview
Else
DoCmd.OpenReport "rptReport", acViewPreview,, "EmpID = " & Me.EmpID)
End If
End Sub

or what I have to delete.

Here's hoping this post goes through.

To be sure that cboEmpID actually contains the correct "bound" value,
add a text
control to the form with
= cboEmpID
calculation in it's ControlSource. That will show you what the real vs
displayed
cboEmpID value is.
--
hth
Al Campagna
Microsoft Access MVP


:

Saylindara,
You should have the ParamForm open, and an EmpID (or none)
selected
BEFORE you run the report. The report should be run from a
ParamForm button, and the form should remain open while the report is
run.

Instead of using a query parameter, use the Where condition of the
OpenReport method to report on just 1 Employee or All... depending
on the value in cboEmpID.

Private Sub RunReportButton_Click()
If IsNull(cboEmpID) Then
DoCmd.OpenReport "rptReport", acViewPreview
Else
DoCmd.OpenReport "rptReport", acViewPreview,, "EmpID = " &
Me.EmpID)
End If
End Sub

To be sure that cboEmpID actually contains the correct "bound"
value,
add a text
control to the form with
= cboEmpID
calculation in it's ControlSource. That will show you what the real
vs
displayed
cboEmpID value is.
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your
life."


I've been having trouble posting a reply and getting 'Service
temporarily
unavailable' messages. I don't know if anyone else has had the same
problem.

I should have explained the problem better.

I created an unbound form: Paramform. Then I added a combobox:
cboFindName.
The combobox showed a list of employee names with the bound field
EmployeeID.
Then I added a command button and coded the ClickEvent:
Me.Visible=False.

In the EmployeeID field of the query I wrote:
forms!ParamForm!cboFindName.

I coded the report's OpenEvent: DoCmd.OpenForm
"ParamForm",,,,,acDialog
and
the CloseEvent: DoCmd.Close acForm, "ParamForm".

I would also like to have an option on the unbound form to print all
the
records.


:

Saylindara,
Yes, it is possible.
But, there are many ways to handle parameters. I'm not sure
what you've done with Fred in the past, but...
If you have a specific question about a specific parameter,
that would be best to post. But here's a sample...

Say you had a parameter, in your query, that asked for a
Company
Name.
And you had the following Companies in the table.
Smith
Smith & Sons
Smithers Products

In the CompanyName column you had the following criteria...
= Like [Enter Company Name]
if you enter "Smith" at the parameter prompt... only Smith will be
returned
(Exact match)

In the CompanyName column you had the following criteria...
(using a global * variable)
= Like [Enter Company Name] & "*"
if you enter "Smi" at the parameter prompt... Smith and Smith &
Sons
would
be will be returned.
(begins with... and after that I don't care)

In the CompanyName column you had the following criteria...
= Like "*" & [Enter Company Name] & "*"
if you enter "mit" at the parameter prompt... Smith, Smith & Sons,
and
Smithers Products will be returned.
(Has this string anywhere within the Company Name)
***But*** if prompt is null (left blank), will return all Company
Names.
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in
your
life."



message
I am tentatively dipping my toe in the water. I would like to add
parameters
to several of my reports and have successfully followed the
directions
posted
by fredg on 1/8/2009 on a simple test report using one parameter.
(Ultimately my reports will have 2 or more parameters, when I
daresay I
will
get my knickers in a knot.) I would also like users to have the
option
to
view the whole report (i.e. no parameters) as well if they want
to.
Is
that
possible?
 
A

Al Campagna

OK Saylindara,
Thanks for letting me know that.
--
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."

Saylindara said:
For some reason my posts have not been accepted for several days - I don't
know if this one will be accepted either but I'm giving it another try.
Because of this I had to go to another forum to fine tune what you
started. I
just wanted you to know you really helped me with my problem and to thank
you
for taking the trouble.

Al Campagna said:
Saylindara,
I may have confused you with my previous code...
As we discussed, you shouldn't be filtering the report by
EmployeeName.
We should use the unique EmpID value.
Your combo (cboEmpID) on the ParamForm should return a legitimate
EmpID, and make sure you use cboEmpID value in your Open Report
Where argument.

Notice in this code that the EmployeeID field of your query is
filtered
by
the cboEmpID on form ParamForm.
The form must be open when the report is run.
Remove any criteria you have hard wired in the query itself.
Notice the Refresh I've added.
Each DoCmd should all be on one line.

Private Sub Command2_Click()
Refresh
If IsNull(cboFindName) Then
DoCmd.OpenReport "r_EventsAttendedByEmployee", acViewPreview
Else
DoCmd.OpenReport "r_EventsAttendedByEmployee", , EmployeeID = "&
Me.cboEmpID)
End If
End Sub

Doing it this way, we don't hard-wire the query to any specific
filter.
Another button on your form could filter the same query and report
by some City value, or some State value, or whatever we want.

Have patience... we will get this resolved
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your
life."


Saylindara said:
I added this to the command button OnClick event:

Private Sub Command2_Click()
If IsNull(cboFindName) Then
DoCmd.OpenReport "r_EventsAttendedByEmployee", acViewPreview
Else
DoCmd.OpenReport "r_EventsAttendedByEmployee", , EmployeeID = "&
Me.EmployeeID)
End If
End Sub

This opened the form when the combobox was blank but when I tried it
with
employee names the form opens but is blank. I had already checked as
you
recommended that I had the correct bound column (such a good idea as it
showed I hadn't and I was able to correct it). I then thought I had a
brainwave (I should have known better) that it was because I hadn't got
employeeID on the form, so I added that in the details section but it
didn't
make any difference. Should I be putting something in the query? I
tried
what
I had originally 'forms!PramForm!cboFindName' but a form popped up
asking
me
for parameters.

I know you must be getting fed up with me now but any further help
would
be
greatly appreciated.

:

Saylindara,
I'm using my own object names... please use your own real names in
any code you use.

In form design mode, select your RunReportButton's OnClick
property.
Put your cursor in the text field next to the OnClick event.
Usng the arrow on the right, select [Event Procedure]
Click on the 3 dots to the right (...)
You'll now be in the form's module where you'll see...

Private Sub RunReport_OnClick()

End Sub

Put the code between the lines...

Private Sub RunReport_OnClick()
If IsNull(cboEmpID) Then
DoCmd.OpenReport "rptReport", acViewPreview
Else
DoCmd.OpenReport "rptReport", acViewPreview,, "EmpID = " &
Me.EmpID)
End If
End Sub

Test by setting a cboEmpID value, or deleting any cboEmpID
value...
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your
life."




I'm having a lot of trouble posting replies. I don't know if there
is
something wrong with my PC or it's a more general problem.

I'm sorry to be so dense but I'm not sure where I put

Private Sub RunReportButton_Click()
If IsNull(cboEmpID) Then
DoCmd.OpenReport "rptReport", acViewPreview
Else
DoCmd.OpenReport "rptReport", acViewPreview,, "EmpID = " & Me.EmpID)
End If
End Sub

or what I have to delete.

Here's hoping this post goes through.

To be sure that cboEmpID actually contains the correct "bound"
value,
add a text
control to the form with
= cboEmpID
calculation in it's ControlSource. That will show you what the real
vs
displayed
cboEmpID value is.
--
hth
Al Campagna
Microsoft Access MVP


:

Saylindara,
You should have the ParamForm open, and an EmpID (or none)
selected
BEFORE you run the report. The report should be run from a
ParamForm button, and the form should remain open while the report
is
run.

Instead of using a query parameter, use the Where condition of the
OpenReport method to report on just 1 Employee or All... depending
on the value in cboEmpID.

Private Sub RunReportButton_Click()
If IsNull(cboEmpID) Then
DoCmd.OpenReport "rptReport", acViewPreview
Else
DoCmd.OpenReport "rptReport", acViewPreview,, "EmpID = " &
Me.EmpID)
End If
End Sub

To be sure that cboEmpID actually contains the correct "bound"
value,
add a text
control to the form with
= cboEmpID
calculation in it's ControlSource. That will show you what the
real
vs
displayed
cboEmpID value is.
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in
your
life."


message
I've been having trouble posting a reply and getting 'Service
temporarily
unavailable' messages. I don't know if anyone else has had the
same
problem.

I should have explained the problem better.

I created an unbound form: Paramform. Then I added a combobox:
cboFindName.
The combobox showed a list of employee names with the bound field
EmployeeID.
Then I added a command button and coded the ClickEvent:
Me.Visible=False.

In the EmployeeID field of the query I wrote:
forms!ParamForm!cboFindName.

I coded the report's OpenEvent: DoCmd.OpenForm
"ParamForm",,,,,acDialog
and
the CloseEvent: DoCmd.Close acForm, "ParamForm".

I would also like to have an option on the unbound form to print
all
the
records.


:

Saylindara,
Yes, it is possible.
But, there are many ways to handle parameters. I'm not sure
what you've done with Fred in the past, but...
If you have a specific question about a specific parameter,
that would be best to post. But here's a sample...

Say you had a parameter, in your query, that asked for a
Company
Name.
And you had the following Companies in the table.
Smith
Smith & Sons
Smithers Products

In the CompanyName column you had the following criteria...
= Like [Enter Company Name]
if you enter "Smith" at the parameter prompt... only Smith will
be
returned
(Exact match)

In the CompanyName column you had the following criteria...
(using a global * variable)
= Like [Enter Company Name] & "*"
if you enter "Smi" at the parameter prompt... Smith and Smith &
Sons
would
be will be returned.
(begins with... and after that I don't care)

In the CompanyName column you had the following criteria...
= Like "*" & [Enter Company Name] & "*"
if you enter "mit" at the parameter prompt... Smith, Smith &
Sons,
and
Smithers Products will be returned.
(Has this string anywhere within the Company Name)
***But*** if prompt is null (left blank), will return all
Company
Names.
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in
your
life."



message
I am tentatively dipping my toe in the water. I would like to
add
parameters
to several of my reports and have successfully followed the
directions
posted
by fredg on 1/8/2009 on a simple test report using one
parameter.
(Ultimately my reports will have 2 or more parameters, when I
daresay I
will
get my knickers in a knot.) I would also like users to have
the
option
to
view the whole report (i.e. no parameters) as well if they
want
to.
Is
that
possible?
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top