|
|
Our Hot Pick: Rising Antivirus 2006 - Certified by TUV & Checkmark! Get 10% discount by entering this coupon code: ONDISCOUNT10
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!
|
|
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 Solaru wrote:
[Quoted Text] > 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!
|
|
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" wrote:
[Quoted Text] > 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 Solaru wrote:
> > 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!
>
|
|
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)
--
Steve Schapel, Microsoft Access MVP
Olu Solaru wrote:
[Quoted Text] > 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.
|
|
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
http://I.Am/DougSteele
(no private e-mails, please)
"Olu Solaru" <OluSolaru[ at ]discussions.microsoft.com> wrote in message
news:191CD967-440F-4CAE-8638-9F7F4785D656[ at ]microsoft.com...
[Quoted Text] > 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" wrote:
>
>> 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 Solaru wrote:
>> > 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!
>>
|
|
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".
--
Steve Schapel, Microsoft Access MVP
Steve Schapel wrote:
[Quoted Text] > 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)
>
|
|
|