Solved Query repeats criteria input. (1 Viewer)

[Woodpecker]

I've been dogged by this problem on separate occasions for quite a while now. When I run the attached query it prompts me to enter the Media Type as expected, but when I do and then press enter, it repeats the prompt so I have to enter the Media Type twice before it lists. This is annoying, and inconvenient so can anybody offer any clues to what's happening please?

 

[Woodpecker]

I've been dogged by this problem on separate occasions for quite a while now. When I run the attached query it prompts me to enter the Media Type as expected, but when I do and then press enter, it repeats the prompt so I have to enter the Media Type twice before it lists. This is annoying, and inconvenient so can anybody offer any clues to what's happening please?

Sorry, forgot to mention I'm using Access 2000.

 

[plog]

Can you instead post the SQL of the query or provide a sample database for us to examine?

 

[Woodpecker]

What's the SQL Plog?

 

[Gasman]

What's the SQL Plog?

Give us a break?, please

 

[CJ_London]

can’t remember what 2000 query window looks like but possibly top left or bottom right there is a sql button - click it and the sql window will appear - that is what access actually uses. Copy and paste the sql to this thread and don’t forget to use the code button (</>) to preserve the formatting

edit: most likely you have the same prompt in the query properties. (Right click in the query window and select properties)

 

[Pat Hartman]

Are both prompts identical? If not, there may be two references that are slightly different.

Usually, you run into this "multiple prompts" issue when working with reports. You get prompted when you open the report to view the data. Then you get prompted again if you decide to print the report. The reason is that Access has to run the query twice. Once when you view the report and a second time when it gets sent to the printer.

To avoid the "multiple prompts" issue, the best solution is to use a control on a form. Either use a combo to select a specific media type or use a Text box.

Just FYI- using LIKE is rarely appropriate for "codes" like media type. It is more commonly used for actual text like names or addresses. A media type is a small, controlled list and an item can easily be selected using a combo and used with the "=" operator rather than "Like *".

"Like *" prevents the query engine from optimizing the request and forces a full table scan in order to create the result set. This isn't a problem if you have only a few thousand rows in a table but the larger your row count gets, the slower the query will be. Therefore, NEVER use LIKE lightly. Use it only when you actually need it. If you are using it because you want an "all" option as default, then using a control on a form will help greatly.

Where [Media Type] = Forms!yourform!cboMediaType OR Forms!yourform!cboMediaType Is Null;

 

[Woodpecker]

Are both prompts identical? If not, there may be two references that are slightly different.

Usually, you run into this "multiple prompts" issue when working with reports. You get prompted when you open the report to view the data. Then you get prompted again if you decide to print the report. The reason is that Access has to run the query twice. Once when you view the report and a second time when it gets sent to the printer.

To avoid the "multiple prompts" issue, the best solution is to use a control on a form. Either use a combo to select a specific media type or use a Text box.

Just FYI- using LIKE is rarely appropriate for "codes" like media type. It is more commonly used for actual text like names or addresses. A media type is a small, controlled list and an item can easily be selected using a combo and used with the "=" operator rather than "Like *".

"Like *" prevents the query engine from optimizing the request and forces a full table scan in order to create the result set. This isn't a problem if you have only a few thousand rows in a table but the larger your row count gets, the slower the query will be. Therefore, NEVER use LIKE lightly. Use it only when you actually need it. If you are using it because you want an "all" option as default, then using a control on a form will help greatly.

Where [Media Type] = Forms!yourform!cboMediaType OR Forms!yourform!cboMediaType Is Null;

Thanks for your detailed reply Pat. I was intending to change all instances of pop-ups to combo or text boxes anyway. You mention their use with forms, but as my usage is always personal I haven't got much use for forms anyway. Can I use text or combo boxes directly with a query?

 

[Pat Hartman]

No, the prompt is quite limited which is why I always use a form. That way I can validate the value before trying to use it. Even when I make apps for myself, I use the same standards I would use for a client. I'm pretty important in my world and I deserve the best

 

[Woodpecker]

Yes, of course Pat and many thanks. Even though I don't use forms, I suppose there's nothing stopping me creating one just to run the query!

 

[Pat Hartman]

Try it, you'll like it

 

[Gasman]

Yes, of course Pat and many thanks. Even though I don't use forms, I suppose there's nothing stopping me creating one just to run the query!

Just use the prompt once at the earliest query, although you appear to just be showing one, based on tables, so unsure why you get multiple prompts? That query does not appear to be in the db you uploaded either?

I would however set a TempVar from a form and then use that everywhere.
I myself you do not like using Form names as criteria. Just a quirk I have.

 

[Woodpecker]

Ok Gasman thanks, I'll give it a try.

 

[Pat Hartman]

Ending up with a tempVar is fine. The point of the form is to capture the data and validate it before using it in the query. The form can save it as a tempVar and that way the query can pick up the value from the TempVar if you don't want to reference a control on a form. The point is, you need code to create the tempVar and that leads you to a form.