Generate Report with a Form Filter.

G

Guest

I am trying to call a report using the OpenReport action. I am using a
dialog form to call the report, this dialog form is opened from my main
swtichboard.

The problem I am having is that my report's design is based on a query whose
parameters are on controls in a form (EMpID, StartDate, and EndDate).

How do I use the OpenReport action to call the report and at the same time
implement my parameters. My problem right now is that, once I call the
report from my dialog box, it automatically prints out all the data, it is
skipping the parameters already set.

I have read suggestions of using the Where command/ filter declaratione.g.

DoCmd.OpenReport "ReportName", , acView - This is where I get Stuck!
 
S

Steve Schapel

Olu,

One option is to write the criteria directly into the query that the
report is based on, using syntax such as...
[Forms]![NameOfForm]![EmpID]

Another, as you suggest, is to use the Where Condition argument. You
are using a VBA procedure, apparently, and not a macro.
DoCmd.OpenReport "ReportName", , , , "[EmpID]=" & Me.EmpID & " And
[YourDate] Between " & CLng(Me.StartDate) & " And " & CLng(Me.EndDate)
.... or some such.
 
G

Guest

What if EMpID is not numerical? Because ACcess is giving me the following
error message: Method or Data object not found, with the EmpID highlighted.
Could it be that that VB is assuming the field is numerical?

Here is my code sample:
Private Sub Toggle3_MouseDown(Button As Integer, Shift As Integer, X As
Single, Y As Single)
DoCmd.OpenReport "Trainging Summary by Employee ID", , , , "[EmpID]=" &
Me.EmpID & " And "
Between " & CLng(Me.StartDate) & " And " & CLng(Me.EndDate)"
End Sub

I would like to know what I am doing wrong, as oppose to getting the
answers, this way I don't have to post repetitive questions.

Steve Schapel said:
Olu,

One option is to write the criteria directly into the query that the
report is based on, using syntax such as...
[Forms]![NameOfForm]![EmpID]

Another, as you suggest, is to use the Where Condition argument. You
are using a VBA procedure, apparently, and not a macro.
DoCmd.OpenReport "ReportName", , , , "[EmpID]=" & Me.EmpID & " And
[YourDate] Between " & CLng(Me.StartDate) & " And " & CLng(Me.EndDate)
.... or some such.

--
Steve Schapel, Microsoft Access MVP

Olu said:
I am trying to call a report using the OpenReport action. I am using a
dialog form to call the report, this dialog form is opened from my main
swtichboard.

The problem I am having is that my report's design is based on a query whose
parameters are on controls in a form (EMpID, StartDate, and EndDate).

How do I use the OpenReport action to call the report and at the same time
implement my parameters. My problem right now is that, once I call the
report from my dialog box, it automatically prints out all the data, it is
skipping the parameters already set.

I have read suggestions of using the Where command/ filter declaratione.g.

DoCmd.OpenReport "ReportName", , acView - This is where I get Stuck!
Click to expand...
Click to expand...
 
S

Steve Schapel

Olu,

First of all, I would definitely not use a MouseDown event to print a
report. The most common events to trigger this type of code would be
the Click event of a command button, or the After Update event of
another control. What is Toggle3's role in the universe?

Yes, you have correctly diagnosed that different syntax is required if
EmpID is a text data type, i.e. it needs text delimiters around it.
Also, the " at the end of that link is not correct. Also, the ""s
around the word Between are not correct. Also, you have not put the
name of your date field. In other words, your code departs
significantly from the example I gave you. The assumption I am making
is that you want the data printed on the report to be reseticted to only
those records where the value of the EmpID field matches the EmpID of
the current record on the form, and also where the value of a date field
(I don't know the name of this field at this stage) on your report falls
between the dates entered in the StartDate and EndDate controls on the
form. So...
DoCmd.OpenReport "Trainging Summary by Employee ID", , , , "[EmpID]='"
& Me.EmpID & "' And [YourDateField] Between " & CLng(Me.StartDate) & "
And " & CLng(Me.EndDate)
 
D

Douglas J. Steele

If EmpID is text, you need to put quotes:

"[EmpID]='" & Me.EmpID & "'"

Exagerated for clarity, that's

"[EmpID]= ' " & Me.EmpID & " ' "

However, your problem is also because you haven't included the name of your
date field between the key words And and Between.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Olu Solaru said:
What if EMpID is not numerical? Because ACcess is giving me the following
error message: Method or Data object not found, with the EmpID
highlighted.
Could it be that that VB is assuming the field is numerical?

Here is my code sample:
Private Sub Toggle3_MouseDown(Button As Integer, Shift As Integer, X As
Single, Y As Single)
DoCmd.OpenReport "Trainging Summary by Employee ID", , , , "[EmpID]=" &
Me.EmpID & " And "
Between " & CLng(Me.StartDate) & " And " & CLng(Me.EndDate)"
End Sub

I would like to know what I am doing wrong, as oppose to getting the
answers, this way I don't have to post repetitive questions.

Steve Schapel said:
Olu,

One option is to write the criteria directly into the query that the
report is based on, using syntax such as...
[Forms]![NameOfForm]![EmpID]

Another, as you suggest, is to use the Where Condition argument. You
are using a VBA procedure, apparently, and not a macro.
DoCmd.OpenReport "ReportName", , , , "[EmpID]=" & Me.EmpID & " And
[YourDate] Between " & CLng(Me.StartDate) & " And " & CLng(Me.EndDate)
.... or some such.

--
Steve Schapel, Microsoft Access MVP

Olu said:
I am trying to call a report using the OpenReport action. I am using a
dialog form to call the report, this dialog form is opened from my main
swtichboard.

The problem I am having is that my report's design is based on a query
whose
parameters are on controls in a form (EMpID, StartDate, and EndDate).

How do I use the OpenReport action to call the report and at the same
time
implement my parameters. My problem right now is that, once I call
the
report from my dialog box, it automatically prints out all the data, it
is
skipping the parameters already set.

I have read suggestions of using the Where command/ filter
declaratione.g.

DoCmd.OpenReport "ReportName", , acView - This is where I get Stuck!
Click to expand...
Click to expand...
Click to expand...
 
S

Steve Schapel

Olu,

And besides all of the previously mentioned points, I would also suggest
you check that the name of the report has been correctly entered as
"Trainging Summary by Employee ID", especially the spelling of "Trainging".
 

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

Similar Threads

Prompting for Criteria already defined in Report/Query. 1
Open date form before printing current record 1
Macro for form for collecting report parameters 21
Macro Expressions for Report Selection Form 5
WHERE CONDITION IN REPORT 3
Macro Condition for Option Group & Combo Box 4
A Report, based on query, called from macro is prompting for param 1
SendObject - Two Email pages 4

Top