criteria in parameter queries

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a criteria for my one field that lets them type in what type of data
they want for a report. I would like a prompt to show up if they enter the
wrong data and tell them to them enter specific criteria. Thanks
 
Are you using a control on a form? If not, then try it. You will be able to
set defaults, use combo or list boxes, check integrity,....
 
I have a criteria for my one field that lets them type in what type of data
they want for a report. I would like a prompt to show up if they enter the
wrong data and tell them to them enter specific criteria. Thanks
Click to expand...

You would need a form to enter the parameter in. Then you can use the
Form Command Button click event to verify that the entry is correct
and if so, continue. Otherwise, go back to the control and re-enter a
permitted value.

Or use a combo box that contains only permitted values.

Code the query criteria using this kind of syntax:
forms!formName!ControlName
 
I don't know if you guys get what im trying to say. I'll try to restate it.
Ok so I have one form that they put in there data. One of the fields is
named Genre. So I made a query that would give out a report that first
prompted them which genre of music they want to see. So if they type in one
of the genres they will get a report that shows each piece of music that is
that kind of genre. But if they type in a genre that doesn't exist the
report will come up blank...I would like to know if theres a way to have a
second prompt come up stating that theyve typed in the wrong genre and give
them a list of the existing genres so that they can retype in the right
one.....that is what id like to know...Thanks!!
 
I would like to know if theres a way to have a
second prompt come up stating that theyve typed in the wrong genre and give
them a list of the existing genres so that they can retype in the right
one....
Click to expand...

Well, rather than making them type in genres and punishing them if
they make a mistake, I'd suggest giving them a Combo Box with a list
of the existing genres. Put an *UNBOUND* combo box on the criteria
form, based on a query of all the valid genres; and use a criterion of

=Forms![NameOfForm]![NameOfCombo]


John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
 
I don't think you understood our suggestions. I NEVER use parameter prompts
because they are prone to errors and just not very functional. Fred and I
both suggested you use a control on a form to provide your criteria. This
allows you to use a combo box of acceptable genre.

You could use the On No Data event of the report and add some code...blah,
blah, blah, but that would be a waste of time since you would be patching a
bad design (my opinion).
 
I do have a form with a drop down to select the genre.....but to make a
printable report of just the genre that they want i figured this would be the
easiest way but it would be nice if it would tell them that they typed the
genre wrong in the prompt.Thanks
 
Is your report's record source query criteria tied in any way to a drop down
(combo box) on a form? Perhaps you should provide the SQL view of your
report's record source.
 
I do have a form with a drop down to select the genre.....but to make a
printable report of just the genre that they want i figured this would be the
easiest way but it would be nice if it would tell them that they typed the
genre wrong in the prompt.Thanks
Click to expand...

R, please go back and reread the thread. Duane, Fred and I all made a
suggestion which makes it UNNECESSARY FOR THE USER TO TYPE ANYTHING.

If they pick a genre from a list, then *they cannot type it wrong* so
you don't NEED any code to deal with them typing it wrong.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
 
Ok here is my sql view of my genre query:
SELECT [Music Table].TitleID, [Genre Table].Genre, [Music Table].Composer,
[Music Table].Arrangedby, [Music Table].YearofPublication

FROM [Subgenre table] INNER JOIN ([Genre Table] INNER JOIN [Music Table] ON
[Genre Table].GenreID = [Music Table].GenreID) ON [Subgenre table].SubgenreID
= [Music Table].SubgenreID

WHERE ((([Genre Table].Genre)=[Genre:]))

ORDER BY [Music Table].TitleID, [Genre Table].Genre, [Subgenre
table].Subgenre;

Ok, now ive made a switch board item so that they only have to hit the Genre
Report button...when they hit that a prompt comes up and asks them for the
genre...when they type in the correct genre the report comes up in Word fine
and dandy...but if they type in a wrong one the report comes up in word with
all the headers but no data in them obviously....all i want is that when they
type in the wrong genre a prompt will come up and say you have entered an
invalid entry and then list the genres and have them pick it...thats all i
want..and my query is a parameter query in case u were wondering.. Thanks! I
appreciate the help!
 
Ok here is my sql view of my genre query:
SELECT [Music Table].TitleID, [Genre Table].Genre, [Music Table].Composer,
[Music Table].Arrangedby, [Music Table].YearofPublication

FROM [Subgenre table] INNER JOIN ([Genre Table] INNER JOIN [Music Table] ON
[Genre Table].GenreID = [Music Table].GenreID) ON [Subgenre table].SubgenreID
= [Music Table].SubgenreID

WHERE ((([Genre Table].Genre)=[Genre:]))

ORDER BY [Music Table].TitleID, [Genre Table].Genre, [Subgenre
table].Subgenre;

Ok, now ive made a switch board item so that they only have to hit the Genre
Report button...when they hit that a prompt comes up and asks them for the
genre...when they type in the correct genre the report comes up in Word fine
and dandy...but if they type in a wrong one the report comes up in word with
all the headers but no data in them obviously....all i want is that when they
type in the wrong genre a prompt will come up and say you have entered an
invalid entry and then list the genres and have them pick it...thats all i
want..and my query is a parameter query in case u were wondering.. Thanks! I
appreciate the help!
Click to expand...

I'll try once more. I understand you quite well; yes, I knew it was a
parameter query from the first, and all of us were suggesting that you
*still* use a parameter query, just a different and better parameter
query.

Create a Combo Box named cboGenre on your switchboard table. Base it
on [Genre Table], or a query sorting the genres alphabetically.

In the query above replace the parameter [Genre:] with a different
parameter:

[Forms]![Switchboard]![cboGenre]

Now the user will be able to SELECT a genre from the combo box; the
query will look at the Switchboard form and see which genre the user
has selected. The user can type the first letter or two of the genre,
and the Combo's autoexpand feature will jump right to the desired
genre.

If you insist on doing it the hard way, you'll need a new Form, a new
Query, and some VBA code to determine that an invalid entry has been
made. You'll end up with exactly what I'm suggesting above but with an
extra step and a lot more work. I'll be willing to put that together
for you at my standard consulting rates; if you'ld rather do it the
easy way, suggested above, that's free.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
 
Ok here is my sql view of my genre query:
SELECT [Music Table].TitleID, [Genre Table].Genre, [Music Table].Composer,
[Music Table].Arrangedby, [Music Table].YearofPublication

FROM [Subgenre table] INNER JOIN ([Genre Table] INNER JOIN [Music Table] ON
[Genre Table].GenreID = [Music Table].GenreID) ON [Subgenre table].SubgenreID
= [Music Table].SubgenreID

WHERE ((([Genre Table].Genre)=[Genre:]))

ORDER BY [Music Table].TitleID, [Genre Table].Genre, [Subgenre
table].Subgenre;

Ok, now ive made a switch board item so that they only have to hit the Genre
Report button...when they hit that a prompt comes up and asks them for the
genre...when they type in the correct genre the report comes up in Word fine
and dandy...but if they type in a wrong one the report comes up in word with
all the headers but no data in them obviously....all i want is that when they
type in the wrong genre a prompt will come up and say you have entered an
invalid entry and then list the genres and have them pick it...thats all i
want..and my query is a parameter query in case u were wondering.. Thanks! I
appreciate the help!
Click to expand...

I'll try once more. I understand you quite well; yes, I knew it was a
parameter query from the first, and all of us were suggesting that you
*still* use a parameter query, just a different and better parameter
query.

Create a Combo Box named cboGenre on your switchboard table. Base it
on [Genre Table], or a query sorting the genres alphabetically.

In the query above replace the parameter [Genre:] with a different
parameter:

[Forms]![Switchboard]![cboGenre]

Now the user will be able to SELECT a genre from the combo box; the
query will look at the Switchboard form and see which genre the user
has selected. The user can type the first letter or two of the genre,
and the Combo's autoexpand feature will jump right to the desired
genre.

If you insist on doing it the hard way, you'll need a new Form, a new
Query, and some VBA code to determine that an invalid entry has been
made. You'll end up with exactly what I'm suggesting above but with an
extra step and a lot more work. I'll be willing to put that together
for you at my standard consulting rates; if you'ld rather do it the
easy way, suggested above, that's free.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
Click to expand...

Wasn't there a musical play several years ago titled "Applause"?
Applause, John.
Bravo
Dare I ask for an encore? ;-)
 
Ok i think im starting to understand..but im stuck i don't know how to create
a combo box on the switchboard table. Do I go into tables or queries or
reports or what i have no idea. I tried using the tables and going into
design view but have no luck seeing what your trying to tell me. Any help on
how to create that?? Thanks!

fredg said:
Ok here is my sql view of my genre query:
SELECT [Music Table].TitleID, [Genre Table].Genre, [Music Table].Composer,
[Music Table].Arrangedby, [Music Table].YearofPublication

FROM [Subgenre table] INNER JOIN ([Genre Table] INNER JOIN [Music Table] ON
[Genre Table].GenreID = [Music Table].GenreID) ON [Subgenre table].SubgenreID
= [Music Table].SubgenreID

WHERE ((([Genre Table].Genre)=[Genre:]))

ORDER BY [Music Table].TitleID, [Genre Table].Genre, [Subgenre
table].Subgenre;

Ok, now ive made a switch board item so that they only have to hit the Genre
Report button...when they hit that a prompt comes up and asks them for the
genre...when they type in the correct genre the report comes up in Word fine
and dandy...but if they type in a wrong one the report comes up in word with
all the headers but no data in them obviously....all i want is that when they
type in the wrong genre a prompt will come up and say you have entered an
invalid entry and then list the genres and have them pick it...thats all i
want..and my query is a parameter query in case u were wondering.. Thanks! I
appreciate the help!
Click to expand...

I'll try once more. I understand you quite well; yes, I knew it was a
parameter query from the first, and all of us were suggesting that you
*still* use a parameter query, just a different and better parameter
query.

Create a Combo Box named cboGenre on your switchboard table. Base it
on [Genre Table], or a query sorting the genres alphabetically.

In the query above replace the parameter [Genre:] with a different
parameter:

[Forms]![Switchboard]![cboGenre]

Now the user will be able to SELECT a genre from the combo box; the
query will look at the Switchboard form and see which genre the user
has selected. The user can type the first letter or two of the genre,
and the Combo's autoexpand feature will jump right to the desired
genre.

If you insist on doing it the hard way, you'll need a new Form, a new
Query, and some VBA code to determine that an invalid entry has been
made. You'll end up with exactly what I'm suggesting above but with an
extra step and a lot more work. I'll be willing to put that together
for you at my standard consulting rates; if you'ld rather do it the
easy way, suggested above, that's free.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
Click to expand...

Wasn't there a musical play several years ago titled "Applause"?
Applause, John.
Bravo
Dare I ask for an encore? ;-)
Click to expand...
 
Ok i think im starting to understand..but im stuck i don't know how to create
a combo box on the switchboard table. Do I go into tables or queries or
reports or what i have no idea. I tried using the tables and going into
design view but have no luck seeing what your trying to tell me. Any help on
how to create that?? Thanks!
Click to expand...

Create the combo box on the switchboard FORM, not the table.

Table datasheets should be used only for debugging. Users shouldn't
generally see them at all. The Switchboard is just a form like any
other form; you can create controls on it at will. Open the Forms
window; select the form you're using as a switchboard (it will be
named SWITCHBOARD if you used the wizard); use the toolbox to add a
combo box to it.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
 
"Create a Combo Box named cboGenre on your switchboard table. Base it
on [Genre Table], or a query sorting the genres alphabetically." How
exactly do u create that if when you go into your Switboard Items Table You
can't enter that type of data....????? Im extremely lost with that. Thanks

R said:
Ok i think im starting to understand..but im stuck i don't know how to create
a combo box on the switchboard table. Do I go into tables or queries or
reports or what i have no idea. I tried using the tables and going into
design view but have no luck seeing what your trying to tell me. Any help on
how to create that?? Thanks!

fredg said:
Ok here is my sql view of my genre query:
SELECT [Music Table].TitleID, [Genre Table].Genre, [Music Table].Composer,
[Music Table].Arrangedby, [Music Table].YearofPublication

FROM [Subgenre table] INNER JOIN ([Genre Table] INNER JOIN [Music Table] ON
[Genre Table].GenreID = [Music Table].GenreID) ON [Subgenre table].SubgenreID
= [Music Table].SubgenreID

WHERE ((([Genre Table].Genre)=[Genre:]))

ORDER BY [Music Table].TitleID, [Genre Table].Genre, [Subgenre
table].Subgenre;

Ok, now ive made a switch board item so that they only have to hit the Genre
Report button...when they hit that a prompt comes up and asks them for the
genre...when they type in the correct genre the report comes up in Word fine
and dandy...but if they type in a wrong one the report comes up in word with
all the headers but no data in them obviously....all i want is that when they
type in the wrong genre a prompt will come up and say you have entered an
invalid entry and then list the genres and have them pick it...thats all i
want..and my query is a parameter query in case u were wondering.. Thanks! I
appreciate the help!

I'll try once more. I understand you quite well; yes, I knew it was a
parameter query from the first, and all of us were suggesting that you
*still* use a parameter query, just a different and better parameter
query.

Create a Combo Box named cboGenre on your switchboard table. Base it
on [Genre Table], or a query sorting the genres alphabetically.

In the query above replace the parameter [Genre:] with a different
parameter:

[Forms]![Switchboard]![cboGenre]

Now the user will be able to SELECT a genre from the combo box; the
query will look at the Switchboard form and see which genre the user
has selected. The user can type the first letter or two of the genre,
and the Combo's autoexpand feature will jump right to the desired
genre.

If you insist on doing it the hard way, you'll need a new Form, a new
Query, and some VBA code to determine that an invalid entry has been
made. You'll end up with exactly what I'm suggesting above but with an
extra step and a lot more work. I'll be willing to put that together
for you at my standard consulting rates; if you'ld rather do it the
easy way, suggested above, that's free.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
Click to expand...

Wasn't there a musical play several years ago titled "Applause"?
Applause, John.
Bravo
Dare I ask for an encore? ;-)
Click to expand...
Click to expand...
 
Ok well i know how to do that but that isn't exactly what i want...it needs
to be on my second page of my switchboard and i want them to just hit the
genre report button so a prompt comes up and asks them for the genre....when
i just create a combo box on the switchboard form it stays there. So you
think its better if i dont use a button and just have it manually put on
there so when they click just type it in there it will automatically open the
report?? I wanted them to hit a button then have a prompt pop up and make the
combo box but obviously u can't i take it.
 
I've made a combo box i can get it to show the genres but i can't get it to
click that genre to bring out the report. I just used the same query as
before if i put in what you suggested [Forms]![Switchboard]![cboGenre] i
can't get anything to show up not even teh genres to select. Any suggestions?

John Vinson said:
Ok here is my sql view of my genre query:
SELECT [Music Table].TitleID, [Genre Table].Genre, [Music Table].Composer,
[Music Table].Arrangedby, [Music Table].YearofPublication

FROM [Subgenre table] INNER JOIN ([Genre Table] INNER JOIN [Music Table] ON
[Genre Table].GenreID = [Music Table].GenreID) ON [Subgenre table].SubgenreID
= [Music Table].SubgenreID

WHERE ((([Genre Table].Genre)=[Genre:]))

ORDER BY [Music Table].TitleID, [Genre Table].Genre, [Subgenre
table].Subgenre;

Ok, now ive made a switch board item so that they only have to hit the Genre
Report button...when they hit that a prompt comes up and asks them for the
genre...when they type in the correct genre the report comes up in Word fine
and dandy...but if they type in a wrong one the report comes up in word with
all the headers but no data in them obviously....all i want is that when they
type in the wrong genre a prompt will come up and say you have entered an
invalid entry and then list the genres and have them pick it...thats all i
want..and my query is a parameter query in case u were wondering.. Thanks! I
appreciate the help!
Click to expand...

I'll try once more. I understand you quite well; yes, I knew it was a
parameter query from the first, and all of us were suggesting that you
*still* use a parameter query, just a different and better parameter
query.

Create a Combo Box named cboGenre on your switchboard table. Base it
on [Genre Table], or a query sorting the genres alphabetically.

In the query above replace the parameter [Genre:] with a different
parameter:

[Forms]![Switchboard]![cboGenre]

Now the user will be able to SELECT a genre from the combo box; the
query will look at the Switchboard form and see which genre the user
has selected. The user can type the first letter or two of the genre,
and the Combo's autoexpand feature will jump right to the desired
genre.

If you insist on doing it the hard way, you'll need a new Form, a new
Query, and some VBA code to determine that an invalid entry has been
made. You'll end up with exactly what I'm suggesting above but with an
extra step and a lot more work. I'll be willing to put that together
for you at my standard consulting rates; if you'ld rather do it the
easy way, suggested above, that's free.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
Click to expand...
 
I guess what im trying to say is that it won't click the value in the combo
box it just lets me drag down the genres but i can't click one. And also I
can't type anything in the combo box i have to click the arrow and drag it
down. I guess im just not very good.

R said:
I've made a combo box i can get it to show the genres but i can't get it to
click that genre to bring out the report. I just used the same query as
before if i put in what you suggested [Forms]![Switchboard]![cboGenre] i
can't get anything to show up not even teh genres to select. Any suggestions?

John Vinson said:
Ok here is my sql view of my genre query:
SELECT [Music Table].TitleID, [Genre Table].Genre, [Music Table].Composer,
[Music Table].Arrangedby, [Music Table].YearofPublication

FROM [Subgenre table] INNER JOIN ([Genre Table] INNER JOIN [Music Table] ON
[Genre Table].GenreID = [Music Table].GenreID) ON [Subgenre table].SubgenreID
= [Music Table].SubgenreID

WHERE ((([Genre Table].Genre)=[Genre:]))

ORDER BY [Music Table].TitleID, [Genre Table].Genre, [Subgenre
table].Subgenre;

Ok, now ive made a switch board item so that they only have to hit the Genre
Report button...when they hit that a prompt comes up and asks them for the
genre...when they type in the correct genre the report comes up in Word fine
and dandy...but if they type in a wrong one the report comes up in word with
all the headers but no data in them obviously....all i want is that when they
type in the wrong genre a prompt will come up and say you have entered an
invalid entry and then list the genres and have them pick it...thats all i
want..and my query is a parameter query in case u were wondering.. Thanks! I
appreciate the help!
Click to expand...

I'll try once more. I understand you quite well; yes, I knew it was a
parameter query from the first, and all of us were suggesting that you
*still* use a parameter query, just a different and better parameter
query.

Create a Combo Box named cboGenre on your switchboard table. Base it
on [Genre Table], or a query sorting the genres alphabetically.

In the query above replace the parameter [Genre:] with a different
parameter:

[Forms]![Switchboard]![cboGenre]

Now the user will be able to SELECT a genre from the combo box; the
query will look at the Switchboard form and see which genre the user
has selected. The user can type the first letter or two of the genre,
and the Combo's autoexpand feature will jump right to the desired
genre.

If you insist on doing it the hard way, you'll need a new Form, a new
Query, and some VBA code to determine that an invalid entry has been
made. You'll end up with exactly what I'm suggesting above but with an
extra step and a lot more work. I'll be willing to put that together
for you at my standard consulting rates; if you'ld rather do it the
easy way, suggested above, that's free.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
Click to expand...
Click to expand...
 
Back
Top