• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1553
  • Last Modified:

microsoft access beforeupdate event --- clear typed entry in field

Hi.

I am using Microsoft Access 2010 VBA

One of the fields is DateOfSale.

I am trying to use the BeforeUpdate event to intercept a bad value.  I would like to clear the entry in the field and cancel the update to force the user to enter the value again from the start.

Appreciate your help.
0
peispud
Asked:
peispud
  • 2
  • 2
  • 2
  • +3
2 Solutions
 
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
In the event:

 Cancel = True
 Me.<name of control>.text = ""

 Also take a look at .SelStart and .SelLength, which allow you to set what appears as selected for editing.

 Rather then clearning the entry, I find it is better to select it so the user can see what they entered, but with one keystorke, they can either wipe it out or keep what they entered and then backup and adjust.  For a date, they'd probably wipe it out.

Jim.
0
 
Rey Obrero (Capricorn1)Commented:
post the codes you are using and give more info about "bad value"
0
 
peispudAuthor Commented:
As requested, here is the code.  Also, this is a field on a continuous form in my database.  I like the idea of having the entire datefield selected.  I will do that.  I am still curious about how to delete user entry in that field.

I cannot seem to do either without pulling an error.



Private Sub Date_of_Sale_BeforeUpdate(Cancel As Integer)
If AcceptableDates(Me.[Date of Sale], Me.[Date of Data Entry], 0, 7) = False Then
Cancel = True
End If


Public Function AcceptableDates(ByVal TheDate As Date, ByVal DateOfDataEntry As Date, DaysInFuture As Integer, ByVal DaysInPast As Integer) As Boolean
If Not IsDate(Nz(TheDate)) Then Exit Function
If DateDiff("d", TheDate, DateOfDataEntry) > DaysInPast Then Exit Function
If DateDiff("d", DateOfDataEntry, TheDate) > DaysInFuture Then Exit Function
AcceptableDates = True
End Function
0
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
You can "undo" it:

Me.Date_Of_Sale.Undo
0
 
als315Commented:
Try to add after Cancel = True
Me.Undo

postEdit: Sorry, Scott, didn't refreshed and undo all form
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
RE:
Me.<name of control>.text = ""

In the Before Update event of a Control, you cannot attempt to change the value of that control or any other control, because it conflicts with the BU event itself.  Note that you can do this - in the Form Before Update event.
The following should work, unless 'something else' is going on:

Private Sub Date_of_Sale_BeforeUpdate(Cancel As Integer)
    If AcceptableDates(Me.[Date of Sale], Me.[Date of Data Entry], 0, 7) = False Then
         Cancel = True
         Me("Date_of_Sale").Undo
         Exit Sub
    End If
End Sub

Public Function AcceptableDates(ByVal TheDate As Date, ByVal DateOfDataEntry As Date, DaysInFuture As Integer, ByVal DaysInPast As Integer) As Boolean
DaysInFuture = False ' Now verify, and set True if all validations pass.
If Not IsDate(Nz(TheDate)) Then Exit Function
If DateDiff("d", TheDate, DateOfDataEntry) > DaysInPast Then Exit Function
If DateDiff("d", DateOfDataEntry, TheDate) > DaysInFuture Then Exit Function
AcceptableDates = True
End Function
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
You can Undo the control in the control's BeforeUpdate event, which is what you've written above (and what als315 and myself suggested earlier). Whether it's better to do so in the Form's BU event would be a matter of style - do you want the user to be alerted to the bad value immediately after they try to enter it, or do you want to be alerted when they try to save the entire record?
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
Huh?
My main point was:

"In the Before Update event of a Control, you cannot attempt to change the value of that control or any other control, because it conflicts with the BU event itself."

mx
0
 
peispudAuthor Commented:
Thank you very much
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 2
  • 2
  • 2
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now