W
WMorsberger
Reroute Contacts is the name of the drop down box
Duane Hookom said:This line:
If Not IsNull(Me.Reroute_Contacts) Then
should include the name of the "drop down box".
--
Duane Hookom
MS Access MVP
WMorsberger said: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.
Duane Hookom said: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