Solved

AfterUpdate/Change/Dirty question

Posted on 2010-11-18
6
393 Views
Last Modified: 2013-11-27
So I have a quick question, I think. Can someone explain to me the difference between these two, or better yet, tell me how to do what I am trying to do. I have a textbox on a form that populates today's date (IssueDate). I have another box that the user puts in the expiration date. On the AfterUpdate event I have some code that checks to see if the ExpirationDate is > 30 days, and if it is, alerts the user that it must be within 30 days. This is all great, however, the alert doesn't happen when the user picks the date, it happens once they click on another control on the form. I want it to happen right away. I have put this code on AfterUpdate, BeforeUpdate, Dirty, and Change, and none of those work. How do I get this to function correctly. Thanks for any help.

Here is my code:

 
Private Sub txtExpDate_AfterUpdate()

Dim daysDiff As Integer

daysDiff = DateDiff("d", Me.IssueDate, Me.ExpirationDate)

If daysDiff > 30 Then

MsgBox "Quality alerts cannot expire more than 30 days from date of issue."

Me.ExpirationDate.Value = ""

'Me.ExpirationDate.SetFocus

End If





End Sub

Open in new window


p.s.  that line Me.ExpirationDate.SetFocus bombs out too, what's up with that?

Thanks again for taking the time to help me out.
0
Comment
Question by:G Scott
  • 2
  • 2
  • 2
6 Comments
 
LVL 4

Expert Comment

by:incerc
ID: 34164282
Hi,

For the differences btw the events, please see :
http://msdn.microsoft.com/en-us/library/ee291976%28v=office.12%29.aspx

Long story short :

- BeforeUpdate and AfterUpdate events for the text box or combo box control occur after you have entered the new or changed data in the control and moved to another control (or clicked Save Record on the Records menu)
- Change and Dirty events occur when the content of the control is changed by typing the data into the control or set the control's Text property (not its value!).

Please try to set the Text property of your textbox and handle the OnChange event.
0
 
LVL 28

Expert Comment

by:omgang
ID: 34164320
What is the name of the text box for IssueDate?  ExpirationDate?

Me.ExpriationDate.SetFocus -- is there a control on the form NAMED ExpirationDate?  From your code it seems that the control NAME is txtExpDate.

When you attempted this using the BeforeUpdate event did you add to your code
If daysDiff > 30 Then
MsgBox "Quality alerts cannot expire more than 30 days from date of issue."
Cancel = True
?

OM Gang
0
 
LVL 1

Author Comment

by:G Scott
ID: 34164748
omgang - yes, you are right, I had my names all messed up.
incerc - I have placed my code on the OnChange event and it works, somewhat.

When I use the DatePicker as soon as I choose the date I can tell it is running the code, however, I am getting and 'Invalid Use of Null' error. I will attach the code that I am using now, forgive me that it has changed a bit from the first post, I have added some msgbox statements to see where/when things are going wrong.  It errors on the line:

theExpirationDate = Me.expDate

 
Private Sub expDate_Change()

Dim theExpirationDate As String

theExpirationDate = Me.expDate

MsgBox Me.theIssueDate

MsgBox theExpirationDate

Dim daysDiff As Integer

daysDiff = DateDiff("d", CDate(Me.theIssueDate), Me.expDate)

If daysDiff > 30 Then

MsgBox "Quality alerts cannot expire more than 30 days from date of issue."

Cancel = True

Me.expDate.Value = ""

Me.expDate.SetFocus

End If

End Sub

Open in new window

I feel like an idiot. I have done this before on other DB's and it's worked fine. I don't know what my problem is today. I guess either too much or not enough coffee.

Thank you both for helping me out. I really appreciate it. QualityAlerts.accdb
0
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 28

Assisted Solution

by:omgang
omgang earned 25 total points
ID: 34165107
The error is due to attempting to assign a Null value to a string variable.  Changing the variable to a Variant data type will stop the error but not fix the problem.  The problem is a value hasn't been assigned to the control expDate at the time this event fires.

More in a bit.

In general it's due to not enough coffee.
OM Gang
0
 
LVL 4

Accepted Solution

by:
incerc earned 100 total points
ID: 34171877
After taking a look on your Access file, I think maybe it's better to use the validation rule / validation text for the control.

Like this:

expDatField -> Properties -> Data -> validation rule : <=DateAdd("d",30,[theIssueDate])  
(the data in the control should not be greater than issueDate + 30 days).

expDatField -> Properties -> Data -> validation text : Quality alerts cannot expire more than 30 days from date of issue.

You don't need event handling this way.
Indeed, the validation rule is checked when the control loses focus, but it cannot let you putting a value that doesn't meet the criteria.

0
 
LVL 1

Author Closing Comment

by:G Scott
ID: 34173065
incerc - I never knew that solution even existed. Thanks for showing me that. It's pretty useful.

omgang - thank you too. I always forget that cancel=true command. That helped me solve another isuse I was having so BONUS!
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

911 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now