Solved

How to aviod enter back date in Access

Posted on 2015-01-26
23
114 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
  • 10
  • 10
  • 3
23 Comments
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 40570868
try

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

Expert Comment

by:Rey Obrero
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
 
LVL 119

Expert Comment

by:Rey Obrero
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 119

Expert Comment

by:Rey Obrero
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 119

Expert Comment

by:Rey Obrero
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 34

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
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 
LVL 119

Expert Comment

by:Rey Obrero
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 119

Expert Comment

by:Rey Obrero
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 119

Accepted Solution

by:
Rey Obrero 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 119

Expert Comment

by:Rey Obrero
ID: 40571434
then your good..
0
 
LVL 34

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 119

Expert Comment

by:Rey Obrero
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 34

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

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
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…

744 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now