Solved

How to aviod enter back date in Access

Posted on 2015-01-26
23
126 Views
Last Modified: 2015-01-27
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
0
Comment
Question by:urjudo
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 10
  • 10
  • 3
23 Comments
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 40570868
try

if me.text1< Date() then
     msgbox "Cannot enter back date!"
     cancel =true
   me.text1.undo
    exit sub
  end if
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 40570870
or

if me.text1< Date() then
     msgbox "Cannot enter back date!"
     cancel =true
   me.text1=Null
    exit sub
  end if
0
 

Author Comment

by:urjudo
ID: 40570980
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 40571006
the original code was

if me.text1< Date() then
     msgbox "Cannot enter back date!"
     cancel =true
  me.text1.setfocus
    exit sub
  end if
0
 

Author Comment

by:urjudo
ID: 40571019
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
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 40571051
post the WHOLE code you are using.

did you save the form?
0
 

Author Comment

by:urjudo
ID: 40571059
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
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 40571072
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
 

Author Comment

by:urjudo
ID: 40571081
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
 
LVL 37

Expert Comment

by:PatHartman
ID: 40571155
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
 

Author Comment

by:urjudo
ID: 40571169
yes, the date control is bound.
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 40571209
@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
 

Author Comment

by:urjudo
ID: 40571223
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
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 40571238
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
 

Author Comment

by:urjudo
ID: 40571280
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
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 400 total points
ID: 40571295
@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
 

Author Comment

by:urjudo
ID: 40571380
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
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 40571434
then your good..
0
 
LVL 37

Assisted Solution

by:PatHartman
PatHartman earned 100 total points
ID: 40571475
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
 

Author Comment

by:urjudo
ID: 40572790
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
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 40572850
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
 

Author Comment

by:urjudo
ID: 40572930
@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
 
LVL 37

Expert Comment

by:PatHartman
ID: 40574076
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

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

729 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question