Option Group as a Filter for a Report

P

PBrown

Here is some background...
1) Query has a "Deleted" field that can be checked when a
project has been removed from the planning
2) There are two forms that will be used to allow users
to pick which area they want to view.
A) Form A is for managers only and can only be reached
via passwords
B) Form B is for use by anyone.
3) The same report runs, despite which form the user
uses. The only difference is the choices of titles
(Another option group already in play here)
4) Another option group has been set up with (3) radio
buttons 1 - Current 2 - Deleted 3 - All

Question:
Is there away to have the query or report use #4's result
to run. I.E. if the user clicks on "Deleted" only
deleted items appear, etc.
The problem is that I do not believe the query can be
tied only to this form. Because as mentioned earlier,
there are (2) forms that use the query for its report
(s). Someone was telling me about, Via the
Docmd.OpenReport method (you used to launch your report),
set the filter (or Where) argument to what the user
selected. However, I have looked at "Help" and many on-
line sources, but am having trouble finding references or
examples that involve using an item selected on a form as
a filter. Any suggestions?

Thank you in advance for any and all help,

PBrown
 
M

Marshall Barton

PBrown said:
Here is some background...
1) Query has a "Deleted" field that can be checked when a
project has been removed from the planning
2) There are two forms that will be used to allow users
to pick which area they want to view.
A) Form A is for managers only and can only be reached
via passwords
B) Form B is for use by anyone.
3) The same report runs, despite which form the user
uses. The only difference is the choices of titles
(Another option group already in play here)
4) Another option group has been set up with (3) radio
buttons 1 - Current 2 - Deleted 3 - All

Question:
Is there away to have the query or report use #4's result
to run. I.E. if the user clicks on "Deleted" only
deleted items appear, etc.
The problem is that I do not believe the query can be
tied only to this form. Because as mentioned earlier,
there are (2) forms that use the query for its report
(s). Someone was telling me about, Via the
Docmd.OpenReport method (you used to launch your report),
set the filter (or Where) argument to what the user
selected. However, I have looked at "Help" and many on-
line sources, but am having trouble finding references or
examples that involve using an item selected on a form as
a filter.
Click to expand...

Using the OpenReport method's WhereCondition argument will
take care your question. What you need to do is use code to
construct a Where clause without the word "Where". E.g.

Dim strWhere As String
Select Case Me.optiongroupname
Case 1
strWhere = "[Deleted] = False"
Case 2
strWhere = "[Deleted] = True"
Case 3
strWhere = "" ' nothing needed to get all records
End Select
DoCmd OpenReport "the report", acViewPreview, _
WhereCondition:= strWhere

I didn't follow the rest of what you are trying to do for
"titles", so I don't know if this is all you need or not.
 
P

PBrown

-----Original Message-----
PBrown said:
Here is some background...
1) Query has a "Deleted" field that can be checked when a
project has been removed from the planning
2) There are two forms that will be used to allow users
to pick which area they want to view.
A) Form A is for managers only and can only be reached
via passwords
B) Form B is for use by anyone.
3) The same report runs, despite which form the user
uses. The only difference is the choices of titles
(Another option group already in play here)
4) Another option group has been set up with (3) radio
buttons 1 - Current 2 - Deleted 3 - All

Question:
Is there away to have the query or report use #4's result
to run. I.E. if the user clicks on "Deleted" only
deleted items appear, etc.
The problem is that I do not believe the query can be
tied only to this form. Because as mentioned earlier,
there are (2) forms that use the query for its report
(s). Someone was telling me about, Via the
Docmd.OpenReport method (you used to launch your report),
set the filter (or Where) argument to what the user
selected. However, I have looked at "Help" and many on-
line sources, but am having trouble finding references or
examples that involve using an item selected on a form as
a filter.
Click to expand...

Using the OpenReport method's WhereCondition argument will
take care your question. What you need to do is use code to
construct a Where clause without the word "Where". E.g.

Dim strWhere As String
Select Case Me.optiongroupname
Case 1
strWhere = "[Deleted] = False"
Case 2
strWhere = "[Deleted] = True"
Case 3
strWhere = "" ' nothing needed to get all records
End Select
DoCmd OpenReport "the report", acViewPreview, _
WhereCondition:= strWhere

I didn't follow the rest of what you are trying to do for
"titles", so I don't know if this is all you need or not.

--
Marsh
MVP [MS Access]
.
Thank you. Now, I just have one question... Where do
Click to expand...
you put this code? I have never done this or ever dealt
with the "OpenReport Where Condition" and the office
assistant was not able to provide any help. Perhaps I
need to use a different set of key words? Could you
please assist?

Thank you again for your help and assistance,

PBrown
 
M

Marshall Barton

-----Original Message-----
Marshall said:
Using the OpenReport method's WhereCondition argument will
take care your question. What you need to do is use code to
construct a Where clause without the word "Where". E.g.

Dim strWhere As String
Select Case Me.optiongroupname
Case 1
strWhere = "[Deleted] = False"
Case 2
strWhere = "[Deleted] = True"
Case 3
strWhere = "" ' nothing needed to get all records
End Select
DoCmd OpenReport "the report", acViewPreview, _
WhereCondition:= strWhere

I didn't follow the rest of what you are trying to do for
"titles", so I don't know if this is all you need or not.

.
Click to expand...
PBrown said:
Thank you. Now, I just have one question... Where do
Click to expand...
you put this code? I have never done this or ever dealt
with the "OpenReport Where Condition" and the office
assistant was not able to provide any help. Perhaps I
need to use a different set of key words?
Click to expand...

I expect your forms have a command button for users to click
after the desired options have been selected and they're
ready to print the report. The command button wizard will
generate most of the code in the button's click event
procedure for you. All you have to do is locate the code
and modify it to something like what I posted.

If you're not already familiar with it, you can get to an
event procedure by locating the event property in the
button's Property Window (View menu) and clicking on the
little [...] button over at the right edge of the window.
 
P

PBrown

-----Original Message-----
-----Original Message-----
PBrown wrote:

Here is some background...
1) Query has a "Deleted" field that can be checked
Click to expand...
when
a
project has been removed from the planning
2) There are two forms that will be used to allow users
to pick which area they want to view.
A) Form A is for managers only and can only be reached
via passwords
B) Form B is for use by anyone.
3) The same report runs, despite which form the user
uses. The only difference is the choices of titles
(Another option group already in play here)
4) Another option group has been set up with (3) radio
buttons 1 - Current 2 - Deleted 3 - All

Question:
Is there away to have the query or report use #4's result
to run. I.E. if the user clicks on "Deleted" only
deleted items appear, etc.
The problem is that I do not believe the query can be
tied only to this form. Because as mentioned earlier,
there are (2) forms that use the query for its report
(s). Someone was telling me about, Via the
Docmd.OpenReport method (you used to launch your report),
set the filter (or Where) argument to what the user
selected. However, I have looked at "Help" and many on-
line sources, but am having trouble finding references or
examples that involve using an item selected on a form
as a filter.
Click to expand...
Marshall said:
Using the OpenReport method's WhereCondition argument will
take care your question. What you need to do is use code to
construct a Where clause without the word "Where". E.g.

Dim strWhere As String
Select Case Me.optiongroupname
Case 1
strWhere = "[Deleted] = False"
Case 2
strWhere = "[Deleted] = True"
Case 3
strWhere = "" ' nothing needed to get all records
End Select
DoCmd OpenReport "the report", acViewPreview, _
WhereCondition:= strWhere

I didn't follow the rest of what you are trying to do for
"titles", so I don't know if this is all you need or not.

.
Click to expand...
PBrown said:
Thank you. Now, I just have one question... Where do
Click to expand...
you put this code? I have never done this or ever dealt
with the "OpenReport Where Condition" and the office
assistant was not able to provide any help. Perhaps I
need to use a different set of key words?
Click to expand...

I expect your forms have a command button for users to click
after the desired options have been selected and they're
ready to print the report. The command button wizard will
generate most of the code in the button's click event
procedure for you. All you have to do is locate the code
and modify it to something like what I posted.

If you're not already familiar with it, you can get to an
event procedure by locating the event property in the
button's Property Window (View menu) and clicking on the
little [...] button over at the right edge of the window.

--
Marsh
MVP [MS Access]
.
I have opened the form in design view and went to the
Click to expand...
OnClick(). Given there is already code in the event, I
tried to incorportate the posted code. The optiongroup
name for the Active, Deleted, All is "Criteria". The
radio buttons have been named Such: Active, Deleted, All
However, a Compile error: Expected expression appears
after the following line:
WhereCondition:= strWhere
The entire code has been copied below so that it may be
easier to assist me figure where it is going wrong.
Also, excuse me for not mentioning earlier, but this is
Access97.

Private Sub Command3_Click()
Dim intCount As Integer
Dim strWhere As String

Dim isgm As String


intCount = 1


gstrTitle = "Preliminary Draft"

Do While intCount <= 2

If [ReportOptions] = 0 Then
MsgBox "No Report Selected for Altima Trim &
Chassis"
Exit Sub

Else


If (ReportOptions = 6) And (IsNull(CustomT))
Then
MsgBox "you must enter a title"
CustomT.SetFocus
Exit Sub
End If
End If
gstrTitle = Choose(ReportOptions, "Director
Review", "Director / Engineering Director
Review", "Director / VP Review", "VP Review", "SR VP
Review", [CustomT])
'DoCmd.Close
Select Case Me.Criteria
Case 1
strWhere = "[Deleted] = False"
Case 2
strWhere = "[Deleted] = True"
Case 3
strWhere = "" ' nothing needed to get
all records

End Select


DoCmd.OpenReport "Altima Budget Reports",
acViewPreview_
WhereCondition:= strWhere
If (ReportOptions = 6) Then CustomT = Null
[ReportOptions] = 0
Exit Sub



Loop



End Sub
 
P

PBrown

-----Original Message-----
-----Original Message-----
-----Original Message-----
PBrown wrote:

Here is some background...
1) Query has a "Deleted" field that can be checked when
a
project has been removed from the planning
2) There are two forms that will be used to allow users
to pick which area they want to view.
A) Form A is for managers only and can only be
reached
via passwords
B) Form B is for use by anyone.
3) The same report runs, despite which form the user
uses. The only difference is the choices of titles
(Another option group already in play here)
4) Another option group has been set up with (3) radio
buttons 1 - Current 2 - Deleted 3 - All

Question:
Is there away to have the query or report use #4's
result
to run. I.E. if the user clicks on "Deleted" only
deleted items appear, etc.
The problem is that I do not believe the query can be
tied only to this form. Because as mentioned earlier,
there are (2) forms that use the query for its report
(s). Someone was telling me about, Via the
Docmd.OpenReport method (you used to launch your
report),
set the filter (or Where) argument to what the user
selected. However, I have looked at "Help" and many on-
line sources, but am having trouble finding references
or
examples that involve using an item selected on a form
as a filter.

Marshall Barton wrote:
Using the OpenReport method's WhereCondition argument
will
take care your question. What you need to do is use
code to
construct a Where clause without the word "Where". E.g.

Dim strWhere As String
Select Case Me.optiongroupname
Case 1
strWhere = "[Deleted] = False"
Case 2
strWhere = "[Deleted] = True"
Case 3
strWhere = "" ' nothing needed to get all
records
End Select
DoCmd OpenReport "the report", acViewPreview, _
WhereCondition:= strWhere

I didn't follow the rest of what you are trying to do for
"titles", so I don't know if this is all you need or not.

.
Click to expand...
PBrown said:
Thank you. Now, I just have one question... Where do
you put this code? I have never done this or ever dealt
with the "OpenReport Where Condition" and the office
assistant was not able to provide any help. Perhaps I
need to use a different set of key words?
Click to expand...

I expect your forms have a command button for users to click
after the desired options have been selected and they're
ready to print the report. The command button wizard will
generate most of the code in the button's click event
procedure for you. All you have to do is locate the code
and modify it to something like what I posted.

If you're not already familiar with it, you can get to an
event procedure by locating the event property in the
button's Property Window (View menu) and clicking on the
little [...] button over at the right edge of the window.

--
Marsh
MVP [MS Access]
.
I have opened the form in design view and went to the
Click to expand...
OnClick(). Given there is already code in the event, I
tried to incorportate the posted code. The optiongroup
name for the Active, Deleted, All is "Criteria". The
radio buttons have been named Such: Active, Deleted, All
However, a Compile error: Expected expression appears
after the following line:
WhereCondition:= strWhere
The entire code has been copied below so that it may be
easier to assist me figure where it is going wrong.
Also, excuse me for not mentioning earlier, but this is
Access97.

Private Sub Command3_Click()
Dim intCount As Integer
Dim strWhere As String

Dim isgm As String


intCount = 1


gstrTitle = "Preliminary Draft"

Do While intCount <= 2

If [ReportOptions] = 0 Then
MsgBox "No Report Selected for Altima Trim &
Chassis"
Exit Sub

Else


If (ReportOptions = 6) And (IsNull(CustomT))
Then
MsgBox "you must enter a title"
CustomT.SetFocus
Exit Sub
End If
End If
gstrTitle = Choose(ReportOptions, "Director
Review", "Director / Engineering Director
Review", "Director / VP Review", "VP Review", "SR VP
Review", [CustomT])
'DoCmd.Close
Select Case Me.Criteria
Case 1
strWhere = "[Deleted] = False"
Case 2
strWhere = "[Deleted] = True"
Case 3
strWhere = "" ' nothing needed to get
all records

End Select


DoCmd.OpenReport "Altima Budget Reports",
acViewPreview_
WhereCondition:= strWhere
If (ReportOptions = 6) Then CustomT = Null
[ReportOptions] = 0
Exit Sub



Loop



End Sub

.
I think the answer has been found...
Click to expand...
Started With:

DoCmd.OpenReport "Altima Budget Reports", >acViewPreview_
WhereCondition:= strWhere
Click to expand...

Changed To:
DoCmd.OpenReport "Altima Budget Reports", acViewPreview, ,
strWhere

Now it is working.... I should have known that is what you
ment.... Slight morning drag right now, but the old mind
is starting to wake up.

Thank you for your help!!

PBrown
 
M

Marshall Barton

PBrown said:
DoCmd.OpenReport "Altima Budget Reports",
acViewPreview_
WhereCondition:= strWhere
Click to expand...

You left out the comma and space after acViewPreview.
 
M

Marshall Barton

PBrown said:
Started With:

DoCmd.OpenReport "Altima Budget Reports", >acViewPreview_
Click to expand...

That should be:

DoCmd.OpenReport "Altima Budget Reports", acViewPreview, _
WhereCondition:= strWhere

Note the comma space after acViewPreview

Changed To:
DoCmd.OpenReport "Altima Budget Reports", acViewPreview, ,
strWhere
Click to expand...

Yes, that is the correct syntax for positional arguments.
The other one uses a Named Argument instead of worrying
about how many commas are needed.

Now it is working.... I should have known that is what you
ment.... Slight morning drag right now, but the old mind
is starting to wake up.
Click to expand...

I know what you mean.

Thank you for your help!!
Click to expand...

You're welcome, glad you got it sorted out.
 

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

Report Group Footer DLookUp 0
Trying to create a report that will list unmatched records by Ticket Primary Key 0
Getting Radio Buttons to work, that are inserted via AutoText 0
HOW CAN I COUNT ALL THE GROUPS IN A REPORT 0
Dynamic Group Report 2
Option Group data display 10
Access Report OnFormat Hangs with all users except Admins group 6
Filter Report Date in VBA 2

Top