Parameter Query Using UPDATE 1

[MattSavage]

I have an Inventory database, and I have created an update query to add new inventory to a quantity field. Using a parameter query, the user is asked to enter the quantity to add, then asked to enter the Product number. Here is the query:

UPDATE Products SET Products.Plant_Inventory = [Plant_Inventory]+[Amount to add:]
WHERE (((Products.PHI_Number)=[PHI Number:]));

I would like the user to be able to enter the Product ID first, then enter the quantity to add. Is this possible to do without using stored procedures or VBA?

 

[BNPMike]

You wouldn't normally do this with a parameterised query. It's unnecessarily user un-friendly eg what happens if they type in a product that doesn't exist or get the amount in the wrong format? How do they add a new product or correct a previous error?

I should start again using a form.

 

[MattSavage]

Thanks BNPMike that's a good point. The way I have it set up now is that each time the database is changed an alert is displayed. If the user types an invalid Product ID, the alert will say that 0 records will be updated. Only a few users here will be working with this databse, and the are intelligent enough to understand how the DB is set up. I have set up another query that adds products easily. I simply would prefer to have the user enter the product number before entering the amount to add. Do you know of a better way to do this that would prevent errors better? Please let me know. I am a network admin here but very inexperienced when it comes to DBs.

 

[BNPMike]

It's a shame they don't provide manuals like they used to. Access came with 3 manuals which would show exactly how to do this.

I'm sure there are cleverer ways but I would probably do this:

1) Create a form with just one text field where the user can enter the required Product ID. Add a button which calls Form 2

2) Create Form 2 which has a text box (amount to add) and datasheet control that is basically a query with all the relevant fields where product id = product id input from the previous form. The user then can

- view the existing data and amend any errors
- put an amount in amount to add
- add a new record

3) Add a button on the form that calls your update query using the amount to add value from the form text field.

 

[MattSavage]

Thanks again BNPMike, that is definately a good way to go. However, I'd like to avoid using forms simply because I want to keep this database as simple as possible, since only myself and 1 other person will be accessing it.

I was able to find a way to do exactly what I wanted to do. In case anyone else would like to specifically order parameter queries, here's how you do it:

To control the order in which the prompts appear when running a parameter query containing more than one parameter, you can specify the desired order in the Query Parameters window. Here's how...

In the query design view choose Query > Parameters… to open the Query Parameters window.

In the Parameter column, type the prompt for each parameter exactly as it was typed in the QBE grid.

In the Data Type column specify the kind of data (as defined in the table properties). Pick a type from the list. The default type is Text.

List the parameters in the order in which you want the dialog boxes to appear when the user runs the query.

Click OK to accept your entries and close the window.

Thanks again BNPMike!