Pass criteria to open report for selected ClientID

M

Mishanya

I have Mainform with cboSelectClientID and MyReport report based on query
wich includes ClientID field.
On the Mainform I've put command button and programmed it on click event as
follows:

Dim strWhere As String

strWhere = "[ClientID]=" & Me![SelectClientID]
DoCmd.OpenReport "MyReport", acPreview, strWhere

If I understand right, the button is supposed to open the MyReport with
selected in the cboSelectClientID client-recordset while the Filter property
of the report has to change to [ClientID] = ClientID Autonumber selected in
the cboSelectClientID.
It does opens the report - but no filtering happens. Moreover, somehow it
opens the report even with cboSelectClientID unselected (Null), although I'm
supposed to get error msgbox requiring to select ClientID first.
What should I check to workaround the task?
 
M

mscertified

Where clause is next parameter so you need an extra comma before it:
DoCmd.OpenReport "MyReport", acPreview, ,strWhere
Also make sure ClientID column is included in the data source for the report

-Dorian
 
J

John Spencer

I believe you are missing a comma in the OpenReport call

strWhere = "[ClientID]=" & Me![SelectClientID]
DoCmd.OpenReport "MyReport", acPreview, , strWhere

I don't see why you should get an error is cboSelectClientID is null.

Perhaps what you want is

If Len(Me![SelectClientID] & vbnullstring) = 0 then
MsgBox "Select a client from ??????"
Else
strWhere = "[ClientID]=" & Me![SelectClientID]
DoCmd.OpenReport "MyReport", acPreview, , strWhere

End if

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
 
M

Mishanya

Dorian Thank You very much. It's solved the issue.
For my further knowledge (as I'm rookie):

1) What is the meaning of "Where clause is next parameter..."
2) When I build command button to open another form with wizard, it puts the
code as follows:

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "MyForm"
stLinkCriteria = "[ClientID]=" & Me![SelectClientID]
DoCmd.OpenForm stDocName, , , stLinkCriteria

I've tried to implement the same for MyReport (only with DoCmd.OpenReport
"MyReport", acPreview...) but it did not deliver the filter.

In this code even more extra commas are used. What's the difference?



mscertified said:
Where clause is next parameter so you need an extra comma before it:
DoCmd.OpenReport "MyReport", acPreview, ,strWhere
Also make sure ClientID column is included in the data source for the report

-Dorian

Mishanya said:
I have Mainform with cboSelectClientID and MyReport report based on query
wich includes ClientID field.
On the Mainform I've put command button and programmed it on click event as
follows:

Dim strWhere As String

strWhere = "[ClientID]=" & Me![SelectClientID]
DoCmd.OpenReport "MyReport", acPreview, strWhere

If I understand right, the button is supposed to open the MyReport with
selected in the cboSelectClientID client-recordset while the Filter property
of the report has to change to [ClientID] = ClientID Autonumber selected in
the cboSelectClientID.
It does opens the report - but no filtering happens. Moreover, somehow it
opens the report even with cboSelectClientID unselected (Null), although I'm
supposed to get error msgbox requiring to select ClientID first.
What should I check to workaround the task?
Click to expand...
Click to expand...
 
M

Mishanya

Thank you, comma it was.
Untill the ClientID is not selected in cboSelectClientID - it would not open
the report - but it does make sense to me - the code is supposed to deliver
the criteria. If the criteria is not selected - it asks to select it,
otherwise the report is not loaded.

John Spencer said:
I believe you are missing a comma in the OpenReport call

strWhere = "[ClientID]=" & Me![SelectClientID]
DoCmd.OpenReport "MyReport", acPreview, , strWhere

I don't see why you should get an error is cboSelectClientID is null.

Perhaps what you want is

If Len(Me![SelectClientID] & vbnullstring) = 0 then
MsgBox "Select a client from ??????"
Else
strWhere = "[ClientID]=" & Me![SelectClientID]
DoCmd.OpenReport "MyReport", acPreview, , strWhere

End if

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
I have Mainform with cboSelectClientID and MyReport report based on query
wich includes ClientID field.
On the Mainform I've put command button and programmed it on click event as
follows:

Dim strWhere As String

strWhere = "[ClientID]=" & Me![SelectClientID]
DoCmd.OpenReport "MyReport", acPreview, strWhere

If I understand right, the button is supposed to open the MyReport with
selected in the cboSelectClientID client-recordset while the Filter property
of the report has to change to [ClientID] = ClientID Autonumber selected in
the cboSelectClientID.
It does opens the report - but no filtering happens. Moreover, somehow it
opens the report even with cboSelectClientID unselected (Null), although I'm
supposed to get error msgbox requiring to select ClientID first.
What should I check to workaround the task?
Click to expand...
Click to expand...
 
D

Dorian

Look in Access HELP screens for full explanation of format of OpenReport
method and its parameters or arguments whetever Microsoft calls them.

by 'next parameter' I just meant the next one along as in
a,b,c,d - c being the next parameter to b
if you omit b you need:
a,,c,d

I can't speak about the wizards - they don't always do what you want. I
rarely use them.

-Dorian

Mishanya said:
Dorian Thank You very much. It's solved the issue.
For my further knowledge (as I'm rookie):

1) What is the meaning of "Where clause is next parameter..."
2) When I build command button to open another form with wizard, it puts the
code as follows:

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "MyForm"
stLinkCriteria = "[ClientID]=" & Me![SelectClientID]
DoCmd.OpenForm stDocName, , , stLinkCriteria

I've tried to implement the same for MyReport (only with DoCmd.OpenReport
"MyReport", acPreview...) but it did not deliver the filter.

In this code even more extra commas are used. What's the difference?



mscertified said:
Where clause is next parameter so you need an extra comma before it:
DoCmd.OpenReport "MyReport", acPreview, ,strWhere
Also make sure ClientID column is included in the data source for the report

-Dorian

Mishanya said:
I have Mainform with cboSelectClientID and MyReport report based on query
wich includes ClientID field.
On the Mainform I've put command button and programmed it on click event as
follows:

Dim strWhere As String

strWhere = "[ClientID]=" & Me![SelectClientID]
DoCmd.OpenReport "MyReport", acPreview, strWhere

If I understand right, the button is supposed to open the MyReport with
selected in the cboSelectClientID client-recordset while the Filter property
of the report has to change to [ClientID] = ClientID Autonumber selected in
the cboSelectClientID.
It does opens the report - but no filtering happens. Moreover, somehow it
opens the report even with cboSelectClientID unselected (Null), although I'm
supposed to get error msgbox requiring to select ClientID first.
What should I check to workaround the task?
Click to expand...
Click to expand...
Click to expand...
 

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

Transferring parameters 13
Export to Snapshot with parameters 4
Form to open report based on several combo boxes 4
Report Where Condition 3
Trying to Open a Report with a Where Condition... 2
Report VB Code 1
Trying to use the filter on the form to filter report 3
Pass x number of parameters to a report from a form 2

Top