JeffBarker
Registered User.
- Local time
- Today, 21:53
- Joined
- Dec 7, 2010
- Messages
- 130
Hi guys,
Bit of a weird (and long-winded) one this, but we have a module based in our (Access 2007) CRM system that allows our team in Production to view and acknowledge new orders as they're logged on the CRM by our Sales team.
There's a number of functions in the module, but the one that gets the most use is the Order Acknowledgement fuction, which is triggered by a text box masquerading as a button on a continuous subform of New Orders.
We have an Event Procedure in the On Click event of the Text Box, that opens up another form which has the code below in its On Load event - as I'm no good at writing Recordsets this was the best way I could find to execute this code.
The idea behind it is for the form that opens up to pass all the variables necessary to build the Acknowledgement email, write a log in the Audit table and then display the email for the Production user to review and send.
The form then closes down, and as this all happens in the blink of an eye all the user will see happen after he clicks the Text Box is the appearance of the Acknowledgement email.
A funny thing is happening, whereby the database is throwing up a 'Type Mismatch' error at some point when executing the code in the Text Box On Click event - but when you put a stop on the code and step through it using F8, everything works.
I've tried pressing Ctrl-Break while the Type Mismatch error is displayed, but it only takes me to the Error Handling code.
Can anyone give me some idea as to why this is happening, please?
Here's the code in the form's On Load event (the one that opens after the Text Box On Click event is triggered):
So yes, if anybody can help me work out why the Type Mismatch only appears when executing the code from the On Click event, that would be great!
Bit of a weird (and long-winded) one this, but we have a module based in our (Access 2007) CRM system that allows our team in Production to view and acknowledge new orders as they're logged on the CRM by our Sales team.
There's a number of functions in the module, but the one that gets the most use is the Order Acknowledgement fuction, which is triggered by a text box masquerading as a button on a continuous subform of New Orders.
We have an Event Procedure in the On Click event of the Text Box, that opens up another form which has the code below in its On Load event - as I'm no good at writing Recordsets this was the best way I could find to execute this code.
The idea behind it is for the form that opens up to pass all the variables necessary to build the Acknowledgement email, write a log in the Audit table and then display the email for the Production user to review and send.
The form then closes down, and as this all happens in the blink of an eye all the user will see happen after he clicks the Text Box is the appearance of the Acknowledgement email.
A funny thing is happening, whereby the database is throwing up a 'Type Mismatch' error at some point when executing the code in the Text Box On Click event - but when you put a stop on the code and step through it using F8, everything works.
I've tried pressing Ctrl-Break while the Type Mismatch error is displayed, but it only takes me to the Error Handling code.
Can anyone give me some idea as to why this is happening, please?
Here's the code in the form's On Load event (the one that opens after the Text Box On Click event is triggered):
Code:
Private Sub Form_Load()
On Error GoTo Err_Form_Load
Dim MyItem As Outlook.MailItem
Dim OL As Outlook.Application
Dim strHTML, strline As String
Dim EMailTo As Variant
Dim Subject As String
Dim HisID As String
Dim CName As String
Dim ProdCont As String
Dim SiteID As String
Dim AllocID As String
Dim Prod1stSend As String
Dim ProdUser As String
Dim vSQL1 As String
Dim vSQL2 As String
Dim vSQL3 As String
Dim vSQL4 As String
Dim vFilt As String
Dim OrderCount As String
Dim DearProductionContactName As String
Set OL = New Outlook.Application
Set MyItem = Outlook.Application.CreateItem(olMailItem)
EMailTo = Me.txtDirectEMail
CName = Me.txtCompanyName
HisID = Me.txtHistoryID
Subject = "H2O Production Planning for your order ref: " & HisID & " for " & CName & "."
ProdCont = Me.txtProductionContact
SiteID = Me.txtSiteID
AllocID = Me.txtAllocaterID
Prod1stSend = Me.txtProd1stSend
ProdUser = fGetUserName
OrderCount = Me.txtRecordCount
DearProductionContactName = Me.txtDearProductionContactName
vFilt = "[Site ID] = " & Me!txtSiteID & " AND [HistoryID] = " & Me!HistoryID
vSQL2 = "UPDATE [Site Contacts] INNER JOIN (tblAllocation INNER JOIN tblHistory ON tblAllocation.HistoryID = tblHistory.HistoryID) ON [Site Contacts].[Contact ID] = tblHistory.ContactID SET [Site Contacts].Prod1stSend = -1, tblAllocation.ArtworkStatus = 'Requested', tblHistory.AcknwDateOrder = Now() " _
& " WHERE (((tblHistory.HistoryID)=[Forms]![frmProduction_AcknwEmail_DONOTDELETE]![txtHistoryID]) AND ((tblAllocation.AllocaterID)=[Forms]![frmProduction_AcknwEmail_DONOTDELETE]![txtAllocaterID]))"
vSQL3 = "UPDATE tblHistory SET tblHistory.ProductionUser = '" & ProdUser & "' WHERE tblHistory.HistoryID = " & HisID
vSQL4 = "UPDATE tblAllocation SET tblAllocation.AcknwDate = '" & Now() & "' WHERE tblAllocation.AllocaterID = " & AllocID & " AND tblAllocation.HistoryID = " & HisID
DoCmd.SetWarnings False
Select Case Prod1stSend
Case False
If OrderCount > 9 Then
DoCmd.OpenReport "rptProduction_FirstAcknwEmail", acViewPreview, , vFilt
DoCmd.OutputTo acOutputReport, "", acFormatPDF, ("C:\Splash\H2O Production Planning for order " & HisID & " - " & CName & ".PDF")
Open ("C:\Splash\H2O Production Planning for order " & HisID & " - " & CName & ".PDF") For Input As 1
Do While Not EOF(1)
Input #1, strline
strHTML = strHTML & strline
Loop
Close 1
If Left(OL.Version, 2) = "10" Then
MyItem.BodyFormat = olFormatHTML
End If
MyItem.HTMLBody = strHTML
MyItem.To = EMailTo
MyItem.Subject = Subject
MyItem.Body = "Hi " & DearProductionContactName & "," & vbCrLf & vbCrLf & _
"Thank you for your order." & vbCrLf & _
"This is an automated email to confirm the artwork deadlines for your order." & vbCrLf & _
"Please find your artwork deadlines attached." & vbCrLf & _
"Please let me know if you have any queries otherwise I look forward to receiving your artwork by the attached dates." & vbCrLf & vbCrLf & _
"Kind Regards" & vbCrLf & vbCrLf & _
ProdUser & vbCrLf & vbCrLf & _
"Office: 0845 500 6008"
MyItem.Attachments.Add "C:\Splash\H2O Production Planning for order " & HisID & " - " & CName & ".PDF"
MyItem.Display
Else
DoCmd.OpenReport "rptProduction_FirstAcknwEmail", acViewPreview, , vFilt
DoCmd.OutputTo acOutputReport, "rptProduction_FirstAcknwEmail", acFormatHTML, ("C:\Splash\H2O Production Planning for order " & HisID & " - " & CName & ".html")
Open ("C:\Splash\H2O Production Planning for order " & HisID & " - " & CName & ".html") For Input As 1
Do While Not EOF(1)
Input #1, strline
strHTML = strHTML & strline
Loop
Close 1
If Left(OL.Version, 2) = "10" Then
MyItem.BodyFormat = olFormatHTML
End If
MyItem.HTMLBody = strHTML
MyItem.To = EMailTo
MyItem.Subject = Subject
MyItem.Display
End If
vSQL1 = "INSERT INTO tblProduction_LogDate ( HistoryID, LogDate, [User], Detail )" _
& " SELECT tblHistory.HistoryID, Now() AS [Date], fgetusername() AS UN, 'First Acknowledgement sent to ' & ProdCont AS Det " _
& " FROM ((tblHistory INNER JOIN tblAllocation ON tblHistory.HistoryID = tblAllocation.HistoryID) INNER JOIN [Site Contacts] ON tblHistory.ContactID = [Site Contacts].[Contact ID]) INNER JOIN [Site Information] ON [Site Contacts].[Site ID] = [Site Information].[Site ID] " _
& " WHERE ((([Site Information].[Site ID]) = " & SiteID & ") AND ((tblAllocation.AllocaterID)= " & AllocID & "))"
DoCmd.RunSQL vSQL1
DoCmd.Close acReport, "rptProduction_FirstAcknwEmail"
Case True
If OrderCount > 9 Then
DoCmd.OpenReport "rptProduction_NextAcknwEmail", acViewPreview, , vFilt
DoCmd.OutputTo acOutputReport, "", acFormatPDF, ("C:\Splash\H2O Production Planning for order " & HisID & " - " & CName & ".PDF")
Open ("C:\Splash\H2O Production Planning for order " & HisID & " - " & CName & ".PDF") For Input As 1
Do While Not EOF(1)
Input #1, strline
strHTML = strHTML & strline
Loop
Close 1
If Left(OL.Version, 2) = "10" Then
MyItem.BodyFormat = olFormatHTML
End If
MyItem.HTMLBody = strHTML
MyItem.To = EMailTo
MyItem.Subject = Subject
MyItem.Body = "Hi " & DearProductionContactName & "," & vbCrLf & vbCrLf & _
"Thank you for your order." & vbCrLf & _
"This is an automated email to confirm the artwork deadlines for your order." & vbCrLf & _
"Please find your artwork deadlines attached." & vbCrLf & _
"Please let me know if you have any queries otherwise I look forward to receiving your artwork by the attached dates." & vbCrLf & vbCrLf & _
"Kind Regards" & vbCrLf & vbCrLf & _
ProdUser & vbCrLf & vbCrLf & _
"Office: 0845 500 6008"
MyItem.Attachments.Add "C:\Splash\H2O Production Planning for order " & HisID & " - " & CName & ".PDF"
MyItem.Display
Else
DoCmd.OpenReport "rptProduction_NextAcknwEmail", acViewPreview, , vFilt
DoCmd.OutputTo acOutputReport, "rptProduction_NextAcknwEmail", acFormatHTML, ("C:\Splash\H2O Production Planning for order " & HisID & " - " & CName & ".html")
Open ("C:\Splash\H2O Production Planning for order " & HisID & " - " & CName & ".html") For Input As 1
Do While Not EOF(1)
Input #1, strline
strHTML = strHTML & strline
Loop
Close 1
If Left(OL.Version, 2) = "10" Then
MyItem.BodyFormat = olFormatHTML
End If
MyItem.HTMLBody = strHTML
MyItem.To = EMailTo
MyItem.Subject = Subject
MyItem.Display
End If
vSQL1 = "INSERT INTO tblProduction_LogDate ( HistoryID, LogDate, [User], Detail )" _
& " SELECT tblHistory.HistoryID, Now() AS [Date], fgetusername() AS UN, 'Acknowledgement sent to ' & ProdCont AS Det " _
& " FROM ((tblHistory INNER JOIN tblAllocation ON tblHistory.HistoryID = tblAllocation.HistoryID) INNER JOIN [Site Contacts] ON tblHistory.ContactID = [Site Contacts].[Contact ID]) INNER JOIN [Site Information] ON [Site Contacts].[Site ID] = [Site Information].[Site ID] " _
& " WHERE ((([Site Information].[Site ID]) = " & SiteID & ") AND ((tblAllocation.AllocaterID)= " & AllocID & "))"
DoCmd.RunSQL vSQL1
DoCmd.Close acReport, "rptProduction_NextAcknwEmail"
End Select
DoCmd.RunSQL vSQL2
DoCmd.RunSQL vSQL3
DoCmd.RunSQL vSQL4
Forms![frmProduction_NewOrders]![frmNewOrders_ProductionSub].Requery
DoCmd.Close acForm, "frmProduction_AcknwEmail_DONOTDELETE"
DeleteDuplicateRecords ("tblProduction_LogDate")
DoCmd.SetWarnings True
Exit_Form_Load:
Exit Sub
Err_Form_Load:
MsgBox Err.Description
Resume Exit_Form_Load
End SubSo yes, if anybody can help me work out why the Type Mismatch only appears when executing the code from the On Click event, that would be great!
