Solved

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

Posted on 2012-03-17
9
1,236 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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
LVL 84
ID: 37732626
You can "undo" it:

Me.Date_Of_Sale.Undo
0
 
LVL 39

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 - Access MVP)
DatabaseMX (Joe Anderson - Access MVP) 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

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

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

Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
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…
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.

821 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