How to aviod enter back date in Access

Hi Experts,
I just asked this question last Friday (1/23/15) the ID# is 28602869 (the title is "How to block date field enter back date").  I got the answer from Rey Obrero but still have one ore question.
The code is doing what I need on the date field but the only problem I notice this morning was if user(s) enter the back date and they want to exit out the form (I have a Cancel button on the form for user(s), normally user users can click Cancel to undo everything), the message still showing unless they removed the date, is anyway that when user(s) click the Cancel button on the form, the message will not show.  I did try to put :
 if me.text1< Date() then
     msgbox "Cannot enter back date!"
     cancel =true
    text1 = ""
    exit sub
  end if
 but I got error message.

 Thanks
urjudoAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
Rey Obrero (Capricorn1)Connect With a Mentor Commented:
@urjudo
if you have problem like this, post the codes related to your problem.
don't wait for experts to ask you to post the codes.

paste the code in your Close button
0
 
Rey Obrero (Capricorn1)Commented:
try

if me.text1< Date() then
     msgbox "Cannot enter back date!"
     cancel =true
   me.text1.undo
    exit sub
  end if
0
 
Rey Obrero (Capricorn1)Commented:
or

if me.text1< Date() then
     msgbox "Cannot enter back date!"
     cancel =true
   me.text1=Null
    exit sub
  end if
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
urjudoAuthor Commented:
Hi,
I tried the me.text1.undo, after I click the message the date field is empty which is difficult for user to see what they enter before.

I also tried the me.text=null, I got this message" The macro or function set to the BeforeUpdate or validation rule property for this field is preventing Employee (name of my database) from saving the data in the field.

What I want to do is even the user(s) enter a back date then message pop up, the click ok, then they can click Cancel exit out of the form with out the message pop up again until the remove the date to avoid the message again then click Cancel to exit out.
0
 
Rey Obrero (Capricorn1)Commented:
the original code was

if me.text1< Date() then
     msgbox "Cannot enter back date!"
     cancel =true
  me.text1.setfocus
    exit sub
  end if
0
 
urjudoAuthor Commented:
sorry, the error message for the me.text1.setfocus is:
"Run-time error "2108", you must save the field before you execute the GoToControl action, the GoToControl method, or the SetFocus Method"
0
 
Rey Obrero (Capricorn1)Commented:
post the WHOLE code you are using.

did you save the form?
0
 
urjudoAuthor Commented:
This is the date field:
    Private Sub Text1_BeforeUpdate(Cancel As Integer)
         If Me.Text1 < Date Then
            MsgBox "can not enter back date!"
            Cancel = True
            'Me.Text1 = Null
            Me.Text1.SetFocus
           Exit Sub
         End If
   End Sub

This is the Code for the Cancel Button:
    Private Sub Command96_Click()
   On Error GoTo Err_Command96_Click

      If Me.Dirty = True Then
          Me.Undo
       End If

      DoCmd.Close

   Exit_Command96_Click:
     Exit Sub
 
   Err_Command96_Click:
      MsgBox Err.DESCRIPTION
      Resume Exit_Command96_Click
  End Sub
0
 
Rey Obrero (Capricorn1)Commented:
what happen if you just use

    Private Sub Text1_BeforeUpdate(Cancel As Integer)
         If Me.Text1 < Date Then
            MsgBox "can not enter back date!"
            Cancel = True

           Exit Sub
         End If
   End Sub
0
 
urjudoAuthor Commented:
Same message pop up "Can not enter back date" when I click "Cancel  button" or "Close Button" unless I removed the date then both buttons can be close.  Sorry
0
 
PatHartmanCommented:
Your Cancel button code should back out the pending change.  Put a stop in the code to make sure it is running.
Also, is the date control bound?  I don't think the form tests dirty if only unbound controls have modified values.
0
 
urjudoAuthor Commented:
yes, the date control is bound.
0
 
Rey Obrero (Capricorn1)Commented:
@urjudo
remove the codes from the beforeUpdate event and use the afterUpdate event of the textbox

    Private Sub Text1_afterUpdate()
         If Me.Text1 < Date Then
            MsgBox "can not enter back date!"
           Exit Sub
         End If
   End Sub

test also the cancel and close button
0
 
urjudoAuthor Commented:
Hi Rey Obrero,
I moved the code to afterupdate, the cancel and close button both work fine.  still one problem is if I enter 1/23/15, the message came up but still allow user to save the record instead they must be change the date before save the record. (sorry for keep having all this problems)
0
 
Rey Obrero (Capricorn1)Commented:
now,

try using the beforeUpdate event of the form (remove the codes from the afterupdate event of text1)

private sub form_beforeupdate(cancel as integer)
     If Me.Text1 < Date Then
            MsgBox "can not enter back date!"
            cancel=true
            me.text1.setfocus
           Exit Sub
         End If


end sub
0
 
urjudoAuthor Commented:
ok, here is the result after I paste the code from AfterUpdate event of text1 to BeforeUpdate event of the form:
Cancel button has no problem
Close Button pop up the message then closed the form and did not save anything.  we need to be still on the form if the date is back date so that user(s) can change the date.
0
 
urjudoAuthor Commented:
Hi Rey Obrero,
I got it works by putting the same code on BeforeUpdate event of the Form and on the close button, so if the user(s) entered a back date on the date field, click the Cancel button then the form is close without save and any message, if user(s) click Close Button, then the message pop up and the form still open unless user(s) either changed the date or click Cancel to exit out.
0
 
Rey Obrero (Capricorn1)Commented:
then your good..
0
 
PatHartmanConnect With a Mentor Commented:
There is a quirk in Access forms that you have run into.  If you initiate the close event before the record has been saved, Access will give you the appropriate error messages and not save the bad data but the form will close anyway.

You have almost stumbled on the correct solution.  Do NOT duplicate the code.  That just causes you a maintenance problem.  Leave all the validation code in the form's BeforeUpdate event.  Then in your close button, force the record to save PRIOR to closing the form.

If Me.Dirty Then
    DoCmd.RunCommand acCmdSaveRecord
End If

Luckily, you also discovered that you NEVER put validation code in the "After" events.  You may get error messages but it is too late since the data is ALREADY saved at that point so the bad data is already in the table.
0
 
urjudoAuthor Commented:
I just tried to removed the code on the form beforeupdate event and only put the code on the close button and test it, it seems works the same as I put the code on both.
0
 
Rey Obrero (Capricorn1)Commented:
there you go.. that is a way of learning how to optimize your codes.

i think this link will be very useful to you. Responding to Events

good luck with your project. ;-)
0
 
urjudoAuthor Commented:
@Rey Obrero,
Thanks so much for your patience, and time.  Every comment from you are very helpful, I wish I know access as much as you :)

@PatHartman,
 Thanks so much for your explanations.
0
 
PatHartmanCommented:
I just tried to removed the code on the form beforeupdate event and only put the code on the close button and test it, it seems works the same as I put the code on both.
It does not.  
For example, try scrolling to the next record.  The previous record will be saved without running your code.  Click the "x" to close the form or the big "x" to close Access, the record will be saved without running your code.  If the form has a subform, click into it or vice versa.  Now, it is possible to plug all the holes but why?  Why would you not simply use the event that MS intended for the purpose?  It's like using a screwdriver to drill a hole.  You can do it, my husband has.  But why?

I will bet $1,000 donated to a charity of your choice that I can save bad data to the table with your validation code being only in the click event of your "close" button.  Are you willing to take the challenge?
0
All Courses

From novice to tech pro — start learning today.