I did a query in access that calculates a date, when I enter the date in a form I get the result in-CodePudding

Pic hereI am new to MS Access. I have a customer table with creationdate field as well as submissiondate. The submissiondate was calculated via a query since it carries multiple conditions. I created a form from the customer table where I am able to input the creationdate field and I managed to show the submissiondate (from the query) via Dlookup but the date is not recorded in the customer table. How can I record the submissiondate value from the query in the customer table without having to do an update query every time I add a new customer? I did an update query but it updates all records every time it runs, and we have more than 50K customers. Any help is appreciated.

this is the query in Access that gets the result for me.

SELECT HardDeadlineCalculationQ1.ID, HardDeadlineCalculationQ1.R AS ReferralDate, HardDeadlineCalculationQ1.[Source of Referral], HardDeadlineCalculationQ1.HD1, HardDeadlineCalculationQ1.wdhd1, HardDeadlineCalculationQ1.hd2, HardDeadlineCalculationQ1.wdhd2, HolidaysT.holidaydates, Switch([HardDeadlineCalculationQ1].hd2=holidayst.holidaydates,"Yes") AS isholiday, IIf(isholiday="Yes",([HardDeadlineCalculationQ1.hd2]-1),[HardDeadlineCalculationQ1].hd2) AS hd3, WeekdayName(Weekday(hd3)) AS wdhd3, Switch(WeekdayName(Weekday(hd3))="Monday",(hd3),WeekdayName(Weekday(hd3))="Tuesday",(hd3),WeekdayName(Weekday(hd3))="Wednesday",(hd3),WeekdayName(Weekday(hd3))="Thursday",(hd3),WeekdayName(Weekday(hd3))="Friday",(hd3),WeekdayName(Weekday(hd3))="Sunday",(hd3-2)) AS hd4, WeekdayName(Weekday(hd4)) AS wdhd4 FROM HardDeadlineCalculationQ1 LEFT JOIN HolidaysT ON HardDeadlineCalculationQ1.hd2 = HolidaysT.holidaydates;

regards,

CodePudding user response：

"The submissiondate was calculated via a query since it carries multiple conditions"

By this, i presume you mean that the submission date is a calculated field(as its control source)

Do the following Copy the calculation, i.e formula and put in vba code, under the after update event of every field that is a variable in the expression. e.g submissiondate= place the formular here

Then on the form remove the calculation , i.e formula from the control source of the form control and make a field in the table the control source, e.g submissiondate.