asked on

Help with Access VBA Logic

Hi. I've been using the attached code to create drop down calendar functionality. I now need to build in logic to keep users from changing a date if the field is already filled and ensure they enter a date > or = current date.

Something like :
if isnull (fieldA) then
(Choose date from calendar)
else: msg "You can't change this date"...
end if

adn then also test on lost focus if date is less than now()?

I've tried playing around with this myself and am having no luck.

thanks

Private Sub Section_III_Due_Date_MouseDown(Button As Integer, Shift As Integer, X As Single, Y As Single)

Set Originator = FieldA
Calendar2.Visible = True
Calendar2.SetFocus
If Not IsNull(Originator) Then
   Calendar2.Value = Originator.Value
Else
   Calendar2.Value = Date
End If
End Sub

Private Sub Calendar2_Click()

Originator.Value = Calendar2.Value
Originator.SetFocus
Calendar2.Visible = False
Set Originator = Nothing    
End Sub

Open in new window

Jeffrey Coachman

It is always a challenge to do things like this with mouse click events.
Because the user can always opt to navigate the form without the mouse...

Also selecting the most appropriate mouse event is also critical..

What I do is keep it simple, and trigger the calendar off small button.
This way coding is simple and straightforward to do almost anything you might want.

;-)

Jeff

valmatic

ASKER

Boag, That's a great tip and I took your advice and found the calendar tool is now part of the product. I created this thing back in office 2000 and now in office 2010 don't even need the mouse down calendar event any more. go figure. Thanks for the kick in the butt though :P

Jeffrey Coachman

No sweat.

I thought you needed the control for legacy support.

The issue with the built in control is that it is very hard to navigate across years and months...

Jeff

Scott McDaniel (EE MVE )

<The issue with the built in control is that it is very hard to navigate across years and months...>

And it's hard to interact with it programatically - the events that you expect to fire when the user selects a date (like Change or AfterUpdate) don't fire, so you have to come up with some odd solutions at times.

Still it's a great addition to Access. Just wish they'd extend it a bit.

valmatic

ASKER

That's good to know. I was working on recoding my forms and have not tested yet so hopefully it all works as intended. I'm going to leave this open for a couple more days in case I have a related issue but I think I'm on the right track now...

Jeffrey Coachman

One note though,

There was a post I was involved with a while ago:
https://www.experts-exchange.com/questions/26900544/Display-Date-Picker-in-Access.html

And you "could" use a hotkey to navigate months/years
Shift+Left Arrow

...but it does not seem to work now...?

Perhaps it's a Service pack issue...

Who knows...

valmatic

ASKER

I think I can work with the built in calendar control but I'm still having trouble with this code.

Works fine if field is blank. User enters a date < = to today then they get a warning and the field blanks out and they are foced to fill it with a future date. The problem is when it is already filled and the user wants to change this date after they are late. User gets focus on the field and gets hit with the warning they can't change it but when they lose focus they also get the warning that the date is <= to today and the field blanks out losing the original date. This would be fine if I could retain the original date that was entered in this case. Any ideas?

Private Sub Section_III_Due_Date_GotFocus()
If (Not IsNull(Section_III_Due_Date)) And (currentuser() <> "john" And currentuser() <> "bob") Then 
    MsgBox "You are not allowed to change this date.  Review with BobI or JohnB" & _
    " to change the date if necessary.", , "Warning"
        
    Ass2.SetFocus
End If
End Sub

Private Sub Section_III_Due_Date_LostFocus()
If Section_III_Due_Date < Now() Then
    MsgBox "This date cannot be less than the current date.", , "warning"
    Section_III_Due_Date = Null
    Ass2.SetFocus
End If
End Sub

Open in new window

Jeffrey Coachman

Again, this is all more complicated when you start wanting to use events like OnFocus and LostFocus.

I work up a sample a bit later...

Perhaps LSM will chime in and assist before then

ASKER CERTIFIED SOLUTION

Jeffrey Coachman

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

valmatic

ASKER

Jeff, this was the perfect solution for me. Works like a charm.

Thanks and Have a great Holiday. :)

Jeffrey Coachman

oK
Great!

;-)