TINA - MULTIPLE CHOICES IN A PROMPT BOX

  • Thread starter Thread starter Tina Marie
  • Start date Start date
T

Tina Marie

Hi ... I have a query that runs a report ... it prompts for the Year and then
prompts for the Session (like Spring, Fall, etc.) ... they want to type in a
year ... e.g. 2009 but may want to have the report show say spring and fall
.... can you put more than one choice in a prompt box? ... if I do a second
prompt box (i.e. an 'or') then I would have to do another year prompt ... any
suggestions?
 
I do not think you can with a prompt but an unbound form with text boxes can.
Reference the text boxes as criteria in the query like this --
[Forms]![YourFormName]![TextBox_1] OR
IIF([Forms]![YourFormName]![TextBox_2] Is Null, Null,
[Forms]![YourFormName]![TextBox_2])
 
Hmm .. interesting Karl .. okay, will give this a try ... thanks!! Tina
--
Thanks!!

T. Marie


KARL DEWEY said:
I do not think you can with a prompt but an unbound form with text boxes can.
Reference the text boxes as criteria in the query like this --
[Forms]![YourFormName]![TextBox_1] OR
IIF([Forms]![YourFormName]![TextBox_2] Is Null, Null,
[Forms]![YourFormName]![TextBox_2])


Tina Marie said:
Hi ... I have a query that runs a report ... it prompts for the Year and then
prompts for the Session (like Spring, Fall, etc.) ... they want to type in a
year ... e.g. 2009 but may want to have the report show say spring and fall
... can you put more than one choice in a prompt box? ... if I do a second
prompt box (i.e. an 'or') then I would have to do another year prompt ... any
suggestions?
Click to expand...
Click to expand...
 
Hmm .. interesting Karl .. okay, will give this a try ... thanks!! Tina
Click to expand...

Hi Tina,

One way to have a report prompt the user for criteria before it runs
is to open a form from the report's Open event. Open the form in
Dialog mode so that the report waits for the form to be closed or
hidden before it proceeds. That way you can collect criteria from the
user and build a Where clause for the report. It also means that you
can call the report directly - you don't need to call it from a form.
And the selection form is reusable - it can be called from multiple
reports if they need the same criteria.

I've posted examples of this technique at
www.JStreetTech.com/downloads - see "Report Selection Techniques".

Armen Stein
Microsoft Access MVP
www.JStreetTech.com
 
Thanks Armen ... I'll take a look .. wasn't still clear on how an unbound
form could 'gather' multiple choices ... I'll let you know ...
 
Thanks Armen ... I'll take a look .. wasn't still clear on how an unbound
form could 'gather' multiple choices ... I'll let you know ...
Click to expand...

The suggestion Karl offered was that you could have two different
comboboxes on your selection form, allowing the user to select one or
two Sessions. You could then refer to them in your query's criteria
on different OR lines as Karl suggested.

The approach I suggested is different. It still uses an unbound form,
but it requires some modifications to the sample VBA code and is more
flexible. It actually changes the WHERE clause in the SQL statement
for the report so that your criteria could include any number of
fields and their combinations.

Either way will work - you can explore them and see which works best
for you.

Armen Stein
Microsoft Access MVP
www.JStreetTech.com
 
Hi Armen ... still a little confused ... I don't know vba/sql well enough to
write it and/or modify your code. I'll tell you what I did ... this one for
picking a course (would be the same for picking a session or multiple
sessions) ...

Created an unbound form called pickCourse with a combo box called cboCourse,
created a macro called mcrPickCourse with an openform command to open the
form pickcourse in dialog mode, in the query that runs my report, I put into
the criteria cell for course [forms]![pickcourse]![cbocourse] ... not
working??
 
Created an unbound form called pickCourse with a combo box called cboCourse,
created a macro called mcrPickCourse with an openform command to open the
form pickcourse in dialog mode, in the query that runs my report, I put into
the criteria cell for course [forms]![pickcourse]![cbocourse] ... not
working??
Click to expand...

Is your form open when your report runs? It needs to be.

When you say "not working", what do you mean? Do you get an error
message? Do you get prompted for a parameter value? Does the report
return too many records? Too few? None?

Try putting an actual Course value in your query just to try it out,
instead of the Forms!pickcourse!cbocourse reference. Does the report
run the way you expect?

Armen Stein
Microsoft Access MVP
www.JStreetTech.com
 
Hey Armen ... when i tried an actual course, it works ... put back the
'forms! bla bla ... but i don't understand why i would have the little
unbound form already open ... the user picks from a submenu, the report they
want to run ... i'm hoping at that point, that the little unbound form
(acting like a regular prompt box) would open, let them choose from the combo
box (nicer than typing the whole course name) ... then I don't have an 'ok'
button? don't know how to program that either ... but in your sample sample
on your site ... seems you just chose something and the report runs auto
anyway ... does this make sense? p.s. error was ... it thought it was a
parameter? Yikes!!
--
Thanks!!

T. Marie


Armen Stein said:
Created an unbound form called pickCourse with a combo box called cboCourse,
created a macro called mcrPickCourse with an openform command to open the
form pickcourse in dialog mode, in the query that runs my report, I put into
the criteria cell for course [forms]![pickcourse]![cbocourse] ... not
working??
Click to expand...

Is your form open when your report runs? It needs to be.

When you say "not working", what do you mean? Do you get an error
message? Do you get prompted for a parameter value? Does the report
return too many records? Too few? None?

Try putting an actual Course value in your query just to try it out,
instead of the Forms!pickcourse!cbocourse reference. Does the report
run the way you expect?

Armen Stein
Microsoft Access MVP
www.JStreetTech.com
Click to expand...
 
Hey Armen ... when i tried an actual course, it works ... put back the
'forms! bla bla ... but i don't understand why i would have the little
unbound form already open ... the user picks from a submenu, the report they
want to run ... i'm hoping at that point, that the little unbound form
(acting like a regular prompt box) would open, let them choose from the combo
box (nicer than typing the whole course name) ... then I don't have an 'ok'
button? don't know how to program that either ... but in your sample sample
on your site ... seems you just chose something and the report runs auto
anyway ... does this make sense? p.s. error was ... it thought it was a
parameter? Yikes!!
Click to expand...

Hi T. Marie,

My example shows the form being opened automatically from the Report's
On Open event. If you open it in Dialog mode the report will wait
until you make your selection. Then when you click OK you need to
hide the form (Me.Visible = False) so that the report can continue.

Armen Stein
Microsoft Access MVP
www.JStreetTech.com
 
Hi Armen .. okay, I've got to be driving you crazy!! I put a command button
on the little form ... called it cmdOK ... put it your code on its 'on click'
event. When I run my report, my form does auto open (its a macro that opens
the form) and its on the 'on open' event of the report ... I choose a course
from the drop down combo box and then click OK ... now, I have a macro that
runs on the 'on no data' event with a message to let the user know that there
criteria doesn't match ... that's what comes up ... but I know I have a
record out there for that course? Probably some little thing right?
 
Hi Armen .. okay, I've got to be driving you crazy!!
Click to expand...

That's a very short trip. :)
I put a command button
on the little form ... called it cmdOK ... put it your code on its 'on click'
event. When I run my report, my form does auto open (its a macro that opens
the form) and its on the 'on open' event of the report ... I choose a course
from the drop down combo box and then click OK ... now, I have a macro that
runs on the 'on no data' event with a message to let the user know that there
criteria doesn't match ... that's what comes up ... but I know I have a
record out there for that course? Probably some little thing right?
Click to expand...

We don't use Macros in our shop, so there might be something I'm not
seeing here. We only use VBA, which is what my example shows.

However....

Does the report display only AFTER you click the OK button on the
form?

Does your form hide itself when the OK button is clicked?

Both answers have to be Yes for this to work. Otherwise your report
will try to run using criteria that you haven't actually specified
yet. As usual, timing is everything.

After your report displays "no records", try this. Use Ctrl-G to open
the Immediate Window. Type in:

?[forms]![pickcourse]![cbocourse]

(note the initial "?") and hit Enter. You should see the value that's
in the combobox on your form. Does it make sense as a criteria for
your query? Is it possible that it's showing the *name* of your
course instead of the *ID* of your course? If so, then the bound
column of your combobox is incorrect.

Armen Stein
Microsoft Access MVP
www.JStreetTech.com
 
Hey Armen ... short trips are good!! and so is crazy!

Well .. you were right ... it was grabbing the code, not the course itself
.... I had both in the sql query ... took out code, column count to 1, bound 1
etc etc ... and it worked!! Weeeeeeeeeeeeeeee!! THANKS SOOOOOO MUCH!!

Now ... for the session example, can I do this 'user friendly' method to
pick more than one course or more than one session the same way??
--
Thanks!!

T. Marie


Armen Stein said:
Hi Armen .. okay, I've got to be driving you crazy!!
Click to expand...

That's a very short trip. :)
I put a command button
on the little form ... called it cmdOK ... put it your code on its 'on click'
event. When I run my report, my form does auto open (its a macro that opens
the form) and its on the 'on open' event of the report ... I choose a course
from the drop down combo box and then click OK ... now, I have a macro that
runs on the 'on no data' event with a message to let the user know that there
criteria doesn't match ... that's what comes up ... but I know I have a
record out there for that course? Probably some little thing right?
Click to expand...

We don't use Macros in our shop, so there might be something I'm not
seeing here. We only use VBA, which is what my example shows.

However....

Does the report display only AFTER you click the OK button on the
form?

Does your form hide itself when the OK button is clicked?

Both answers have to be Yes for this to work. Otherwise your report
will try to run using criteria that you haven't actually specified
yet. As usual, timing is everything.

After your report displays "no records", try this. Use Ctrl-G to open
the Immediate Window. Type in:

?[forms]![pickcourse]![cbocourse]

(note the initial "?") and hit Enter. You should see the value that's
in the combobox on your form. Does it make sense as a criteria for
your query? Is it possible that it's showing the *name* of your
course instead of the *ID* of your course? If so, then the bound
column of your combobox is incorrect.

Armen Stein
Microsoft Access MVP
www.JStreetTech.com
Click to expand...
 
Well .. you were right ... it was grabbing the code, not the course itself
... I had both in the sql query ... took out code, column count to 1, bound 1
etc etc ... and it worked!! Weeeeeeeeeeeeeeee!! THANKS SOOOOOO MUCH!!
Click to expand...

Glad to hear it. I like being right sometimes. But I have to ask -
why didn't you use the code instead of the name? What if two courses
have the same name? The unique code would probably be more reliable.
Now ... for the session example, can I do this 'user friendly' method to
pick more than one course or more than one session the same way??
Click to expand...

Well, that's a bit trickier. I assume you mean that you would like to
show all records for Course A OR Course B, right? You sure you don't
want to learn some VBA code? My example shows how to build a WHERE
clause in code. You could have say three comboboxes on your form,
then build your Where clause like this:

Where CourseCode IN ("A", "B", "C")

or if CourseCodes are numbers, you don't use quotes:

Where CourseCode IN (1, 2, 3)

(The SQL IN operator is a quick way of stringing multiple OR
statements together.)

Anyway, there are lots of people here that would help you with some
VBA coding if you want to try it. It's much more powerful than
macros.

Armen Stein
Microsoft Access MVP
www.JStreetTech.com
 
Hey Armen ... I agree re the code versus the name ... right now there are
only about 20 courses so not a problem yet ... I think the user will want to
see the course names though ... now I could show both I suppose ... anyway,
I'll burn that one when she comes ...

Re the multiple choices ... okay, will 'play around' with that one ... and
see if I can figure it out ... believe it or not I used to be a cobol
programmer .. yep, old eh? I do a little bit of sql/vba because of access and
excel but not much ... guess I'll have to start learning .. will be out of
town for the next 2 days so you may not hear back that I got it or not until
end of week ... but once again ... thanks so much for your help ... even
though sometimes 'goof around' on this site ... its gold to me!! Ta, Tina
 
Back
Top