Access 2010 expression from form needs to reflect in table

1131 Views 6 Replies 2 Participants Last post by hesykhia, Mar 11, 2016

hesykhia

Discussion Starter · Mar 9, 2016

I am super new to Access (like, what I know I taught myself or learned from some short youtube tutorials a few days ago), but I'm putting together a database for my reporting at work. This is the first time I've really run into an issue.

I am using Access 2010. I have a table called "Holds Table", and a form called "Holds Form". I have a field on both called "Fraud Status Date?", and a field called "Case Age".

"Fraud Status Date?" is, obviously, a date field, and the control source for that text box on my form references the correct field on my table.

"Case Age" is another matter. It has to calculate how many days have passed since the case was opened. Currently, the control source I have for that is "=DateDiff("d",[Fraud Status Date?],Date())", and it's working great.

However, the "Case Age" field in the table is blank, because no text box is pointing to it. I need to have the number from the form show up in the table, so that I can query it.

Any suggestions on how to do this? I'm not super solid with VBA, so if you have suggestions with that, please spell out exactly where I would click and what I would enter where (step by step, because I'm such a n00b). If I'm going about this backwards, or the wrong way, please give me alternatives!

I really appreciate ANY help you can give!

Save

Status: Not open for further replies.

1 - 7 of 7 Posts

OBP

· #2 · Mar 10, 2016

First of all welcome to TSG.
Second well done in getting this far.
Last but least, what you have is the correct way to do what you want.
Let me explain, the case age this month could e say 60 days, if you store that in the table, next month it would be incorrect because it would then be 90 days, but the table would still say 60 days unless you overwrite it each time.
What you have here is a case of Dynamic Data and it should not be stored, but calculated as and when required.
But of course you want to use the this in reports, so the answer is to use your form's formula as a Column Heading of your report Query.

Save

Reactions: 1

hesykhia

Discussion Starter · #3 · Mar 11, 2016

Thank you so much! That has worked, and I'm so happy about it. I was very concerned that I wouldn't be able to figure out a way to query that field.

I do have a follow up question though. You know how I said that expression I have ("=DateDiff("d",[Fraud Status Date],Date())") is working great? Yeah, that isn't correct anymore for some reason.

It does calculate the way it's supposed to, but say I make a mistake in filling out the form, and need to change the Fraud Status Date to a different day. It will change, but the Case Age stays the same. So, basically, it was accurate for the first time I put it in, but doesn't update if I change the date after the fact. With several different people working out of this form, it is very likely to happen that someone puts in the wrong day, and it seems like they would have to start a new entry entirely. I'd like to avoid that if at all possible.

And an additional issue I have, in relation to this, is how to let the date update daily. It seems like it should, since it's "Date()", but I need to make sure that it does, because I need to query based on ">=83", and have it be accurate.

Any words of wisdom on these questions?

See less See more

Save

OBP

· #4 · Mar 11, 2016

You will find that it will update correctly the next time you open the form, however that is a bit disconcerting for the person puting the data in.
To overcome this you can use some VBA in the "After Update" Event Procedure either to "refresh" the Age or you can use VBA to do the calculation for you when the data is input or edited.

The Date() should always update daily as it is system generated.

Save

Reactions: 1

hesykhia

Discussion Starter · #5 · Mar 11, 2016

I have zero experience with VBA in Access. I've tried a bit, and have been fairly unsuccessful with it. Any pointers on the code I would use to 'refresh' it? And, it would go in the "After Update" slot on the "Fraud Status Date" object, right?

Save

OBP

· #6 · Mar 11, 2016

Correct, when you click on it select "Event Procedure" and then press the 3 small dots on the right hand side, that will take you to the correct place to enter the code.
The code would be

me.[Case Age].requery

assuming that Case Age is the name of the field.

Save

Reactions: 1

hesykhia

Discussion Starter · #7 · Mar 11, 2016

OBP - you, sir, are a godsend. THANK YOU SO MUCH.

Save

1 - 7 of 7 Posts

Status: Not open for further replies.

Top Contributors this Month