Automatically update field?

[Nathan Barley]

Hi,

I'm trying to transition a personnel tracking spreadsheet I made into Access. It's my first time using Access, so I'm learning as I am going along, but things are proceeding pretty well so far.

I've ran into a few roadblocks though, and hope someone here can help me.
In my spreadsheet, I had a protected field that denoted the status of that entry, and automatically updated itself with a value depending on what values were entered into certain other fields in that entry by the user.

For example, if someone's name was entered in the "Person Assigned" field, the protected field would update to say "Assigned". If the date the client had requested the assignment commence has past, the field would update to say "Overdue". (Requested Date < Today)

Would I create these values in one of the tables, or is it something that is better left on the form, for Access to calculate on the fly? How would I go about accomplishing this?

Thanks for any help or pointers.

 

[Corticus]

In Access, values that are dependant upon values usually aren't saved, but calculated on the fly.

The reason for this being that anytime you changed the value of a field that affected this calculated field, every record would have to be updated. Instead, you can use a query with an expression field, and have that expression calculate the value based on the other fields (which are stored). Once you've made this query, you can refer to it instead of the source table, and you'll always have that calculated value accesible.

For example, say you had an employee table. Some fields in this table might be: Name, dateHired and dateReleased.

Now say you wanted one more field for "Currently Employed", and it would be a yes/no field. This field would be true if there was no 'date released' false if there was. Instead of adding a field to the employee table and storing this data (which would constantly have to be updated any time you changed the date released field) create an expression field in a query like:
SELECT tblEmp.Name, tblEmp.DateHired, tblEmp.DateReleased, Iif(isnull([dateReleased]),True) AS CurrentlyEmployed
FROM tblEmp;

Now you can refer to this query instead of tblEmp anytime you need to look up this calculated "currently employed" field.

 

[Nathan Barley]

Thank you Corticus, that was a big help. I have another similar question to pose.

I now have a query that gives me a count of the number of entries under each order ID. I want to somehow populate this total on the form, so that at a glance, users can see how many more entries are needed to fill the order.

I have not had much success in getting Access to display the query result for this total in a text box, however. How would I get it to display the total for the current OrderID being viewed?

Thanks

 

[Corticus]

Set the form's recordsource to the query (in the same way that you would set a forms recordsource to a table) that gives the counts. Then set a textbox's control source to the field in the query that displays this count. You won't be able to update the value, since its calculated, but it will be displayed.