By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
446,392 Members | 1,556 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 446,392 IT Pros & Developers. It's quick & easy.

2 Date Controls

P: 69
Using Access '97

Hi there, I have 2 date controls on a form and am wondering if there is a way to ensure that the second control CAN NOT have a date that is earlier than the date entered in the first date control.

EXAMPLE

You enter 07/15/1007 in DATE_A
You enter 07/01/2007 in DATE_B

Thank you VERY much for your assistance
Aug 6 '07 #1
Share this Question
Share on Google+
4 Replies


Scott Price
Expert 100+
P: 1,384
In the afterupdate event of your second date control (assuming the name is Date_B), put this kind of code:

Expand|Select|Wrap|Line Numbers
  1. If Date_B < Date_A Then
  2.   MsgBox ("Please enter a valid date that is later than Date_A", vbYesNo + vbDefaultButton1 + vbApplicationModal)
  3. End If
Remember that line 2 should go all on one line in your code window.

Hope this helps get you a little further down the road...

Regards,
Scott
Aug 6 '07 #2

missinglinq
Expert 2.5K+
P: 3,532
Data validation should always be done in the BeforeUpdate event, not AfterUpdate. In the BeforeUpdate you have the option of setting Cancel = True, which cancels the update and places the cursor back in the offending textbox so that the appropriate correction can be made.

Expand|Select|Wrap|Line Numbers
  1. Private Sub Date_B_BeforeUpdate(Cancel As Integer)
  2.  If Me.Date_B < Me.Date_A Then
  3.   MsgBox "Please enter a valid date that is later than Date_A"
  4.   Cancel = True
  5.  End If
  6. End Sub
  7.  
While placing the validation in the textbox's BeforeUpdate event works when checking the data in the text box against a fixed value, such as say, Date() which would return the current date, checking it against the value of another textbox creates a special problem. What if data is entered in the Date_B textbox before data is entered in the Date_A textbox? The code will fail! In this case, the code needs to go into the Form's BeforeUpdate event.
Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_BeforeUpdate(Cancel As Integer)
  2.   If Me.Date_B < Me.Date_A Then
  3.    MsgBox "Please enter a valid date that is later than Date_A"
  4.    Date_B.SetFocus
  5.    Cancel = True
  6.   End If
  7.  End Sub
  8.  
Now when Access goes to save the record, the check will be made and appropriate action can be taken.

Scott, your line:
Expand|Select|Wrap|Line Numbers
  1. MsgBox ("Please enter a valid date that is later than Date_A", vbYesNo + vbDefaultButton1 + vbApplicationModal)
has a problem. By including vbYesNo + vbDefaultButton1 you're telling Access to expect the user to make a choice, pressing either Yes or No. This isn't applicable here; the user has no choice except to to enter an acceptable date.

Whenever this button is included in a messagebox, the code has to be preceded by assigning the results of the messagebox to an object, i.e.
Expand|Select|Wrap|Line Numbers
  1. resp = MsgBox ("Would you like to save this record?", vbYesNo + vbDefaultButton1 + vbApplicationModal)
  2. If resp = vbYes Then
  3.  'Code to save the record goes here
  4. Else
  5.  'Code to dump the record goes here
  6. End If 
The + vbApplicationModal is really unneccessary, as it is the default, and just adds clutter to the code.

Linq ;0)>
Aug 6 '07 #3

Scott Price
Expert 100+
P: 1,384
Linq,

You're right, of course... Sorry for the wrong bit of code. I guess I shouldn't post a piece while I'm still foggy from sleep!

I also wasn't aware putting the data validation in the beforeupdate event... Good call, I learn something too...

Thanks!

Regards,
Scott
Aug 6 '07 #4

P: 69
Linq,

You're right, of course... Sorry for the wrong bit of code. I guess I shouldn't post a piece while I'm still foggy from sleep!

I also wasn't aware putting the data validation in the beforeupdate event... Good call, I learn something too...

Thanks!

Regards,
Scott

Thank you VERY much, will try this out. Have a NICE day. :)
Aug 7 '07 #5

Post your reply

Sign in to post your reply or Sign up for a free account.