Forum Discussion
Updating a field in a Form 1 using button in Form 2
Hi!
I'm trying to update the data in a field in my Form 1 by clicking a button in Form 2. Total disclosure: I'm not good at coding. Whatever codes I got, it's from Google.
So, in Form 1, I have a code that computes the number of working days between the time I started a concern (StartDate) and the time I resolved it (ResolveDate). This data is logged in my DTR field and is triggered thru an If-Then code in VBA embedded in a button as an OnClick event. Now, sometimes, I have to update a lot of records, so I created an update query that would change the ResolveDate. This can be executed thru another button OnClick event, but this time, the button is found in my Form 2.
Unfortunately, this does not update the DTR field since the code to compute the difference is embedded in a separate code. My question now is, is there a way to call the If-Then code to my button in Form2 so that when I click on it, both the ResolveDate and DTR field will be updated in my Form 1?
Thank you!
4 Replies
The relational way is to NOT store DTR, and rather calculate it in a query.
Why? Because it depends fully on StartDate and ResolveDate, which ARE stored, and in a relational database we don't store data that is fully dependent on other stored data.
Why? Because if one field gets updated, the dependent field needs to be updated as well, and if that does not happen, we have illogical data.
So create a function that takes two dates and returns the number of business days between them. Let's call this function Fred for now. Put it in a Standard Module, and declare it as a Public Function.
Then in a query:
select [somefields], Fred(StartDate, ResolveDate) as DTR from [sometable];- Hi, thank you for the info, that did make sense. I actually do have a function that returns the number of working days between StartDate and ResolveDate, and this is the value that gets thrown into DTR. And yes, what you said is the problem that I have now haha.
If you don't mind, just a few clarifications on the query. What type of query would it be? Also, what is in [somefields]? I'm assuming [sometable] is the database I'm currently using.
Thank you!- This is a SELECT query (as the first word in the SQL statement suggests :-)).
With [somefields] I mean you would select some fields to be returned by your select query.
With [sometable] I mean the table you select from (the one with StartDate and ResolveDate).
