Forum home » Delegate support and help forum » Microsoft Access Training and help » Form calculated fields to update main table
|
|
|
The UK's most regular Training information: See also: |
|
Welcome. Please choose your application (eg. Excel) and then post your question. Our Microsoft Qualified trainers will then respond within 24 hours (working days). Frequently Asked Questions
Any suggestions, questions or comments? Please post in the Improve the forum thread. |
Form calculated fields to update main table
Resolved · High Priority · Version 2003
Reply Thu 7 Oct 2010, 11:12Delegate Harriet said...
Harriet has attended:
Access Introduction course
Access Intermediate course
Access Advanced course
Form calculated fields to update main table
I am creating a table with validations and rules, ready for new data entry. I am then going to create a form to sit on top of this table, so that other users can enter the data themselves.
However i need all data in the form to update in the main table.
I need to add a few calculated fields into the form, but i need these calculations to be stored in the main table. How do i do this?
I have a field called 'DateReceived', when this date is entered, i need the next field 'DeadlineDate' to automatically update to 21 days after 'DateReceived', i then need this value to be stored in the main table.
Please Help
For upcoming training course dates see: Pricing & availability
RE: Form calculated fields to update main table
Hello Harriet,
Hope you enjoyed your Microsoft Access courses with Best STL.
Thank you for your question regarding using calculated fields within forms. The first thing you need to do is create all the calculated fields on the data sheet table itself. Then when you create the form you will have access to the calculated fields which are in the data sheet, together with all the other fields.
I hope this resolves your question. If it has, please mark this question as resolved.
If you require further assistance, please reply to this post. Or perhaps you have another Microsoft Office question?
Have a great day.
Regards,
Rodney
Microsoft Office Specialist Trainer
Reply Fri 8 Oct 2010, 11:52Delegate Harriet said...
RE: Form calculated fields to update main table
Thank you for your help!
Iv had a look on the internet and alot of guides have told be not to create a calculated field in a table.
Essentially in the form, when I enter a value in the 'DateReceived' box, and tab to the 'DeadlineDate' box, i want the value to automatically update to 21 days later.
But i want this value to be permentanly stored in the main table.
I cant create a 'calculated' field in the table, only in a query? is that right? but i dont want to have to run the query everytime i enter a new date into the form.
If i create the empty field in the main table 'Deadline Date', as a standard date format, then import it into the form, and make the expression in that field in the form, will this translate to the main table?
Sorry this is long!!!
RE: Form calculated fields to update main table
Hi Harriet, this is quite knotty problem to advise on from afar! However:
1) Create the calculated field on the form itself, not the table;
2) Create an update query that takes the new data from the form and writes it to the appropriate record on the table;
3) Trigger that update query either via a command button on the form, or by coding detection of a change in your "Deadline Date" control. This last option will cause the changes to be made without any other input from the user but will require knowledge of VBA to do implement.
Hope this helps,
Anthony
Tue 8 Feb 2011: Automatically marked as resolved.
|