Choosing from a combo box to run a report

[WMorsberger]

I have created a form that will allow me to choose an item from a drop down
box to base the report off of. I am having a couple of problems though. The
first problem being that since I made an unbound form, if I pull the
information for the combo box out of the original table I then don't show
anything on the report. I then tried to link the combo box to the query that
I am trying to run the report off of and when I do that the combo box shows
me nothing. Can someone please help?

 

[Duane Hookom]

How do you "base the report off of" the drop down box?
Generally, you should open the form and select a value from the combo box.
Then click a button that has code like:

Dim strWhere as String
Dim strReport as String
strReport = "rptMyReportName"
strWhere = "1=1 "
If Not IsNull(Me.cboMyCombo) Then
strWhere = strWhere & " AND [MyField] = """ & Me.cboMyCombo & """ "
End If
DoCmd.OpenReport strReport, acPreview, , strWhere

 

[WMorsberger]

I do have a button that once the value is chosen in the drop down box the
report comes up on the screen, but there is nothing in the report. It is
almost like it is not reading the information that I choose in the drop down
box. I know that there is information that should be populating on the
report but for some reason it is not when I choose from the drop down box. I
hope this makes sense, sometimes I don't even understand what I type.

How do you "base the report off of" the drop down box?
Generally, you should open the form and select a value from the combo box.
Then click a button that has code like:

Dim strWhere as String
Dim strReport as String
strReport = "rptMyReportName"
strWhere = "1=1 "
If Not IsNull(Me.cboMyCombo) Then
strWhere = strWhere & " AND [MyField] = """ & Me.cboMyCombo & """ "
End If
DoCmd.OpenReport strReport, acPreview, , strWhere


--
Duane Hookom
MS Access MVP

I have created a form that will allow me to choose an item from a drop down
box to base the report off of. I am having a couple of problems though.
The
first problem being that since I made an unbound form, if I pull the
information for the combo box out of the original table I then don't show
anything on the report. I then tried to link the combo box to the query
that
I am trying to run the report off of and when I do that the combo box
shows
me nothing. Can someone please help?

 

[Duane Hookom]

Again "How do you "base the report off of" the drop down box?"

--
Duane Hookom
MS Access MVP

I do have a button that once the value is chosen in the drop down box the
report comes up on the screen, but there is nothing in the report. It is
almost like it is not reading the information that I choose in the drop
down
box. I know that there is information that should be populating on the
report but for some reason it is not when I choose from the drop down box.
I
hope this makes sense, sometimes I don't even understand what I type.

How do you "base the report off of" the drop down box?
Generally, you should open the form and select a value from the combo
box.
Then click a button that has code like:

Dim strWhere as String
Dim strReport as String
strReport = "rptMyReportName"
strWhere = "1=1 "
If Not IsNull(Me.cboMyCombo) Then
strWhere = strWhere & " AND [MyField] = """ & Me.cboMyCombo & """ "
End If
DoCmd.OpenReport strReport, acPreview, , strWhere


--
Duane Hookom
MS Access MVP

I have created a form that will allow me to choose an item from a drop
down
box to base the report off of. I am having a couple of problems
though.
The
first problem being that since I made an unbound form, if I pull the
information for the combo box out of the original table I then don't
show
anything on the report. I then tried to link the combo box to the
query
that
I am trying to run the report off of and when I do that the combo box
shows
me nothing. Can someone please help?

 

[WMorsberger]

Well from prior posts that I have read regarding trying to have criteria on a
report that can be choosen from a drop down box so that the only thing that
shows on the report is based on the criteria that you have chosen.

This is the information that I set up on my database:

Let's assume it is a CustomerID number you need as criteria.

Make a new unbound form.
Add a combo box that will show the CustomerID field.
Make sure the Combo Box Bound Column is the
CustomerID field.
Add a command button to the form.
Code the button's Click event:
Me.Visible = False
Name this form "ParamForm"

Code the Report's Record Source Query's CustomerID field criteria
line:
forms!ParamForm!ComboBoxName

Code the Report's Open Event:
DoCmd.OpenForm "ParamForm" , , , , , acDialog

Code the Report's Close event:
DoCmd.Close acForm, "ParamForm"

Run the Report.
The report will open the form.

Find the CustomerID in the combo box.
Click the command button.

The Report will display just those records selected.
When the Report closes it will close the form.


I hope this is what you mean - I'm a little lost - I have everything in this
command on my report except it is not bringing back any data.

Again "How do you "base the report off of" the drop down box?"

--
Duane Hookom
MS Access MVP

I do have a button that once the value is chosen in the drop down box the
report comes up on the screen, but there is nothing in the report. It is
almost like it is not reading the information that I choose in the drop
down
box. I know that there is information that should be populating on the
report but for some reason it is not when I choose from the drop down box.
I
hope this makes sense, sometimes I don't even understand what I type.

How do you "base the report off of" the drop down box?
Generally, you should open the form and select a value from the combo
box.
Then click a button that has code like:

Dim strWhere as String
Dim strReport as String
strReport = "rptMyReportName"
strWhere = "1=1 "
If Not IsNull(Me.cboMyCombo) Then
strWhere = strWhere & " AND [MyField] = """ & Me.cboMyCombo & """ "
End If
DoCmd.OpenReport strReport, acPreview, , strWhere


--
Duane Hookom
MS Access MVP

I have created a form that will allow me to choose an item from a drop
down
box to base the report off of. I am having a couple of problems
though.
The
first problem being that since I made an unbound form, if I pull the
information for the combo box out of the original table I then don't
show
anything on the report. I then tried to link the combo box to the
query
that
I am trying to run the report off of and when I do that the combo box
shows
me nothing. Can someone please help?

 

[Duane Hookom]

I'm not sure how many experienced developers use code in the report open
event to open a form for parameters. If you are using that method, start
with the form closed.

I don't ever use that solution. I always have a form open where the user can
enter criteria. Code will then open the report using either a "Where Clause"
in the DoCmd.OpenReport as my sample code suggests or having a reference to
a control on a form hard-coded into the query.

--
Duane Hookom
MS Access MVP

Well from prior posts that I have read regarding trying to have criteria
on a
report that can be choosen from a drop down box so that the only thing
that
shows on the report is based on the criteria that you have chosen.

This is the information that I set up on my database:

Let's assume it is a CustomerID number you need as criteria.

Make a new unbound form.
Add a combo box that will show the CustomerID field.
Make sure the Combo Box Bound Column is the
CustomerID field.
Add a command button to the form.
Code the button's Click event:
Me.Visible = False
Name this form "ParamForm"

Code the Report's Record Source Query's CustomerID field criteria
line:
forms!ParamForm!ComboBoxName

Code the Report's Open Event:
DoCmd.OpenForm "ParamForm" , , , , , acDialog

Code the Report's Close event:
DoCmd.Close acForm, "ParamForm"

Run the Report.
The report will open the form.

Find the CustomerID in the combo box.
Click the command button.

The Report will display just those records selected.
When the Report closes it will close the form.


I hope this is what you mean - I'm a little lost - I have everything in
this
command on my report except it is not bringing back any data.

Again "How do you "base the report off of" the drop down box?"

--
Duane Hookom
MS Access MVP

I do have a button that once the value is chosen in the drop down box
the
report comes up on the screen, but there is nothing in the report. It
is
almost like it is not reading the information that I choose in the drop
down
box. I know that there is information that should be populating on the
report but for some reason it is not when I choose from the drop down
box.
I
hope this makes sense, sometimes I don't even understand what I type.

:

How do you "base the report off of" the drop down box?
Generally, you should open the form and select a value from the combo
box.
Then click a button that has code like:

Dim strWhere as String
Dim strReport as String
strReport = "rptMyReportName"
strWhere = "1=1 "
If Not IsNull(Me.cboMyCombo) Then
strWhere = strWhere & " AND [MyField] = """ & Me.cboMyCombo & """
"
End If
DoCmd.OpenReport strReport, acPreview, , strWhere


--
Duane Hookom
MS Access MVP

I have created a form that will allow me to choose an item from a
drop
down
box to base the report off of. I am having a couple of problems
though.
The
first problem being that since I made an unbound form, if I pull the
information for the combo box out of the original table I then don't
show
anything on the report. I then tried to link the combo box to the
query
that
I am trying to run the report off of and when I do that the combo
box
shows
me nothing. Can someone please help?

 

[Ken Sheridan]

Duane:

I think it’s a fairly common technique. I seem to recall seeing an example
from MS somewhere which loops in the Open event procedure until the form is
opened, and the Solutions database includes a report where the Open event
rather crudely prompts the user to open a dialogue form. Here's an example
of mine from a report's Open event procedure which filters a report based on
an unrestricted query, but it could equally be done where the query
references the control as a parameter. The dialogue form can be used with
different reports as the report's name is passed to it:

Private Sub Report_Open(Cancel As Integer)

Const FORMNOTOPEN = 2450
Dim frm As Form

On Error Resume Next
Set frm = Forms!frmNameDlg
If Err = FORMNOTOPEN Then
' open dialogue form, passing report name to it
DoCmd.OpenForm "frmNameDlg", OpenArgs:=Me.Name
Cancel = True
Else
If Err <> 0 Then
' unknown error
MsgBox Err.Description, vbExclamation, "Error"
Else
If Not IsNull(frm.txtLastName) Then
Me.Filter = "LastName= """ & frm.txtLastName & """"
Me.FilterOn = True
Else
Me.FilterOn = False
End If
DoCmd.Close acForm, frm.Name
End If
End If

End Sub

The button on the dialogue form has the following as its Click event
procedure:

Private Sub cmdOpenReport_Click()

Const REPORTCANCELLED = 2501

On Error Resume Next
' open report whose name was passed to form
DoCmd.OpenReport Me.OpenArgs, acViewPreview
Select Case Err.Number
Case 0
' no error so do nothing
Case REPORTCANCELLED
' anticipated error so do nothing
Case Else
'unknown error so inform user
MsgBox Err.Description
End Select

End Sub

Ken Sheridan
Stafford, England

 

[Duane Hookom]

Thanks for the example Ken. I know it can be done and there are samples like
this in MS stuff. It just hasn't made any sense to me to use it in any apps
I have written.

--
Duane Hookom
MS Access MVP

Duane:

I think it's a fairly common technique. I seem to recall seeing an
example
from MS somewhere which loops in the Open event procedure until the form
is
opened, and the Solutions database includes a report where the Open event
rather crudely prompts the user to open a dialogue form. Here's an
example
of mine from a report's Open event procedure which filters a report based
on
an unrestricted query, but it could equally be done where the query
references the control as a parameter. The dialogue form can be used with
different reports as the report's name is passed to it:

Private Sub Report_Open(Cancel As Integer)

Const FORMNOTOPEN = 2450
Dim frm As Form

On Error Resume Next
Set frm = Forms!frmNameDlg
If Err = FORMNOTOPEN Then
' open dialogue form, passing report name to it
DoCmd.OpenForm "frmNameDlg", OpenArgs:=Me.Name
Cancel = True
Else
If Err <> 0 Then
' unknown error
MsgBox Err.Description, vbExclamation, "Error"
Else
If Not IsNull(frm.txtLastName) Then
Me.Filter = "LastName= """ & frm.txtLastName & """"
Me.FilterOn = True
Else
Me.FilterOn = False
End If
DoCmd.Close acForm, frm.Name
End If
End If

End Sub

The button on the dialogue form has the following as its Click event
procedure:

Private Sub cmdOpenReport_Click()

Const REPORTCANCELLED = 2501

On Error Resume Next
' open report whose name was passed to form
DoCmd.OpenReport Me.OpenArgs, acViewPreview
Select Case Err.Number
Case 0
' no error so do nothing
Case REPORTCANCELLED
' anticipated error so do nothing
Case Else
'unknown error so inform user
MsgBox Err.Description
End Select

End Sub

Ken Sheridan
Stafford, England

 

[WMorsberger]

So, Is there something that I am doing wrong with what I have that would keep
the information from populating on the report?

Thanks for the example Ken. I know it can be done and there are samples like
this in MS stuff. It just hasn't made any sense to me to use it in any apps
I have written.

 

[Duane Hookom]

Again, is ParamForm closed when you first attempt to open your report? Are
you using a command button to "close/hide" the ParamForm?

 

[WMorsberger]

The form is closed when i run the report - when i click on the report to open
it - the form then comes up with the combo box so that I can choose what to
run the report on.

 

[Duane Hookom]

And then what happens? I assume you select a value in the combo box and
click a button. After the button is clicked, what happens? What is the SQL
view of the Record Source of your report?

 

[WMorsberger]

I hope I am giving you the right information - I am new with access so I'm
still learning all the terms.


The record source for the report that i am running is a Query: Reroutes Not
Received Back

After the button is clicked on the form the report is this shown on the
screen. Right now the information is blank. In the report screen under the
properties I have on the ON OPEN the following code:


Private Sub Report_Open(Cancel As Integer)
DoCmd.OpenForm "ParamForm", , , , , acDialog

End Sub


On the ON CLOSE this is what I have:

Private Sub Report_Close()
DoCmd.Close acForm, "ParamForm"
End Sub

 

[Duane Hookom]

You need to open the query "Reroutes Not Received Back" in SQL view and copy
the sql and paste it into a reply.

 

[WMorsberger]

Here is what is in the SQL

SELECT [Reroutes Table].ID, [Reroutes Table].Date, [Reroutes Table].[Reroute
Contacts], [Reroutes Table].[MCS Number], [Reroutes Table].[Count Of Pieces],
[Reroutes Table].[1st Attempt Follow-up Date], [Reroutes Table].[2nd Attempt
Follow-up Date], [Reroutes Table].[3rd Attempt Follow-up Date], [Reroutes
Table].[Date Received Back]
FROM [Reroutes Table]
WHERE ((([Reroutes Table].[Reroute
Contacts])=[forms]![ParamForm]![RerouteContacts]) AND (([Reroutes
Table].[Date Received Back]) Is Null));

 

[Duane Hookom]

What do you get if you:
- open the ParamForm
- select a value in RerouteContacts
- view the datasheet of your query


--
Duane Hookom
MS Access MVP

Here is what is in the SQL

SELECT [Reroutes Table].ID, [Reroutes Table].Date, [Reroutes
Table].[Reroute
Contacts], [Reroutes Table].[MCS Number], [Reroutes Table].[Count Of
Pieces],
[Reroutes Table].[1st Attempt Follow-up Date], [Reroutes Table].[2nd
Attempt
Follow-up Date], [Reroutes Table].[3rd Attempt Follow-up Date], [Reroutes
Table].[Date Received Back]
FROM [Reroutes Table]
WHERE ((([Reroutes Table].[Reroute
Contacts])=[forms]![ParamForm]![RerouteContacts]) AND (([Reroutes
Table].[Date Received Back]) Is Null));

You need to open the query "Reroutes Not Received Back" in SQL view and
copy
the sql and paste it into a reply.

 

[WMorsberger]

If I just open the form from the form menu I don't get anything after I
choose the value of the reroute contact - once I press the button the the
form closes and there is nothing there

What do you get if you:
- open the ParamForm
- select a value in RerouteContacts
- view the datasheet of your query


--
Duane Hookom
MS Access MVP

Here is what is in the SQL

SELECT [Reroutes Table].ID, [Reroutes Table].Date, [Reroutes
Table].[Reroute
Contacts], [Reroutes Table].[MCS Number], [Reroutes Table].[Count Of
Pieces],
[Reroutes Table].[1st Attempt Follow-up Date], [Reroutes Table].[2nd
Attempt
Follow-up Date], [Reroutes Table].[3rd Attempt Follow-up Date], [Reroutes
Table].[Date Received Back]
FROM [Reroutes Table]
WHERE ((([Reroutes Table].[Reroute
Contacts])=[forms]![ParamForm]![RerouteContacts]) AND (([Reroutes
Table].[Date Received Back]) Is Null));

You need to open the query "Reroutes Not Received Back" in SQL view and
copy
the sql and paste it into a reply.


--
Duane Hookom
MS Access MVP


I hope I am giving you the right information - I am new with access so
I'm
still learning all the terms.


The record source for the report that i am running is a Query:
Reroutes
Not
Received Back

After the button is clicked on the form the report is this shown on the
screen. Right now the information is blank. In the report screen
under
the
properties I have on the ON OPEN the following code:


Private Sub Report_Open(Cancel As Integer)
DoCmd.OpenForm "ParamForm", , , , , acDialog

End Sub


On the ON CLOSE this is what I have:

Private Sub Report_Close()
DoCmd.Close acForm, "ParamForm"
End Sub



:

And then what happens? I assume you select a value in the combo box
and
click a button. After the button is clicked, what happens? What is the
SQL
view of the Record Source of your report?

--
Duane Hookom
MS Access MVP

The form is closed when i run the report - when i click on the
report
to
open
it - the form then comes up with the combo box so that I can choose
what
to
run the report on.

:

Again, is ParamForm closed when you first attempt to open your
report?
Are
you using a command button to "close/hide" the ParamForm?

--
Duane Hookom
MS Access MVP

message
So, Is there something that I am doing wrong with what I have
that
would
keep
the information from populating on the report?

:

Thanks for the example Ken. I know it can be done and there are
samples
like
this in MS stuff. It just hasn't made any sense to me to use it
in
any
apps
I have written.

--
Duane Hookom
MS Access MVP

message
Duane:

I think it's a fairly common technique. I seem to recall
seeing
an
example
from MS somewhere which loops in the Open event procedure
until
the
form
is
opened, and the Solutions database includes a report where the
Open
event
rather crudely prompts the user to open a dialogue form.
Here's
an
example
of mine from a report's Open event procedure which filters a
report
based
on
an unrestricted query, but it could equally be done where the
query
references the control as a parameter. The dialogue form can
be
used
with
different reports as the report's name is passed to it:

Private Sub Report_Open(Cancel As Integer)

Const FORMNOTOPEN = 2450
Dim frm As Form

On Error Resume Next
Set frm = Forms!frmNameDlg
If Err = FORMNOTOPEN Then
' open dialogue form, passing report name to it
DoCmd.OpenForm "frmNameDlg", OpenArgs:=Me.Name
Cancel = True
Else
If Err <> 0 Then
' unknown error
MsgBox Err.Description, vbExclamation, "Error"
Else
If Not IsNull(frm.txtLastName) Then
Me.Filter = "LastName= """ & frm.txtLastName &
""""
Me.FilterOn = True
Else
Me.FilterOn = False
End If
DoCmd.Close acForm, frm.Name
End If
End If

End Sub

The button on the dialogue form has the following as its Click
event
procedure:

Private Sub cmdOpenReport_Click()

Const REPORTCANCELLED = 2501

On Error Resume Next
' open report whose name was passed to form
DoCmd.OpenReport Me.OpenArgs, acViewPreview
Select Case Err.Number
Case 0
' no error so do nothing
Case REPORTCANCELLED
' anticipated error so do nothing
Case Else
'unknown error so inform user
MsgBox Err.Description
End Select

End Sub

Ken Sheridan
Stafford, England

:

I'm not sure how many experienced developers use code in the
report
open
event to open a form for parameters. If you are using that
method,
start
with the form closed.

I don't ever use that solution. I always have a form open
where
the
user
can
enter criteria. Code will then open the report using either a
"Where
Clause"
in the DoCmd.OpenReport as my sample code suggests or having
a
reference
to
a control on a form hard-coded into the query.

 

[Duane Hookom]

You need to try step 3 again but it doesn't happen automatically. You must
find the query in the database window and open it.

--
Duane Hookom
MS Access MVP

If I just open the form from the form menu I don't get anything after I
choose the value of the reroute contact - once I press the button the the
form closes and there is nothing there

What do you get if you:
- open the ParamForm
- select a value in RerouteContacts
- view the datasheet of your query


--
Duane Hookom
MS Access MVP

Here is what is in the SQL

SELECT [Reroutes Table].ID, [Reroutes Table].Date, [Reroutes
Table].[Reroute
Contacts], [Reroutes Table].[MCS Number], [Reroutes Table].[Count Of
Pieces],
[Reroutes Table].[1st Attempt Follow-up Date], [Reroutes Table].[2nd
Attempt
Follow-up Date], [Reroutes Table].[3rd Attempt Follow-up Date],
[Reroutes
Table].[Date Received Back]
FROM [Reroutes Table]
WHERE ((([Reroutes Table].[Reroute
Contacts])=[forms]![ParamForm]![RerouteContacts]) AND (([Reroutes
Table].[Date Received Back]) Is Null));


:

You need to open the query "Reroutes Not Received Back" in SQL view
and
copy
the sql and paste it into a reply.


--
Duane Hookom
MS Access MVP


I hope I am giving you the right information - I am new with access
so
I'm
still learning all the terms.


The record source for the report that i am running is a Query:
Reroutes
Not
Received Back

After the button is clicked on the form the report is this shown on
the
screen. Right now the information is blank. In the report screen
under
the
properties I have on the ON OPEN the following code:


Private Sub Report_Open(Cancel As Integer)
DoCmd.OpenForm "ParamForm", , , , , acDialog

End Sub


On the ON CLOSE this is what I have:

Private Sub Report_Close()
DoCmd.Close acForm, "ParamForm"
End Sub



:

And then what happens? I assume you select a value in the combo box
and
click a button. After the button is clicked, what happens? What is
the
SQL
view of the Record Source of your report?

--
Duane Hookom
MS Access MVP

message
The form is closed when i run the report - when i click on the
report
to
open
it - the form then comes up with the combo box so that I can
choose
what
to
run the report on.

:

Again, is ParamForm closed when you first attempt to open your
report?
Are
you using a command button to "close/hide" the ParamForm?

--
Duane Hookom
MS Access MVP

message
So, Is there something that I am doing wrong with what I have
that
would
keep
the information from populating on the report?

:

Thanks for the example Ken. I know it can be done and there
are
samples
like
this in MS stuff. It just hasn't made any sense to me to use
it
in
any
apps
I have written.

--
Duane Hookom
MS Access MVP

in
message
Duane:

I think it's a fairly common technique. I seem to recall
seeing
an
example
from MS somewhere which loops in the Open event procedure
until
the
form
is
opened, and the Solutions database includes a report where
the
Open
event
rather crudely prompts the user to open a dialogue form.
Here's
an
example
of mine from a report's Open event procedure which filters
a
report
based
on
an unrestricted query, but it could equally be done where
the
query
references the control as a parameter. The dialogue form
can
be
used
with
different reports as the report's name is passed to it:

Private Sub Report_Open(Cancel As Integer)

Const FORMNOTOPEN = 2450
Dim frm As Form

On Error Resume Next
Set frm = Forms!frmNameDlg
If Err = FORMNOTOPEN Then
' open dialogue form, passing report name to it
DoCmd.OpenForm "frmNameDlg", OpenArgs:=Me.Name
Cancel = True
Else
If Err <> 0 Then
' unknown error
MsgBox Err.Description, vbExclamation, "Error"
Else
If Not IsNull(frm.txtLastName) Then
Me.Filter = "LastName= """ & frm.txtLastName
&
""""
Me.FilterOn = True
Else
Me.FilterOn = False
End If
DoCmd.Close acForm, frm.Name
End If
End If

End Sub

The button on the dialogue form has the following as its
Click
event
procedure:

Private Sub cmdOpenReport_Click()

Const REPORTCANCELLED = 2501

On Error Resume Next
' open report whose name was passed to form
DoCmd.OpenReport Me.OpenArgs, acViewPreview
Select Case Err.Number
Case 0
' no error so do nothing
Case REPORTCANCELLED
' anticipated error so do nothing
Case Else
'unknown error so inform user
MsgBox Err.Description
End Select

End Sub

Ken Sheridan
Stafford, England

:

I'm not sure how many experienced developers use code in
the
report
open
event to open a form for parameters. If you are using that
method,
start
with the form closed.

I don't ever use that solution. I always have a form open
where
the
user
can
enter criteria. Code will then open the report using
either a
"Where
Clause"
in the DoCmd.OpenReport as my sample code suggests or
having
a
reference
to
a control on a form hard-coded into the query.

 

[WMorsberger]

I have taken your advice and created a form that can be opened by the user.
The user will then choose the item from the drop down box and push a button
to run the report. I have taken your code and put on that on the button that
I have. This is the code that I used

Private Sub Command5_Click()
Dim strWhere As String
Dim strReport As String
strReport = "Reroutes No Attempts Made Report"
strWhere = "1=1 "
If Not IsNull(Me.Reroute_Contacts) Then
strWhere -strWhere & " And [Reroute Contacts] = """ &
Me.Reroute_Contacts & """ "
End If
DoCmd.OpenReport strReport, acViewPreview, , strWhere

End Sub

When I push the button I am getting an error message that there is a
Complile Error: Expected Sub, Function, Or property.

And the field that it is highlighting is .Reroute_Contacts located in the
7th line down. What do I have wrong? That is the name of the combo box on
the report that I am trying to run.

You need to try step 3 again but it doesn't happen automatically. You must
find the query in the database window and open it.

--
Duane Hookom
MS Access MVP

If I just open the form from the form menu I don't get anything after I
choose the value of the reroute contact - once I press the button the the
form closes and there is nothing there

What do you get if you:
- open the ParamForm
- select a value in RerouteContacts
- view the datasheet of your query


--
Duane Hookom
MS Access MVP

Here is what is in the SQL

SELECT [Reroutes Table].ID, [Reroutes Table].Date, [Reroutes
Table].[Reroute
Contacts], [Reroutes Table].[MCS Number], [Reroutes Table].[Count Of
Pieces],
[Reroutes Table].[1st Attempt Follow-up Date], [Reroutes Table].[2nd
Attempt
Follow-up Date], [Reroutes Table].[3rd Attempt Follow-up Date],
[Reroutes
Table].[Date Received Back]
FROM [Reroutes Table]
WHERE ((([Reroutes Table].[Reroute
Contacts])=[forms]![ParamForm]![RerouteContacts]) AND (([Reroutes
Table].[Date Received Back]) Is Null));


:

You need to open the query "Reroutes Not Received Back" in SQL view
and
copy
the sql and paste it into a reply.


--
Duane Hookom
MS Access MVP


I hope I am giving you the right information - I am new with access
so
I'm
still learning all the terms.


The record source for the report that i am running is a Query:
Reroutes
Not
Received Back

After the button is clicked on the form the report is this shown on
the
screen. Right now the information is blank. In the report screen
under
the
properties I have on the ON OPEN the following code:


Private Sub Report_Open(Cancel As Integer)
DoCmd.OpenForm "ParamForm", , , , , acDialog

End Sub


On the ON CLOSE this is what I have:

Private Sub Report_Close()
DoCmd.Close acForm, "ParamForm"
End Sub



:

And then what happens? I assume you select a value in the combo box
and
click a button. After the button is clicked, what happens? What is
the
SQL
view of the Record Source of your report?

--
Duane Hookom
MS Access MVP

message
The form is closed when i run the report - when i click on the
report
to
open
it - the form then comes up with the combo box so that I can
choose
what
to
run the report on.

:

Again, is ParamForm closed when you first attempt to open your
report?
Are
you using a command button to "close/hide" the ParamForm?

--
Duane Hookom
MS Access MVP

message
So, Is there something that I am doing wrong with what I have
that
would
keep
the information from populating on the report?

:

Thanks for the example Ken. I know it can be done and there
are
samples
like
this in MS stuff. It just hasn't made any sense to me to use
it
in
any
apps
I have written.

--
Duane Hookom
MS Access MVP

in
message
Duane:

I think it's a fairly common technique. I seem to recall
seeing
an
example
from MS somewhere which loops in the Open event procedure
until
the
form
is
opened, and the Solutions database includes a report where
the
Open
event
rather crudely prompts the user to open a dialogue form.
Here's
an
example
of mine from a report's Open event procedure which filters
a
report
based
on
an unrestricted query, but it could equally be done where
the
query
references the control as a parameter. The dialogue form
can
be
used
with
different reports as the report's name is passed to it:

Private Sub Report_Open(Cancel As Integer)

Const FORMNOTOPEN = 2450
Dim frm As Form

On Error Resume Next
Set frm = Forms!frmNameDlg
If Err = FORMNOTOPEN Then
' open dialogue form, passing report name to it
DoCmd.OpenForm "frmNameDlg", OpenArgs:=Me.Name
Cancel = True
Else
If Err <> 0 Then
' unknown error
MsgBox Err.Description, vbExclamation, "Error"
Else
If Not IsNull(frm.txtLastName) Then
Me.Filter = "LastName= """ & frm.txtLastName
&
""""
Me.FilterOn = True
Else
Me.FilterOn = False
End If
DoCmd.Close acForm, frm.Name
End If
End If

End Sub

The button on the dialogue form has the following as its
Click
event
procedure:

Private Sub cmdOpenReport_Click()

Const REPORTCANCELLED = 2501

On Error Resume Next
' open report whose name was passed to form
DoCmd.OpenReport Me.OpenArgs, acViewPreview
Select Case Err.Number
Case 0
' no error so do nothing
Case REPORTCANCELLED
' anticipated error so do nothing
Case Else
'unknown error so inform user
MsgBox Err.Description
End Select

End Sub

Ken Sheridan
Stafford, England

:

I'm not sure how many experienced developers use code in
the
report
open
event to open a form for parameters. If you are using that
method,
start
with the form closed.

I don't ever use that solution. I always have a form open
where
the
user
can
enter criteria. Code will then open the report using
either a
"Where
Clause"
in the DoCmd.OpenReport as my sample code suggests or
having
a
reference
to
a control on a form hard-coded into the query.

 

[Duane Hookom]

This line:
If Not IsNull(Me.Reroute_Contacts) Then
should include the name of the "drop down box".


--
Duane Hookom
MS Access MVP

I have taken your advice and created a form that can be opened by the user.
The user will then choose the item from the drop down box and push a
button
to run the report. I have taken your code and put on that on the button
that
I have. This is the code that I used

Private Sub Command5_Click()
Dim strWhere As String
Dim strReport As String
strReport = "Reroutes No Attempts Made Report"
strWhere = "1=1 "
If Not IsNull(Me.Reroute_Contacts) Then
strWhere -strWhere & " And [Reroute Contacts] = """ &
Me.Reroute_Contacts & """ "
End If
DoCmd.OpenReport strReport, acViewPreview, , strWhere

End Sub

When I push the button I am getting an error message that there is a
Complile Error: Expected Sub, Function, Or property.

And the field that it is highlighting is .Reroute_Contacts located in the
7th line down. What do I have wrong? That is the name of the combo box
on
the report that I am trying to run.

You need to try step 3 again but it doesn't happen automatically. You
must
find the query in the database window and open it.

--
Duane Hookom
MS Access MVP

If I just open the form from the form menu I don't get anything after I
choose the value of the reroute contact - once I press the button the
the
form closes and there is nothing there

:

What do you get if you:
- open the ParamForm
- select a value in RerouteContacts
- view the datasheet of your query


--
Duane Hookom
MS Access MVP

Here is what is in the SQL

SELECT [Reroutes Table].ID, [Reroutes Table].Date, [Reroutes
Table].[Reroute
Contacts], [Reroutes Table].[MCS Number], [Reroutes Table].[Count Of
Pieces],
[Reroutes Table].[1st Attempt Follow-up Date], [Reroutes Table].[2nd
Attempt
Follow-up Date], [Reroutes Table].[3rd Attempt Follow-up Date],
[Reroutes
Table].[Date Received Back]
FROM [Reroutes Table]
WHERE ((([Reroutes Table].[Reroute
Contacts])=[forms]![ParamForm]![RerouteContacts]) AND (([Reroutes
Table].[Date Received Back]) Is Null));


:

You need to open the query "Reroutes Not Received Back" in SQL view
and
copy
the sql and paste it into a reply.


--
Duane Hookom
MS Access MVP


message
I hope I am giving you the right information - I am new with
access
so
I'm
still learning all the terms.


The record source for the report that i am running is a Query:
Reroutes
Not
Received Back

After the button is clicked on the form the report is this shown
on
the
screen. Right now the information is blank. In the report
screen
under
the
properties I have on the ON OPEN the following code:


Private Sub Report_Open(Cancel As Integer)
DoCmd.OpenForm "ParamForm", , , , , acDialog

End Sub


On the ON CLOSE this is what I have:

Private Sub Report_Close()
DoCmd.Close acForm, "ParamForm"
End Sub



:

And then what happens? I assume you select a value in the combo
box
and
click a button. After the button is clicked, what happens? What
is
the
SQL
view of the Record Source of your report?

--
Duane Hookom
MS Access MVP

message
The form is closed when i run the report - when i click on the
report
to
open
it - the form then comes up with the combo box so that I can
choose
what
to
run the report on.

:

Again, is ParamForm closed when you first attempt to open
your
report?
Are
you using a command button to "close/hide" the ParamForm?

--
Duane Hookom
MS Access MVP

in
message
So, Is there something that I am doing wrong with what I
have
that
would
keep
the information from populating on the report?

:

Thanks for the example Ken. I know it can be done and
there
are
samples
like
this in MS stuff. It just hasn't made any sense to me to
use
it
in
any
apps
I have written.

--
Duane Hookom
MS Access MVP

"Ken Sheridan" <[email protected]>
wrote
in
message
Duane:

I think it's a fairly common technique. I seem to
recall
seeing
an
example
from MS somewhere which loops in the Open event
procedure
until
the
form
is
opened, and the Solutions database includes a report
where
the
Open
event
rather crudely prompts the user to open a dialogue form.
Here's
an
example
of mine from a report's Open event procedure which
filters
a
report
based
on
an unrestricted query, but it could equally be done
where
the
query
references the control as a parameter. The dialogue
form
can
be
used
with
different reports as the report's name is passed to it:

Private Sub Report_Open(Cancel As Integer)

Const FORMNOTOPEN = 2450
Dim frm As Form

On Error Resume Next
Set frm = Forms!frmNameDlg
If Err = FORMNOTOPEN Then
' open dialogue form, passing report name to it
DoCmd.OpenForm "frmNameDlg", OpenArgs:=Me.Name
Cancel = True
Else
If Err <> 0 Then
' unknown error
MsgBox Err.Description, vbExclamation,
"Error"
Else
If Not IsNull(frm.txtLastName) Then
Me.Filter = "LastName= """ &
frm.txtLastName
&
""""
Me.FilterOn = True
Else
Me.FilterOn = False
End If
DoCmd.Close acForm, frm.Name
End If
End If

End Sub

The button on the dialogue form has the following as its
Click
event
procedure:

Private Sub cmdOpenReport_Click()

Const REPORTCANCELLED = 2501

On Error Resume Next
' open report whose name was passed to form
DoCmd.OpenReport Me.OpenArgs, acViewPreview
Select Case Err.Number
Case 0
' no error so do nothing
Case REPORTCANCELLED
' anticipated error so do nothing
Case Else
'unknown error so inform user
MsgBox Err.Description
End Select

End Sub

Ken Sheridan
Stafford, England

:

I'm not sure how many experienced developers use code
in
the
report
open
event to open a form for parameters. If you are using
that
method,
start
with the form closed.

I don't ever use that solution. I always have a form
open
where
the
user
can
enter criteria. Code will then open the report using
either a
"Where
Clause"
in the DoCmd.OpenReport as my sample code suggests or
having
a
reference
to
a control on a form hard-coded into the query.