popup form to filter and return results in report

[kniedens]

Hi All,
I have a simple question, but can't seem to figure it out on my own.

I have a report, and upon opening it I want the user to be able to select
from a list a criteria. Based on criteria selected, (probably on a dialog box?
), a filter is automatically applied, and the report is run.

So far I have been able to get a form (dialog box) to open whenever the
report is selected, but I can't get it to set the value or return to
filtering and running the report. Maybe I'm going about this in the wrong way.
..

Any help is greatly appreciated!

 

[Duane Hookom]

It would help if we could see your code. Did you open the form in dialog
mode?

 

[kniedens]

It would help if we could see your code. Did you open the form in dialog
mode?

Hi, thanks for the reply; yes, I opened the form in dialog mode, but am
pretty new to this, so I probably am doing a lot of things wrong.

 

[Duane Hookom]

We don't know anything about how your solution is built. It's like you are
telling us "I get in my car and it doesn't go where I want it to go".
"It would help if we could see your code."

I believe most old developers open a form first and allow users to enter
criteria values in various controls. Then we open the report based on the
criteria. I think attempting to open the report first and then a dialog form
is putting the cart in front of the horse.

 

[kniedens]

Thanks again for your help and patience; all I have so far is the report, and
I have experimented with forms and macros (with no success due to my lack of
experience I'm sure). I just made a simple form (dialog box) that I was
trying to use to make this whole thing work based on some vague/confusing
instructions from a book someone had at work. I can delete the form easy
enough--I like your idea of starting from the form and ending up in the
report, so maybe we could start fresh from there.

SO.....I guess my real question would be, how do I make a form that offers a
list (such as, "select category"). When the user clicks on one category
listed (then clicks the ok button), a filter is applied to the related report,
and the filtered report runs. For illustrative purposes, let's say we have a
report for menu items; each item has a category listed as breakfast, lunch or
dinner. When the user is in the form, he clicks on lunch, and then clicks the
ok button, the report subsequently runs showing items with the category
"lunch."


Is there another way to accomplish this? In the query you can just put [enter
category] in the criteria box, and when you run the report the user is
prompted to enter a category...I just want those categories listed so the
user can select something instead of having to know what to enter (I have
about 20 categories in a production line)....I hope this question makes more
sense...

 

[Duane Hookom]

Assuming you have a combo box [cboCategory] on your form and you want to
open a report [rptMyReport] with the text field [Category] in the report's
record source.

Make sure you have the above set up on your form. Then use the command
button wizard to add a button to open the report. Then view the code created
by the wizard and change it like:

Dim strWhere as String
Dim stDocument as String
stDocument = "rptMyReport"
strWhere = "1=1 "
If Not IsNull(Me.cboCategory) Then
strWhere = strWhere & " And [Category]=""" & _
Me.cboCategory & """"
End If
DoCmd.OpenReport stDocument, acPreview, , strWhere


--
Duane Hookom
MS Access MVP

Thanks again for your help and patience; all I have so far is the report,
and
I have experimented with forms and macros (with no success due to my lack
of
experience I'm sure). I just made a simple form (dialog box) that I was
trying to use to make this whole thing work based on some vague/confusing
instructions from a book someone had at work. I can delete the form easy
enough--I like your idea of starting from the form and ending up in the
report, so maybe we could start fresh from there.

SO.....I guess my real question would be, how do I make a form that offers
a
list (such as, "select category"). When the user clicks on one category
listed (then clicks the ok button), a filter is applied to the related
report,
and the filtered report runs. For illustrative purposes, let's say we have
a
report for menu items; each item has a category listed as breakfast, lunch
or
dinner. When the user is in the form, he clicks on lunch, and then clicks
the
ok button, the report subsequently runs showing items with the category
"lunch."


Is there another way to accomplish this? In the query you can just put
[enter
category] in the criteria box, and when you run the report the user is
prompted to enter a category...I just want those categories listed so the
user can select something instead of having to know what to enter (I have
about 20 categories in a production line)....I hope this question makes
more
sense...

We don't know anything about how your solution is built. It's like you are
telling us "I get in my car and it doesn't go where I want it to go".
"It would help if we could see your code."

I believe most old developers open a form first and allow users to enter
criteria values in various controls. Then we open the report based on the
criteria. I think attempting to open the report first and then a dialog
form
is putting the cart in front of the horse.

 

[kniedens via AccessMonster.com]

Thanks so much! I'll try it first thing tomorrow morning at work...wish I had
access installed at home and I would just do a test drive now...). Seems to
make sense anyway, so hopefully I won't mess it up....

Thanks for your help and sticking with me through this!
Kari

 

[kniedens via AccessMonster.com]

Okay, I got in to change the code, but an error message come up at this
point:
strWhere=strWhere&"And[Category]=""&_
it says "compile error" and "expected end of statement"

Once again...any help is appreciated!

Assuming you have a combo box [cboCategory] on your form and you want to
open a report [rptMyReport] with the text field [Category] in the report's
record source.

Make sure you have the above set up on your form. Then use the command
button wizard to add a button to open the report. Then view the code created
by the wizard and change it like:

Dim strWhere as String
Dim stDocument as String
stDocument = "rptMyReport"
strWhere = "1=1 "
If Not IsNull(Me.cboCategory) Then
strWhere = strWhere & " And [Category]=""" & _
Me.cboCategory & """"
End If
DoCmd.OpenReport stDocument, acPreview, , strWhere

Thanks again for your help and patience; all I have so far is the report,
and

[quoted text clipped - 44 lines]

 

[kniedens via AccessMonster.com]

Okay, I got in to change the code, but an error message come up at this
point:
strWhere=strWhere&"And[Category]=""&_
it says "compile error" and "expected end of statement"

Once again...any help is appreciated!

 

[Duane Hookom]

Did you copy and paste my code? It looks like a bunch of spaces are missing.
I will put ~ where there should be spaces:
strWhere=strWhere~&~"~And~[Category]=""~&~_

--
Duane Hookom
MS Access MVP

Okay, I got in to change the code, but an error message come up at this
point:
strWhere=strWhere&"And[Category]=""&_
it says "compile error" and "expected end of statement"

Once again...any help is appreciated!

Assuming you have a combo box [cboCategory] on your form and you want to
open a report [rptMyReport] with the text field [Category] in the report's
record source.

Make sure you have the above set up on your form. Then use the command
button wizard to add a button to open the report. Then view the code
created
by the wizard and change it like:

Dim strWhere as String
Dim stDocument as String
stDocument = "rptMyReport"
strWhere = "1=1 "
If Not IsNull(Me.cboCategory) Then
strWhere = strWhere & " And [Category]=""" & _
Me.cboCategory & """"
End If
DoCmd.OpenReport stDocument, acPreview, , strWhere

Thanks again for your help and patience; all I have so far is the
report,
and

[quoted text clipped - 44 lines]

Hi, thanks for the reply; yes, I opened the form in dialog mode, but
am
pretty new to this, so I probably am doing a lot of things wrong.

 

[kniedens via AccessMonster.com]

Hi, thanks for the help on the spacing... I am still getting error messages,
although they are further along in the code. I have tried multiple spacing
options, and haven't yet succedded...if you get a chance, could you show me
the rest of the code using the ~ for spaces?

Did you copy and paste my code? It looks like a bunch of spaces are missing.
I will put ~ where there should be spaces:
strWhere=strWhere~&~"~And~[Category]=""~&~_

Okay, I got in to change the code, but an error message come up at this
point:

[quoted text clipped - 29 lines]

 

[Duane Hookom]

Post your code back that gives you the error(s).

--
Duane Hookom
MS Access MVP

Hi, thanks for the help on the spacing... I am still getting error
messages,
although they are further along in the code. I have tried multiple spacing
options, and haven't yet succedded...if you get a chance, could you show
me
the rest of the code using the ~ for spaces?

Did you copy and paste my code? It looks like a bunch of spaces are
missing.
I will put ~ where there should be spaces:
strWhere=strWhere~&~"~And~[Category]=""~&~_

Okay, I got in to change the code, but an error message come up at this
point:

[quoted text clipped - 29 lines]

am
pretty new to this, so I probably am doing a lot of things wrong.

 

[kniedens via AccessMonster.com]

Hi,
Here is the code:
strWhere=strWhere & " And [Category]="" & _
Me.cboCategory_Label& ""


The error says, "compile error" and "syntax error"

Thanks yet again!
kari

Post your code back that gives you the error(s).

Hi, thanks for the help on the spacing... I am still getting error
messages,

[quoted text clipped - 13 lines]

 

[Douglas J. Steele]

If that's a literal copy-and-paste of your code, you need a space between
Me.cboCategory_Label and the ampersand.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hi,
Here is the code:
strWhere=strWhere & " And [Category]="" & _
Me.cboCategory_Label& ""


The error says, "compile error" and "syntax error"

Thanks yet again!
kari

Post your code back that gives you the error(s).

Hi, thanks for the help on the spacing... I am still getting error
messages,

[quoted text clipped - 13 lines]

am
pretty new to this, so I probably am doing a lot of things wrong.

 

[kniedens via AccessMonster.com]

Hi, Thanks for the input. I have tried various things with spacing throughout,
and since I am pretty new to this I don't really know how to experiment with
the code much. it still isn't working, so here is what all I have:

Option Compare Database

Private Sub OK_Click()
Dim strWhere As String
Dim stDocument As String
stDocument = "Efficiancy and Labour per Unit by Category"
strWhere = "1=1"
If Not IsNull(Me.cboCategory_Label) Then
strWhere = strWhere & " And [Category]=""& _
Me.cboCategory_Label & " "
End If
DoCmd.OpenReport stDocument, acViewPreview, , strWhere
End Sub

Any suggestions are appreciated....k

If that's a literal copy-and-paste of your code, you need a space between
Me.cboCategory_Label and the ampersand.

Hi,
Here is the code:

[quoted text clipped - 12 lines]

 

[Douglas J. Steele]

Assuming Category is a text field, you need quotes before and after the
value you're trying to use as a criteria. To have a double quote appear in a
string, you need to use two double quotes in a row:

strWhere = strWhere & " And [Category]=""" & _
Me.cboCategory_Label & """"


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hi, Thanks for the input. I have tried various things with spacing
throughout,
and since I am pretty new to this I don't really know how to experiment
with
the code much. it still isn't working, so here is what all I have:

Option Compare Database

Private Sub OK_Click()
Dim strWhere As String
Dim stDocument As String
stDocument = "Efficiancy and Labour per Unit by Category"
strWhere = "1=1"
If Not IsNull(Me.cboCategory_Label) Then
strWhere = strWhere & " And [Category]=""& _
Me.cboCategory_Label & " "
End If
DoCmd.OpenReport stDocument, acViewPreview, , strWhere
End Sub

Any suggestions are appreciated....k

If that's a literal copy-and-paste of your code, you need a space between
Me.cboCategory_Label and the ampersand.

Hi,
Here is the code:

[quoted text clipped - 12 lines]

am
pretty new to this, so I probably am doing a lot of things
wrong.

 

[kniedens via AccessMonster.com]

Hi, sorry but none of this is making sense to me. I tried your suggestions in
the code, but it still isn't working for me...is there a different way to go
about this? I just need to be able to set the criteria in a report based on
the selection made in a form....do I need to change settings somewhere in my
report, query, or form? If I do need to go about this by changing code, is
there somewhere I can find the codes listed or explained?

In case I missed a part of my description of what I need, here goes:
I have a report based on a query. one of the fields in the query is "category.
" Upon opening the report, I want a form to pop up (or I can just name a form
"open report" and we'll start from a form if that is easier) which lists
about 20 different production line categories. Based on the selection made....
ok button pressed, I want the report to run for that particular criteria.

Thanks for bearing with me through this..it's my first big project, and I am
doing okay with everything but this so far...

Assuming Category is a text field, you need quotes before and after the
value you're trying to use as a criteria. To have a double quote appear in a
string, you need to use two double quotes in a row:

strWhere = strWhere & " And [Category]=""" & _
Me.cboCategory_Label & """"

Hi, Thanks for the input. I have tried various things with spacing
throughout,

[quoted text clipped - 27 lines]

 

[Douglas J. Steele]

"isn't working" doesn't tell us much.

Do you get an error? If so, what's the error?

Just before your statement

DoCmd.OpenReport stDocument, acPreview, , strWhere

put

Debug.Print strWhere

Go to the Immediate window (Ctrl-G), and copy what appears there back here.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hi, sorry but none of this is making sense to me. I tried your suggestions
in
the code, but it still isn't working for me...is there a different way to
go
about this? I just need to be able to set the criteria in a report based
on
the selection made in a form....do I need to change settings somewhere in
my
report, query, or form? If I do need to go about this by changing code, is
there somewhere I can find the codes listed or explained?

In case I missed a part of my description of what I need, here goes:
I have a report based on a query. one of the fields in the query is
"category.
" Upon opening the report, I want a form to pop up (or I can just name a
form
"open report" and we'll start from a form if that is easier) which lists
about 20 different production line categories. Based on the selection
made....
ok button pressed, I want the report to run for that particular criteria.

Thanks for bearing with me through this..it's my first big project, and I
am
doing okay with everything but this so far...

Assuming Category is a text field, you need quotes before and after the
value you're trying to use as a criteria. To have a double quote appear in
a
string, you need to use two double quotes in a row:

strWhere = strWhere & " And [Category]=""" & _
Me.cboCategory_Label & """"

Hi, Thanks for the input. I have tried various things with spacing
throughout,

[quoted text clipped - 27 lines]

pretty new to this, so I probably am doing a lot of things
wrong.

 

[kniedens via AccessMonster.com]

thanks...sorry, by "isn't working" I meant that I keep getting error messages
in the code.

I added, "Debug.Print strWhere" per your suggestion, then used ctrl+G. A
blank page appeared below the area with the code, then an error message came
up which stated, "Compile error: Expected: end of statement."

thanks

"isn't working" doesn't tell us much.

Do you get an error? If so, what's the error?

Just before your statement

DoCmd.OpenReport stDocument, acPreview, , strWhere

put

Debug.Print strWhere

Go to the Immediate window (Ctrl-G), and copy what appears there back here.

Hi, sorry but none of this is making sense to me. I tried your suggestions
in

[quoted text clipped - 34 lines]

 

[Douglas J. Steele]

Since AccessMonster seems to remove most of the history, it's a little hard
for me coming in late to this discussion.

Please copy-and-paste your entire procedure so that I can see everything as
it currently is.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

thanks...sorry, by "isn't working" I meant that I keep getting error
messages
in the code.

I added, "Debug.Print strWhere" per your suggestion, then used ctrl+G. A
blank page appeared below the area with the code, then an error message
came
up which stated, "Compile error: Expected: end of statement."

thanks

"isn't working" doesn't tell us much.

Do you get an error? If so, what's the error?

Just before your statement

DoCmd.OpenReport stDocument, acPreview, , strWhere

put

Debug.Print strWhere

Go to the Immediate window (Ctrl-G), and copy what appears there back
here.

Hi, sorry but none of this is making sense to me. I tried your
suggestions
in

[quoted text clipped - 34 lines]

pretty new to this, so I probably am doing a lot of things
wrong.