Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2012-03-17
9
Medium Priority
?
1,368 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 58

Accepted Solution

by:
Jim Dettman (Microsoft MVP/ EE MVE) earned 1000 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
Independent Software Vendors: 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 85
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 1000 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 85
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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

876 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