Enter Parameter Value message - after sorting

[Grunners]

All,

SQL backend - Access frontend. All clients have the same frontend. Everybody can run the queries with [Please input value] as the criteria. Box pops up, they enter criteria and sort away, works fine.

On one machine, after sorting the results, it will always ask 'Enter Parameter Value'. I've relinked all the tables, checked for missing references, checked the ODBC, pulled across a replacement client. The query hasn't any ORDER BY clauses in, it's not linked to a report, it's just a simple query on one table. Except it seems on this one machine!

I've just tried stripping down the query to a couple of fields and changed the field the user has to input on. Same problem when I sort. So I've tried another table - same problem. It's as though the query is running and then forgetting the criteria?

I really want to put the PC in the skip now but it's got to be something relatively simple. Hasn't it?!!

Any ideas as to which way to turn would be much appreciated!

Many thanks

 

[neileg]

Two things:
1) I prefer to caputure user input in a form insteat of a parameter prompt. If you leave the form open the query can see the parameter all the time.
2) If this doesn't appeal, post the sql of your query

 

[Grunners]

Apologies for the late reply.

I've ended up following your advice and having the user input the parameter to a form. I'm in two minds as to whether I should have to do this but in the short term it gets me round the problem.

It's an issue I've had before, a long time ago though, so I'm building a new machine and I bet it'll work as it did first time!

Many thanks indeed for replying - an independent point of view is always useful.

 

[neileg]

Pleased it solved the problem. It's my own view that forms are the correct way to capture any kind of user input whether it's data or parameters. I only use a parameter prompt for databases that are exclusively for my own use, when I can't be bothered to make them user friendly!

 

[kstarnes]

Neil,

I was just searching for information on how to make my parameter prompts more friendly and I ran across this post. Would it be possible for you to explain how I would create a form to enter information into a parameter prompt? At the moment I have several queries with strange looking prompts, one of which is for a check box and I don't think the 0/-1 thing is going to be very well received.
Thanks,
Kathryn

 

[Rabbie]

You don't need a parameter prompt if you are using the form. Where you have the parameter prompt just use the contents of the field on your form like this

where YES_No_field = me!formfield



Hope this helps

 

[kstarnes]

No...actually I'm more confused now. I think i should do some more reading/searching. Thanks for responding, however.

 

[Rabbie]

Neil is advising you to not use a parameter prompt but to get the value from a field on your form so for the Yes?no field you would let the users set the required value on the form. If the Form field is a boolean the users could set it from a radio button etc. No need for them to see the 0/-1 thing at all.

 

[kstarnes]

I experimented a little with that...but either I'm confused (very likely) or I haven't been very clear about what I'm trying to do (also likely).

I currently have a form and subform controlled by two tables. The database is designed to track information about contracts-including payments in the subform. When the contract is completely finished there is a checkbox in the main form to indicate this. I have created a query that will extract either all of the records with the box checked, or all of the records without it checked and display some relevant information. The query works fine; but in order to get it to run the user must define the parameter of checked or unchecked. Since the people using this aren't familiar with access I think it will be more intutive if they can enter something other than 0/-1. I don't want the query generated from the original form as the person inputting the data and the person generating the query are not the same-ideally I will link the query to the switchboard.
I'm not a programmer or a database developer or anything-I'm a political theory person doing a temporary job...I'm just trying to do it as professionally as I am able. I have gotten this far purely through trial and error so if I seem a bit dense don't worry, I will work through it eventually!

 

[neileg]

I would use a list box. With a list you can have two columns, but hide one of them. So I would have the first column hold 0 or -1 and the second column hold 'Continuing' and 'Complete' or whatever you choose. If the first column is the bound column (which it is by default) then your user will see 'Continuing' but the query will see 0.

I'm not a programmer or a developer either, I'm an accountant.

 

[Rabbie]

Hi

Thanks for your clarification. I quite understand why you want to avoid having users entering 0/-1. With the latest clarification the simplest way would be to give the users a form or Switch board with 2 buttons - one to run the queryfor the Yes case and one to run the query for the No case. You would just need to set that criteria in the code behind the buttons.

Hope this helps.

PS You don't come over as Dense so don't worry about it.

 

[kstarnes]

Perfect! That should have been obvious Thank you.

 

[Rabbie]

Perfect! That should have been obvious Thank you.

Happy to have been of help. It can be difficult to see a solution when you are too close to the problem