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.  

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
If Not IsNull(Originator) Then
   Calendar2.Value = Originator.Value
   Calendar2.Value = Date
End If
End Sub

Private Sub Calendar2_Click()

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

Open in new window

Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jeffrey CoachmanMIS LiasonCommented:
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.


valmaticAuthor Commented:
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 CoachmanMIS LiasonCommented:
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...

Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
<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.
valmaticAuthor Commented:
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 CoachmanMIS LiasonCommented:
One note though,

There was a post I was involved with a while ago:

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...
valmaticAuthor Commented:
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"
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
End If
End Sub

Open in new window

Jeffrey CoachmanMIS LiasonCommented:
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
Jeffrey CoachmanMIS LiasonCommented:
The other issue here is if you want this to be "dynamic"

Meaning this can be done if they open the record. (on the current event)
But do you also want the date field to be locked as soon as they edit/add
 a date.
...suppose they enter a valid, but wrong date by accident?
If the field is locked after they edit it, then how do they fix mistakes?

(Also, in most systems, if they enter an invalid date, the date remains visible so they can see what they did wrong)

In any event, here is a sample to get you started.

Play around with it and study it carefully

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
valmaticAuthor Commented:
Jeff, this was the perfect solution for me.  Works like a charm.

Thanks and Have a great Holiday. :)
Jeffrey CoachmanMIS LiasonCommented:

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.