Solved

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

Posted on 2012-03-17
9
1,257 Views
Last Modified: 2012-06-21
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
Comment
Question by:peispud
  • 2
  • 2
  • 2
  • +3
9 Comments
 
LVL 57

Accepted Solution

by:
Jim Dettman (Microsoft MVP/ EE MVE) earned 250 total points
ID: 37732542
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
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 37732545
post the codes you are using and give more info about "bad value"
0
 

Author Comment

by:peispud
ID: 37732594
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
Technology Partners: 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 84
ID: 37732626
You can "undo" it:

Me.Date_Of_Sale.Undo
0
 
LVL 40

Expert Comment

by:als315
ID: 37732632
Try to add after Cancel = True
Me.Undo

postEdit: Sorry, Scott, didn't refreshed and undo all form
0
 
LVL 75

Assisted Solution

by:DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform) earned 250 total points
ID: 37733328
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
 
LVL 84
ID: 37734496
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
 
LVL 75
ID: 37735241
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
 

Author Closing Comment

by:peispud
ID: 37736495
Thank you very much
0

Featured Post

Technology Partners: 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!

Question has a verified solution.

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

Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

679 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