Confirm that Date chosen is not in future

[andy49]

I have an unbound textbox

txtDateofIncidentRecorded

which gets its value directly from a date picker. When a date is selected a subroutine

txtdateSelected decides which controls should be visible based on the date selected.

I would like to check whether the chosen date is in the future (this makes no sense to the form in general) and if so informs the user and return the date in the textbox to the current default date (today)



Something tells me the before_update event might be the solution but I cant get it to do what I want.



I tried this as a fifth attempt!!!

Private Sub txtDateofIncidentRecorded_BeforeUpdate(Cancel As Integer)
If Me.txtDateofIncidentRecorded > Date() Then
me.txtdateofIncidentRecorded = date()

MsgBox "Date selected cannot be in the future tense. Date will be reset to today."
Cancel = True
End If
txtdateSelected   'a subroutine which decides which details should be visible based upon the selected date
End Sub

I get an error saying

runtime error 2115 - The macro or function set to the BeforeUpdate or ValidationRule property for this field is preventing Microsoft Office Access from saving the data in the field.

VBA has stopped at

me.refresh

The first line in the

subroutine txtdateSelected

Hoping someone out there can advise me.

[Micron]

try Me.txtDateofIncidentRecorded.Undo after Cancel = True

or is this not happening when the date is in the future, but is otherwise OK?
Then again, undo might be only for bound controls.
You want your routine to run txtdateSelected even if the value gets set to Date?

[isladogs]

Or perhaps add the line Exit Sub after Cancel=True.

[andy49]

Thanks for help Micron. I now have...……

Private Sub txtDateofIncidentRecorded_Change()
txtdateSelected 'this is the sub which runs
End Sub




Private Sub txtDateofIncidentRecorded_BeforeUpdate(Cancel As Integer)
If Me.txtDateofIncidentRecorded > Date Then
'Me.txtDateofIncidentRecorded = Date (This caused an error when I left it uncommented)
MsgBox "Date selected cannot be in the future tense. Date will be reset to today."
Cancel = True
Me.txtDateofIncidentRecorded.Undo
End If
'txtdateSelected   'a subroutine which decides which details should be visible based upon the selected date - commented out 
End Sub

This almost works fine

date yesterday - the subroutine changes what it should
date today - the subroutine changes what it should
date tomorrow - tells me I cant have tomorrow's date (great) but doesn't return the date to today's date. It leaves the last date I put in the date selector.

[andy49]

Tried this too Colin but the date is left at the 29th. Not a disaster but I'd like it returned to 28th (today)

[Micron]

No definitive idea then. The order of events isn't clear; nor is what is going on in the called function. All I can think of at this point is to either select the control and set its text property OR its default value. Not seeing what the called procedure does or knowing the order of events/procedures leaves one guessing somewhat.

[andy49]

skinny1.zip

Posting a skinnier version if this helps

Username is akerr
Password is 12345

Click on the Select class and you'll see an empty sheet (I don't teach on Sundays)

Change to earlier in the week and you'll see I do.

Change the date to tomorrow and you'll see the warning but not the change back to today's date that I'd like to see.

Hope it helps.

[isladogs]

I have only briefly looked at your skinny db

However I have several databases where I prevent future dates and reset them to today's date
For example have a look at the Incident Analysis example app which is one of two apps available at http://www.mendipdatasystems.co.uk/m...ter/4594454290



The date is updated to today's date when the OK button is clicked

In my case, that code runs in the after update event. I have other validation code in the before update event
As you are UK based, I think you will need to format your dates as mm/dd/yyyy ... as I have done.

BTW - did you sort out your timetabling issue from your last thread a month or so ago?

[andy49]

BTW - did you sort out your timetabling issue from your last thread a month or so ago? - yes I did thank you.

This is part of it though. Your process may will solve my current problem though.

[Micron]

difficult to follow your logic, but seeing as how I spent time on it and got beat to the punch I'll post what I came up with as it's one thing to fix it, another to understand the issue for next time (assuming I do).
Your code calls other procedures, one of which invokes Me.Refresh, which automatically causes your unbound control's BeforeUpdate to run - which won't otherwise. You're sort of forcing the situation as on one hand, you're saying "do this before updating" but before that can process, you're trying to force a refresh, which is - you guessed it - updating.

If you move the reset to an AfterUpdate on the control, you can easily set the value to Date if the updated value doesn't pass the smell test. Of course, I would not do this if the control were bound.

[andy49]

Thank you so much to you both. Micron I think this is what you meant...


Used after_update event to test whether datebox was in the future and return it to current date if it was.
Me.refresh in the on_change event to refresh the page.

All sorted now.