[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1443
  • 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)PresidentCommented:
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
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
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 MVP, Access and Data Platform)Commented:
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 MVP, Access and Data Platform)Commented:
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

Featured Post

[Webinar] Improve your customer journey

A positive customer journey is important in attracting and retaining business. To improve this experience, you can use Google Maps APIs to increase checkout conversions, boost user engagement, and optimize order fulfillment. Learn how in this webinar presented by Dito.

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