Pass criteria to open report for selected ClientID

  • Thread starter Thread starter Mishanya
  • Start date Start date
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?
 
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
 
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
 
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...
 
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...
 
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...
 
Back
Top