Calendar Control - Stop Future Dates?

[andy_dyer]

Hi,

I have a calendar that enables me to set the date when used for several different fields.

I would like to help control my users by restricting their ability to input a date in the future.

The only problem is that I would like this to only be used for one field and for the rest of the fields that rely on this calendar to be unaffected.

Has anyone got any ideas on how I can best do this and what vba code I would need to restrict future dates?

Thanks

Andy

 

[IMO]

You could try something like this in the LostFocus event of your TextBox

Private Sub YourDateField_LostFocus()

If Me.YourDateField > Date Then
    MsgBox "Your Message"
    Me.AnotherControl.SetFocus
    Me.YourDateField.SetFocus
End If

End Sub

IMO

 

[andy_dyer]

Thanks IMO,

However this still allows any date to be selected.

My code now is;

Private Sub txtReferralReceivedDate_LostFocus()

If Me.txtReferralReceivedDate > Date Then
DoCmd.OpenForm "frmError5"
Me.cmdRefRecd.SetFocus
End If

End Sub



Me.txtReferralReceivedDate is the text box with the date

Me.cmdRefRecd is the command button for the calendar

Thanks

Andy

 

[Rich]

Use the Before Update event of the control to canel the update

 

[andy_dyer]

So if I understand right...

You want me to move the code from Lost Focus to Before Update?

If so I've tried that now as well

Private Sub txtReferralReceivedDate_BeforeUpdate(Cancel As Integer)

If Me.txtReferralReceivedDate > Date Then
DoCmd.OpenForm "frmError5"
Me.cmdRefRecd.SetFocus
End If

End Sub

Still allows me to input anything...

Thanks

Andy

 

[IMO]

You'll have to code the calendar itself telling it to disable the buttons with a greater date than today when it's selected for the "txtReferralReceivedDate" field.

IMO

 

[Rich]

Private Sub txtReferralReceivedDate_BeforeUpdate(Cancel As Integer)

If Me.txtReferralReceivedDate > Date Then
Cancel = True
DoCmd.OpenForm "frmError5"
Me.cmdRefRecd.SetFocus
End If

End Sub

 

[andy_dyer]

Thanks you guys for your help,

IMO's idea sounds good but I have no idea how to do the coding!

Rich, the new code still allows any date to be input on my form!

I think I have managed to "bodge" this by creating a duplicate calendar with no future dates allowed and setting the command button to point to the new calendar instead.

I know this isn't pretty, and that I don't really want to have duplicate forms, but this appears to work!

I am still open to new ideas, as I am trying to keep my database as compact as possible.

Thanks again for your suggestions.

Andy

 

[IMO]

Using some of the code from a past example from Mile, Ive messed it about a bit and come up with this.

IMO

 

[andy_dyer]

Thanks IMO,

That worked a treat!!

Andy

 

[kupe]

Looks really good, IMO, and useful. Cheers

 

[IMO]

Gald you like it, but we can't forget Mile as he coded the original, I just played about with his code.

IMO

 

[kupe]

Oddly, I felt there were touches of his mastercraftsmanship in there too. Many thanks, Mile, too for the hard work. (Though perhaps to you geniuses, it's not hard work at all. As a very recent example, did you see his immediate answers to sarahdaws on
http://www.access-programmers.co.uk/forums/showthread.php?threadid=54415) Cheers and thanks, gentlemen.

 

[andy_dyer]

Help! I've discovered a problem!

Hi,

This has all worked fine except my calendar is accessed from more than one form...

Whenever I now try to access it from frmReportDates it says it cannot find frmInput and from looking at the code I can see that it needs to be able to!

Is there anyway I can add into this code to allow full access to all dates from frmReportDates?

Thanks

Andy

Here is the code that works for my calendar from frmInput...

Private Sub ctlCalendar_Click()

On Error GoTo Err_ctlCalendar_Click

If Forms!frmInput.pbooClickTest = True And ctlCalendar > Date Then
DoCmd.OpenForm "frmError5"

Else
SetAndClose
Forms!frmInput.pbooClickTest = False
End If

Exit_ctlCalendar_Click:
Exit Sub

Err_ctlCalendar_Click:
MsgBox Err.Description, vbExclamation, "Error #" & Err.Number
Resume Exit_ctlCalendar_Click

End Sub

 

[Mile-O]

Here's another calendar idea which is even easier to implement.

 

[andy_dyer]

That doesn't do what i need it to tho and stop people selecting future dates...

I need to access the calendar about 6 times from frmInput and twice from frmReportDates.

The demo that IMO posted which was an amended version of one of ur demos enabled me to for one of my selections from frmInput to not allow future dates but for the rest to be fine.

This worked ok for frmInput, but has stopped the calendar working completely for frmReportDates.

I am trying to fathom out how I can tweak the code to allow dates from frmReportDates as tho the "pbooClickTest = False" applies....

Any ideas?

Thanks

Andy

 

[Mile-O]

A few extra lines into that calendar and:

 

[andy_dyer]

I still cannot see in that example how can i can specify one rule for one command button on one form and a different rule for the other 5 buttons on that form and two other buttons on a different form?

I am trying to wangle my own way through the code and have got here so far:

Private Sub ctlCalendar_Click()

On Error GoTo Err_ctlCalendar_Click

If Forms!frmInput.pbooClickTest = True And ctlCalendar > Date Then
DoCmd.OpenForm "frmError5"
End If

If Forms!frmInput.pbooClickTest = False Then
SetAndClose
Forms!frmInput.pbooClickTest = False
End If

If Forms!frmReportdates.pbooClickTest = False Then
SetAndClose
Forms!frmReportdates.pbooClickTest = False
End If

Exit_ctlCalendar_Click:
Exit Sub

Err_ctlCalendar_Click:
MsgBox Err.Description, vbExclamation, "Error #" & Err.Number
Resume Exit_ctlCalendar_Click

End Sub

But it still keeps falling over on the frmInput as it is not open due to me accessing the calendar from frmReportDates.

Thanks

Andy

 

[andy_dyer]

Thanks for your help guys,

I have managed to "bodge" this by creating a copy of my calendar and having different rules for each one and then pointing the right command button at the right calendar...

Not ideal I know, but it is all working as it should at least!

Thanks

Andy

 

[Mile-O]

Andy, the calendar2 example I posted can stop future dates and, because you send it the name of the calling form, you can set rules using the the old IF...THEN...ELSE...END IF structure.

It would just require a small bit of modification.