access formulas

Hello Peter and welcome to Mr Excel.

This is definitely possible. However, when I read your post, I couldn't work out if you are entering the stock level into a proper form or directly into the table (you mentioned "cell" which is typical of entering data into a table).

If you are using a form then this is how you could update that field automatically :

Go into the form design, right click the 'stock_level' field (or whatever you have called it) -> click Properties -> Event Tab -> After Update -> Click the ... button to activate the Builder -> Code Builder -> Ok -> enter this (without the two outermost quotes) "[Status] = Iif([stock level] <= [reorder], "Reorder", "Ok") -> Save and close the VB window -> Save the form.

The code should look like this (before you close the VB window):
Be sure to use your actual field names from your form. Any time you change the stock level on the form, then the status will update automatically. You can't use this method when entering data directly into the table.

If you are entering the data directly into the cell (not the preferred method for entering data, but what the hey?) then another approach might be to do this in your query instead. You mentioned that you would run a query to identify the items that need to be reordered, rather than testing for items where the Status = Reorder, you could test for items where the stock_level is less than or equals the reorder level. How? In the query design screen, in the criteria section under the 'stock_level' field, enter this (without the outermost quotes) : "<=[Reorder]"

Again, make sure you use your actual field names. This will return all items that need to be reordered without using the status field.

If you get stuck, post a reply in this thread.

HTH, Andrew

Thank you Andrew it worked a treat. I used the first suggestion as I was working with forms and it was exactly what I wanted it to do. Thanks again
Peter

Thanks heaps for you help.

Just 2 questions, i can do these calculations using multiple tables? for example, a price from table A + a price from table B and have the result record in table C?

Also i followed the directions in the link provided and it works ok but to resolve the equation i have to enter a number in the total box and hit enter for it to display the answer to my equation. Is there a way that is i enter in the value in txtbox1 and txtbox2 and the answer autofills in txtbox3?

Yes you can do the calcs using multiple tables but you need to pull everything together in a single query first. Build a query based on 2 tables (3 if required) and view it. If the asterisk in the record selector is black, you can use it for data entry. If not, you may have to rethink your query design. Always make sure that you include the Primary Key for the "main" table -- the one the from is based on -- and the joining keys that link it to the other tables. Otherwise you'll have a dimmed asterisk and no data entry options.

Denis

Hi Peter
I realised after the event that we should include some basic error checking in the code (e.g. test for null values) so I recommend you change this : to this: What do you want to show both values =0? One way around that is to change the <= to <.
Andrew

Thanks again Andrew for all your help. Kinds Regards Peter