UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome Guest ( Log In | Register )

 
 Reply to this topicStart new topic
> Afterupdate Not Triggering When Use Calendar    
 
   
vladimir.tz
post Mar 31 2012, 12:47 AM
Post #1

UtterAccess Enthusiast
Posts: 59
From: Bosnia-Herzegovina
Hi!
In a date field I have the following AfterUpdate event:

Private Sub Date_AfterUpdate()
On Error GoTo Err_Handler
Dim intNumRecs As String


intNumRecs = DCount("*", "tblCDRs", _
"([ContractID] = " & Me.[ContractID] & ") AND " & _
"([CLIN] = '" & Me.CLIN & "') AND " & _
"([Date] = #" & Me.Date & "#)")

If intNumRecs > 0 Then
MsgBox "CDR for this date is already entered in Database!", vbCritical, "Double Entry!!!"
Me.Undo
End If

If Me.Date < Forms![frmCDR]![FirstOfPOP Start Date] Or Me.Date > Forms![frmCDR]![LastOfPOP End Date] Then
MsgBox "Date entered is outside the CLINs Period of Performance! Check CLIN and your CDR and try again!", vbCritical, "Date Conflict!"
Me.Undo
End If

Exit_Here:
Exit Sub
Err_Handler:
MsgBox Err.Description, vbExclamation, "Error"
Resume Exit_Here
End Sub

It works just fine. Now, I want to add a calendar so users do not have to type the date in the field. The calendar is triggered by double clicking the field. When date is selected via the calendar, value goes in the date field however the afterupdate event do not fire off.

I remember seeing this on forums but can not find it now. Could anyone please help me finding a solution or a workaround. I've been thinking about LostFocus event but it has some drawbacks I think...

Cheers!
V
Go to the top of the page
 
+
arnelgp
post Mar 31 2012, 01:16 AM
Post #2

UtterAccess Ruler
Posts: 1,090
CODE
intNumRecs = DCount("*", "tblCDRs", _
"([ContractID] = " & Me.[ContractID] & ") AND " & _
"([CLIN] = '" & Me.CLIN & "') AND " & _
"([Date] = #" & Me.Date & "#)")


you could check this one:

intNumRecs = DCount("*", "tblCDRs", _
"([ContractID] = " & Me.[ContractID] & ") AND " & _
"([CLIN] = '" & Me.CLIN & "') AND " & _
"([Date] = #" & Format(Me.Date, 'm\/d\/yyyy') & "#)")

yes it was in Lostfocus event of the Date field.
just rename your private Date_AfterUpdate to Date_LostFocus
Go to the top of the page
 
+
vladimir.tz
post Mar 31 2012, 04:25 AM
Post #3

UtterAccess Enthusiast
Posts: 59
From: Bosnia-Herzegovina
QUOTE (arnelgp @ Mar 31 2012, 01:16 AM) *
CODE
intNumRecs = DCount("*", "tblCDRs", _
"([ContractID] = " & Me.[ContractID] & ") AND " & _
"([CLIN] = '" & Me.CLIN & "') AND " & _
"([Date] = #" & Me.Date & "#)")


you could check this one:

intNumRecs = DCount("*", "tblCDRs", _
"([ContractID] = " & Me.[ContractID] & ") AND " & _
"([CLIN] = '" & Me.CLIN & "') AND " & _
"([Date] = #" & Format(Me.Date, 'm\/d\/yyyy') & "#)")

yes it was in Lostfocus event of the Date field.
just rename your private Date_AfterUpdate to Date_LostFocus


arnelgp, could you please explain why I need formatting the field that way?
You suggest I put my code in lost focus right, the whole thing from above? What would happen when a user decides to enter the date manually then close the form without leaving the field? I am going to tweak the code and try the calendar and will let you know.

Thanks for your help, I really appreciate it!
Go to the top of the page
 
+
arnelgp
post Mar 31 2012, 05:02 AM
Post #4

UtterAccess Ruler
Posts: 1,090
Unfortunately Access only recognize English(EN) date format on date manipulation which is in the form m/d/yyyy.
If your code is working and your Regional setting for your date is English, then there is no need to change your code.
The Format() statement is just a safeguard, and will always convert to English date format.

Forget about LostFocus event thing, I was wrong on that one.
It will be difficult to SetFocus to your Date control when we enter our validation here.

You can instead use the onExit event of your control:

CODE
Private Sub Date_Exit(cancel as Integer)
   On Error GoTo Err_Handler
   Dim intNumRecs As String

   If Trim("" & Me.Date) = vbNullString
       '** what will you do if it is blank?
   End If

   intNumRecs = DCount("*", "tblCDRs", _
   "([ContractID] = " & Me.[ContractID] & ") AND " & _
   "([CLIN] = '" & Me.CLIN & "') AND " & _
   "([Date] = #" & Me.Date & "#)")

   If intNumRecs > 0 Then
      MsgBox "CDR for this date is already entered in Database!", vbCritical, "Double Entry!!!"
      Me.Undo
      Cancel = True
   End If

   If Me.Date < Forms![frmCDR]![FirstOfPOP Start Date] Or Me.Date > Forms![frmCDR]![LastOfPOP End Date] Then
      MsgBox "Date entered is outside the CLINs Period of Performance! Check CLIN and your CDR and try again!",              vbCritical, "Date Conflict!"
      Me.Undo
      Cancel = True
   End If

Exit_Here:
   Exit Sub
Err_Handler:
   MsgBox Err.Description, vbExclamation, "Error"
   Resume Exit_Here
End Sub
Go to the top of the page
 
+
vladimir.tz
post Mar 31 2012, 07:29 AM
Post #5

UtterAccess Enthusiast
Posts: 59
From: Bosnia-Herzegovina
Something is not working, I did it as per your instructions and it is keep firing and showing: "CDR for this date is already entered in Database!" (IMG:style_emoticons/default/frown.gif)
I do not understand why it is keep firing when I am not even in the field?
In matter of fact, I did not include the part what to do when the field is empty but still I am not in the field yet, as soon as I open the form or select any contract on the main form (Date is on subform) it starts firing and showing the message.

On the field's DoubleClick I have: =CalendarFor([Date],"CDR Date")
Go to the top of the page
 
+
arnelgp
post Mar 31 2012, 08:16 AM
Post #6

UtterAccess Ruler
Posts: 1,090
sorry about that.
so it is on a subform. Is your subform a datasheet view, Single Form, Continuous?

anyway, now we need to add to this code:
CODE
Private Sub Date_Exit(cancel as Integer)
   On Error GoTo Err_Handler
   Dim intNumRecs As String

   If Trim("" & Me.Date) = vbNullString
       '** what will you do if it is blank?
   End If



CODE
Private Sub Date_Exit(cancel as Integer)
   On Error GoTo Err_Handler
   Dim intNumRecs As String

   Dim strDate$

   strDate = Trim( "" & Me.Date)
   If strDate = vbNullString
       '** what will you do if it is blank?
       '** we say we exit sub because nothing to evaluate
      Exit Sub
   End If
Go to the top of the page
 
+
vladimir.tz
post Mar 31 2012, 08:22 AM
Post #7

UtterAccess Enthusiast
Posts: 59
From: Bosnia-Herzegovina
It is Continuous Form.

Sorry for now giving you all the details in advance.

Thanks for your help. Oh, and I am using English Regional Settings, forgot to tell you that before (IMG:style_emoticons/default/smile.gif)

Go to the top of the page
 
+
arnelgp
post Mar 31 2012, 08:46 AM
Post #8

UtterAccess Ruler
Posts: 1,090
OK, then.
Let us go back and anylize things first.
We dont want to fire this event when the following conditions are true:
1. our control Date is Blank.
2. we have the same value for Date, Forms![frmCDR]![FirstOfPOP Start Date] and Forms![frmCDR]![LastOfPOP End Date]
as before.

CODE
Private Sub Date_Exit(cancel as Integer)
   On Error GoTo Err_Handler
   Dim lngNumRecs As Long

   Static strSavedDate$, strFirstDate$, strLastDate$

   Dim strDate$

   strDate = Trim( "" & Me.Date)
   If strDate = vbNullString
       '** what will you do if it is blank?
       '** we say we exit sub because nothing to evaluate
      Exit Sub
   End If

   If strSavedDate = strDate And strFirstDate = Trim("" &  Forms![frmCDR]![FirstOfPOP Start Date]) And _
         strLastDate = Trim( "" &  Forms![frmCDR]![LastOfPOP End Date] ) Then
      Exit Sub
   End If

   lngNumRecs = DCount("*", "tblCDRs", _
      "([ContractID] = " & Me.[ContractID] & ") AND " & _
      "([CLIN] = '" & Me.CLIN & "') AND " & _
      "([Date] = #" & Me.Date & "#)")

   If lngNumRecs > 0 Then
      MsgBox "CDR for this date is already entered in Database!", vbCritical, "Double Entry!!!"
      Me.Undo
      Cancel =  True

   Else

      If Me.Date < Forms![frmCDR]![FirstOfPOP Start Date] Or Me.Date > Forms![frmCDR]![LastOfPOP End Date] Then
         MsgBox "Date entered is outside the CLINs Period of Performance! Check CLIN and your CDR and try again!",  vbCritical, "Date Conflict!"
         Me.Undo
         Cancel = True
      Else
          '* save these values, so we can check again
          '* if there are no changes for these values then no need to validate
          strSavedDate = strDate
          strFirstDate = Trim("" &  Forms![frmCDR]![FirstOfPOP Start Date])
          strLastDate = Trim( "" &  Forms![frmCDR]![LastOfPOP End Date])
      End if
   End If

Exit_Here:
Exit Sub
Err_Handler:
MsgBox Err.Description, vbExclamation, "Error"
Resume Exit_Here
End Sub
Go to the top of the page
 
+
« Next Oldest · Access Modules · Next Newest »
 

Thank you for your support! Reply to this topicStart new topic

Jump To Forum:
 

Display Mode: Standard · Switch to: Linear+ · Switch to: Outline

Track this topic · Email this topic · Print this topic · Subscribe to this forum

RSS Go to Top  ·  Lo-Fi Version Time is now: 22nd May 2013 - 04:09 AM