Date Picker Stops Working

[CharlesWhiteman]

Hi, I'm using Access 2007 and on a Form I have two date fields, txtTaskStartDate & txtTaskEndDate.

The code behind my form runs a few sql strings to enter data into tables, refresh some listboxes and then some code which sends an emil notification of a task created.

However, I've noticed (typically when there has been some kind of background error) that then when the form is used again to create a task the date picker doesnt work.

In the case I am testing now, there has been no error. The date pickers work again when the application has been closed and re-opened.

Any advice?

 

[RuralGuy]

That behavior is by design for unhandled errors. You need more Error handling in your application.

 

[CharlesWhiteman]

Hi Rural, I'm using error handline code which is supposed to catch any unhandled errors - but none are highlighted.

Here's the code behind the command. As a note re my email code, errors to do with missing information or inability to contact the server are handled.

Private Sub cmdSaveWorkflow_Click()
On Error GoTo err_handler
If IsNull(Me.txtTaskDueDate) Then
MsgBox "When does this task need to be completed by?", vbCritical, "Form Completion"
Me.txtTaskDueDate.SetFocus
Exit Sub
Else
End If
If IsNull(Me.txtTaskWorker) Then
MsgBox "Who this Task Is For?", vbCritical, "Form Completion"
Me.txtTaskWorker.SetFocus
Exit Sub
Else
End If
If IsNull(Me.txtTaskTitle) Then
MsgBox "What is this task?", vbCritical, "Form Completion"
Me.txtTaskTitle.SetFocus
Exit Sub
Else
End If
Me.txtTaskDetail.SetFocus
Me.cmdSaveWorkflow.enabled = False
Me.txtNotification.visible = True
'Enter The Enquiry Data Into TblHistory
Dim strSQLWorkflow As String
strSQLWorkflow = "INSERT INTO TblTasks (EnquiryID, PrimaryDataID, JobNumber, TaskTitle, TaskWorker, TaskManager,TaskDetail, TaskStartDate, TaskDueDate, DateCreated, CreatedBy)"
strSQLWorkflow = strSQLWorkflow & " VALUES(txtEnquiryID, txtPrimaryDataID, txtJobNumber, txtTaskTitle, txtTaskWorker, txtTaskManager, txtTaskDetail, txtTaskStartDate, txtTaskDueDate, txtDateCreated, txtCreatedBy)"
    DoCmd.SetWarnings False
DoCmd.RunSQL strSQLWorkflow
Forms!FrmPrimaryData.LbTasks.Requery
    DoCmd.SetWarnings True
 
'Mail Notification Code
Dim strExchangeIPAddress As String
strExchangeIPAddress = DLookup("ExchangeIPAddress", "TblVariablesSystem")
Dim strSendUserName As String
strSendUserName = DLookup("WindowsName", "TblUsers", "UserID=Forms!FrmSplashScreen.txtDbUserId")
Dim strSendUserPW As String
strSendUserPW = DLookup("MailPassword", "TblUsers", "UserID=Forms!FrmSplashScreen.txtDbUserId")
Dim strSendEmailAddress As String
strSendEmailAddress = DLookup("MailEmailAddress", "TblUsers", "UserID=Forms!FrmSplashScreen.txtDbUserId")
Dim strMmsgTo As String
strMmsgTo = DLookup("MailEmailAddress", "TblUsers", "UserID =" & Me.txtTaskWorker)
Dim strMsgBody As String
strMsgBody = Forms!FrmSplashScreen.txtCurrentUser & " has created a task for you in Omniscient" & vbCrLf & vbCrLf & "Task Details:" & vbCrLf & vbCrLf _
& "Job Number: " & Me.txtJobNumber & vbCrLf & "Task Due Date: " & Me.txtTaskDueDate & vbCrLf & "Task Title: " & Me.txtTaskTitle.Column(1) & vbCrLf & "Task Detail: " & Me.txtTaskDetail
 Dim iCfg As Object
    Dim iMsg As Object
    
    Set iCfg = CreateObject("CDO.Configuration")
    Set iMsg = CreateObject("CDO.Message")
    With iCfg.Fields
        .Item("[URL]http://schemas.microsoft.com/cdo/configuration/sendusing[/URL]") = 2
        .Item("[URL]http://schemas.microsoft.com/cdo/configuration/smtpserverport[/URL]") = 25
        .Item("[URL]http://schemas.microsoft.com/cdo/configuration/smtpserver[/URL]") = strExchangeIPAddress
        .Item("[URL]http://schemas.microsoft.com/cdo/configuration/smtpauthenticate[/URL]") = 1
        .Item("[URL]http://schemas.microsoft.com/cdo/configuration/sendusername[/URL]") = strSendUserName
        .Item("[URL]http://schemas.microsoft.com/cdo/configuration/sendpassword[/URL]") = strSendUserPW
        .Item("[URL]http://schemas.microsoft.com/cdo/configuration/sendemailaddress[/URL]") = strSendEmailAddress
        .Update
    End With
    With iMsg
        .Configuration = iCfg
        .Subject = "Omniscient Task Notification"
        .To = strMmsgTo
        .TextBody = strMsgBody
        '.AddAttachment "C:\whatever.csv"
        .Send
    End With
ExitHandler:
    Set iMsg = Nothing
    Set iCfg = Nothing
    DoCmd.Close
    Exit Sub

err_handler:
Call LogError(Err.Number, Err.Description, "cmdSaveWorkflow()")

 

[CharlesWhiteman]

The code executes properly, the history record is created, its just hte date picker field which is the issue.

 

[CharlesWhiteman]

I've tested the command without the email notification code and the issue doesnt arise.

Although the mail notification code works perfectly?

 

[CharlesWhiteman]

I solved the issue by putting the email notification code into a separate module and then calling it within my existing code.

 

[CharlesWhiteman]

Modified Code

Private Sub cmdSaveWorkflow_Click()
On Error GoTo err_handler
If IsNull(Me.txtTaskDueDate) Then
MsgBox "When does this task need to be completed by?", vbCritical, "Form Completion"
Me.txtTaskDueDate.SetFocus
Exit Sub
Else
End If
If IsNull(Me.txtTaskWorker) Then
MsgBox "Who this Task Is For?", vbCritical, "Form Completion"
Me.txtTaskWorker.SetFocus
Exit Sub
Else
End If
If IsNull(Me.txtTaskTitle) Then
MsgBox "What is this task?", vbCritical, "Form Completion"
Me.txtTaskTitle.SetFocus
Exit Sub
Else
End If
Me.txtTaskDetail.SetFocus
Me.cmdSaveWorkflow.enabled = False
Me.txtNotification.visible = True
'Enter The Enquiry Data Into TblHistory
Dim strSQLWorkflow As String
strSQLWorkflow = "INSERT INTO TblTasks (EnquiryID, PrimaryDataID, JobNumber, TaskTitle, TaskWorker, TaskManager,TaskDetail, TaskStartDate, TaskDueDate, DateCreated, CreatedBy)"
strSQLWorkflow = strSQLWorkflow & " VALUES(txtEnquiryID, txtPrimaryDataID, txtJobNumber, txtTaskTitle, txtTaskWorker, txtTaskManager, txtTaskDetail, txtTaskStartDate, txtTaskDueDate, txtDateCreated, txtCreatedBy)"
    DoCmd.SetWarnings False
DoCmd.RunSQL strSQLWorkflow
Forms!FrmPrimaryData.LbTasks.Requery
    DoCmd.SetWarnings True
Call TaskEmailNotify
DoCmd.Close
err_handler:
Call LogError(Err.Number, Err.Description, "cmdSaveWorkflow()")
End Sub

 

[RuralGuy]

Glad you were able to pin it down. You do know that you are turning off error messages alolg with warnings with DoCmd.SetWarnings False Right? If you use CurrentDB.Execute strSQLWorkflow, dbFailOnError no warnings appear but you can still get the errors. You might try that for grins.