Field refresh VBA (1 Viewer)

[Jonny45wakey]

Hi I have one final hurdle left in the development of my MS Access Project Management Database, essentially i have a table displayed in a form (frmUser_DS) which contains the database users, the fields in this table are as follows:-

UserID
UserName
UserLogin
EmpPassword
PCName
UserSecurity
IssueDate
ExpireDate
Flag

When a new user is added the "Issue Date" logs todays date and the After Update code adds 12 months to the "Issue Date" and populates the "Expire Date" field.

Hidden on the form is an unbound text box "txtFlagTime" which has the "=Now()" in the Default value field.

What i am looking to automate using VBA is that when the database is opened the VBA opens the form "frmUser_DS" and as the date updates in "txtFlagTime" the code updates the "UserSecurity" field with "INVALIDATED" if the date in the "txtFlagTime" field has exceeded the "ExpireDate" fields date.

This should be a hidden routine which runs on database opening to validate user expiry date and if their expiry date has passed their user security is set to INVALIDATED

I have code in the "Flag" field under On Got Focus which is:- If Me.Flag.Value = "Expired" Then Me.UserSecurity.Value = 4 4 is the Security ID which = "INVALIDATED"

This works if you click the field manually but i cannot get this to automatically update

Any ideas welcomed

Jonny

 

[Minty]

You can always calculate that value so I question the value of storing it. By calculating it, it is always accurate, no need for any update routine.
You can also always calculate your expiry date if it is a fixed value - 12 months in your case.

So you could move all that data into a query to display the accurate data.

 

[CJ_London]

since this only occurs when the app is opened, you only need =date(), Now includes a time element. And as Minty says, you don't need to pass values from pillar to post. All you need in the form current event is

if expiredate>date() then usersecurity=4

And if your expiry date is always 12 months after issue date, you don't need the expiry date field either

if dateadd("mm",12,issuedate)>date() then usersecurity=4

you might want to store the 12 value if some users only have say 3 months use of the app (so you would store a 3)

 

[gemma-the-husky]

In your example, are you actually changing a control on a form to value 4?
You cant update a value on a table by a simple assignment statement.

 

[arnelgp]

use Query for your form so you can have a Calculated column
that will show "INVALIDATED".

 

[Pat Hartman]

Larry, just because you can do something doesn't mean that you should. Two experts plus me think that there is no reason to store this type of calculated value. Even arnel is on board with not storing the calculated value.

And just FYI, running an action query is always more efficient than using VBA to process row by row in a loop. Queries are optimized to do this kind of thing. VBA code is not.
Plus, Now() is not the same as Date() as has already been pointed out. Using Now() in this case will give inaccurate results.

 

[CJ_London]

And @LarryE, don't want to rain on your parade - but please use the code tags (the </> button) to ensure code indenting is preserved.

although I agree with Pat, I do have some questions about your code - why use double for recnum and index when they can only be long? why use screen.activeform when you are already in it? usually recordcount is not determined until the last record is loaded - does your way get round this?

 

[LarryE]

Answer deleted.