L
Larry06Green
Recently I converted a bound data entry form to an unbound form. Since then
I've been getting emails from users who say that they get a "Data Type
Conversion Error" when they hit the Save Data button on the form. Can someone
look at the VBA code behind the Save Data button and tell me what might be
causing this annoying error message?
Here's the VBA code:
Private Sub Label282_Click()
On Error GoTo Err_Label282_Click
Dim intPress As Integer
Dim intPress1 As Integer
Dim intPress2 As Integer
Dim intPress3 As Integer
Dim intPressA As Integer
Dim db As Database
Dim rs As Recordset
Dim i As Integer
Dim X As Variant
Dim m As Variant
Dim db1 As Object
Dim session As Object
Dim doc As Object
Dim Resolution As String
Me.Aud_Type.Value = ""
Me.Aud_TimeStamp.Value = Null
Me.Aud_Racf = ""
Me.ActiveControl.SetFocus
If IsNull(Me.chrProjectTitle.Value) Then
intPressA = MsgBox("Project Title is missing.", 64, "Missing Information")
Me.chrProjectTitle.SetFocus
Else
If IsNull(Me.dtmSubmissionDate.Value) Then
intPressA = MsgBox("Submission Date is missing.", 64, "Missing
Information")
Me.dtmSubmissionDate.SetFocus
Else
If Me.dtmSubmissionDate > Date Then
intPressA = MsgBox("Submission Date cannot be greater than today's
date.", 64, "Invalid Information")
Me.dtmSubmissionDate.SetFocus
Else
If IsNull(Me.chrRequestingArea.Value) Then
intPressA = MsgBox("Requesting Area selection is missing.", 64, "Missing
Information")
Me.chrRequestingArea.SetFocus
Else
If IsNull(Me.chrSubmittedTo.Value) Then
intPressA = MsgBox("Submitted To selection is missing.", 64, "Missing
Information")
Me.chrSubmittedTo.SetFocus
Else
If IsNull(Me.chrTypeofRequest.Value) Then
intPressA = MsgBox("Type of Request selection is missing.", 64, "Missing
Information")
Me.chrTypeofRequest.SetFocus
Else
If IsNull(Me.chrProjDesc.Value) Then
intPressA = MsgBox("High Level Project Description is missing.", 64,
"Missing Information")
Me.chrProjDesc.SetFocus
Else
Set db = CurrentDb()
Set rs = db.OpenRecordset("SELECT Max(tblGetNum.recnum) as Maxofrecnum
FROM tblGetNum")
Me.intProjectID.Value = rs("Maxofrecnum") + 1
Me.chrProjID.Value = Me.chrRA.Value & "" & Me.intProjectID.Value
rs.Close
Set rs = db.OpenRecordset("tblGetNum")
rs.AddNew
rs("recnum") = Me.intProjectID.Value
rs.Update
Set rs = db.OpenRecordset("tblProjects")
rs.AddNew
X = Me.chrProjID.Value
For i = 0 To Me.Controls.Count - 1
If Me.Controls(i).Tag <> "Lookup" Then
Select Case (Me.Controls(i).ControlType)
Case acTextBox, acComboBox, acListBox, acOptionGroup
X = Me.Controls(i).Name
rs(Me.Controls(i).Name) = Me.Controls(i)
End Select
Me.Aud_Type.Value = "New"
Me.Aud_TimeStamp.Value = Now()
Call GetName
Me.Aud_Racf = racf
End If
Next i
rs.Update
rs.Close
Set rs = db.OpenRecordset("tblHistory")
X = Me.chrProjID
rs.AddNew
For i = 0 To Me.Controls.Count - 1
If Me.Controls(i).Tag <> "Lookup" Then
Select Case (Me.Controls(i).ControlType)
Case acTextBox, acComboBox, acListBox, acOptionGroup
X = Me.Controls(i).Name
rs(Me.Controls(i).Name) = Me.Controls(i)
End Select
Me.Aud_Type = "New"
Me.Aud_TimeStamp = Now()
Call GetName
Me.Aud_Racf = racf
End If
Next i
rs.Update
rs.Close
DoCmd.Beep
intPress = MsgBox("Project request information has been saved." & vbCrLf
& "Would you like to send an Email Notification?", vbQuestion + _
vbYesNo, "Email Prompt")
If intPress = 6 Then
On Error GoTo ErrorHandler2
Set session = CreateObject("Notes.NotesSession")
Set db1 = session.CurrentDatabase
Set doc = db1.CreateDocument
doc.Form = "Memo"
doc.SendTo = Me.chrSubmittedTo.Value
doc.Subject = "New Project Notification" & Chr(13) & _
Me.chrProjectTitle.Value & Chr(13) & _
Me.chrProjID.Value
doc.Body = "The project request briefly described below was just
submitted to you through the Marketing Analytical Request System. Please
contact me if you have any questions." & Chr(13) & _
" " & Chr(13) & _
"Submission Date:" & Chr(13) & _
Me.dtmSubmissionDate.Value & Chr(13) & _
" " & Chr(13) & _
"Requested Completion Date:" & Chr(13) & _
Me.dtmRequestedCompletionDate.Value & Chr(13) & _
" " & Chr(13) & _
"Project Priority:" & Chr(13) & _
Me.chrProjectPriority.Value & Chr(13) & _
" " & Chr(13) & _
"Requesting Area:" & Chr(13) & _
Me.chrRequestingArea.Value & Chr(13) & _
" " & Chr(13) & _
"Type of Request:" & Chr(13) & _
Me.chrTypeofRequest.Value & Chr(13) & _
" " & Chr(13) & _
"High Level Project Description:" & Chr(13) & _
Me.chrProjDesc.Value & vbCrLf
Call doc.Send(False)
Set session = Nothing 'Unload the Object
intPress2 = MsgBox("Your project information has been sent.", 64, "Email
Notification")
Call BlankForm
intPress3 = MsgBox("Enter another Project?", vbQuestion + _
vbYesNo, "Project Prompt")
If intPress3 = 7 Then
DoCmd.Close
DoCmd.OpenForm "Switchboard"
End If
If intPress3 = 6 Then
DoCmd.Close
DoCmd.OpenForm "Switchboard"
DoCmd.Close
DoCmd.OpenForm "frmAddRecord"
End If
End If
If intPress = 7 Then
Call BlankForm
DoCmd.Beep
intPress1 = MsgBox("Enter another Project?", vbQuestion + _
vbYesNo, "Project Prompt")
If intPress1 = 7 Then
Call BlankForm
DoCmd.Close
End If
If intPress1 = 6 Then
DoCmd.Close
DoCmd.OpenForm "Switchboard"
DoCmd.Close
DoCmd.OpenForm "frmAddRecord"
End If
End If
End If
End If
End If
End If
End If
End If
End If
Exit_Label282_Click:
Exit Sub
CleanUp:
DoCmd.Close acForm, "frmAddRecord"
DoCmd.OpenForm "Switchboard"
Exit Sub
Err_Label282_Click:
MsgBox Err.Description
Resume Exit_Label282_Click
ErrorHandler2:
MsgBox "Project request has been saved in the database, but email
notification could not be sent at this time. Please contact your analytics
partner with project specifics."
Resume CleanUp
End Sub
I've been getting emails from users who say that they get a "Data Type
Conversion Error" when they hit the Save Data button on the form. Can someone
look at the VBA code behind the Save Data button and tell me what might be
causing this annoying error message?
Here's the VBA code:
Private Sub Label282_Click()
On Error GoTo Err_Label282_Click
Dim intPress As Integer
Dim intPress1 As Integer
Dim intPress2 As Integer
Dim intPress3 As Integer
Dim intPressA As Integer
Dim db As Database
Dim rs As Recordset
Dim i As Integer
Dim X As Variant
Dim m As Variant
Dim db1 As Object
Dim session As Object
Dim doc As Object
Dim Resolution As String
Me.Aud_Type.Value = ""
Me.Aud_TimeStamp.Value = Null
Me.Aud_Racf = ""
Me.ActiveControl.SetFocus
If IsNull(Me.chrProjectTitle.Value) Then
intPressA = MsgBox("Project Title is missing.", 64, "Missing Information")
Me.chrProjectTitle.SetFocus
Else
If IsNull(Me.dtmSubmissionDate.Value) Then
intPressA = MsgBox("Submission Date is missing.", 64, "Missing
Information")
Me.dtmSubmissionDate.SetFocus
Else
If Me.dtmSubmissionDate > Date Then
intPressA = MsgBox("Submission Date cannot be greater than today's
date.", 64, "Invalid Information")
Me.dtmSubmissionDate.SetFocus
Else
If IsNull(Me.chrRequestingArea.Value) Then
intPressA = MsgBox("Requesting Area selection is missing.", 64, "Missing
Information")
Me.chrRequestingArea.SetFocus
Else
If IsNull(Me.chrSubmittedTo.Value) Then
intPressA = MsgBox("Submitted To selection is missing.", 64, "Missing
Information")
Me.chrSubmittedTo.SetFocus
Else
If IsNull(Me.chrTypeofRequest.Value) Then
intPressA = MsgBox("Type of Request selection is missing.", 64, "Missing
Information")
Me.chrTypeofRequest.SetFocus
Else
If IsNull(Me.chrProjDesc.Value) Then
intPressA = MsgBox("High Level Project Description is missing.", 64,
"Missing Information")
Me.chrProjDesc.SetFocus
Else
Set db = CurrentDb()
Set rs = db.OpenRecordset("SELECT Max(tblGetNum.recnum) as Maxofrecnum
FROM tblGetNum")
Me.intProjectID.Value = rs("Maxofrecnum") + 1
Me.chrProjID.Value = Me.chrRA.Value & "" & Me.intProjectID.Value
rs.Close
Set rs = db.OpenRecordset("tblGetNum")
rs.AddNew
rs("recnum") = Me.intProjectID.Value
rs.Update
Set rs = db.OpenRecordset("tblProjects")
rs.AddNew
X = Me.chrProjID.Value
For i = 0 To Me.Controls.Count - 1
If Me.Controls(i).Tag <> "Lookup" Then
Select Case (Me.Controls(i).ControlType)
Case acTextBox, acComboBox, acListBox, acOptionGroup
X = Me.Controls(i).Name
rs(Me.Controls(i).Name) = Me.Controls(i)
End Select
Me.Aud_Type.Value = "New"
Me.Aud_TimeStamp.Value = Now()
Call GetName
Me.Aud_Racf = racf
End If
Next i
rs.Update
rs.Close
Set rs = db.OpenRecordset("tblHistory")
X = Me.chrProjID
rs.AddNew
For i = 0 To Me.Controls.Count - 1
If Me.Controls(i).Tag <> "Lookup" Then
Select Case (Me.Controls(i).ControlType)
Case acTextBox, acComboBox, acListBox, acOptionGroup
X = Me.Controls(i).Name
rs(Me.Controls(i).Name) = Me.Controls(i)
End Select
Me.Aud_Type = "New"
Me.Aud_TimeStamp = Now()
Call GetName
Me.Aud_Racf = racf
End If
Next i
rs.Update
rs.Close
DoCmd.Beep
intPress = MsgBox("Project request information has been saved." & vbCrLf
& "Would you like to send an Email Notification?", vbQuestion + _
vbYesNo, "Email Prompt")
If intPress = 6 Then
On Error GoTo ErrorHandler2
Set session = CreateObject("Notes.NotesSession")
Set db1 = session.CurrentDatabase
Set doc = db1.CreateDocument
doc.Form = "Memo"
doc.SendTo = Me.chrSubmittedTo.Value
doc.Subject = "New Project Notification" & Chr(13) & _
Me.chrProjectTitle.Value & Chr(13) & _
Me.chrProjID.Value
doc.Body = "The project request briefly described below was just
submitted to you through the Marketing Analytical Request System. Please
contact me if you have any questions." & Chr(13) & _
" " & Chr(13) & _
"Submission Date:" & Chr(13) & _
Me.dtmSubmissionDate.Value & Chr(13) & _
" " & Chr(13) & _
"Requested Completion Date:" & Chr(13) & _
Me.dtmRequestedCompletionDate.Value & Chr(13) & _
" " & Chr(13) & _
"Project Priority:" & Chr(13) & _
Me.chrProjectPriority.Value & Chr(13) & _
" " & Chr(13) & _
"Requesting Area:" & Chr(13) & _
Me.chrRequestingArea.Value & Chr(13) & _
" " & Chr(13) & _
"Type of Request:" & Chr(13) & _
Me.chrTypeofRequest.Value & Chr(13) & _
" " & Chr(13) & _
"High Level Project Description:" & Chr(13) & _
Me.chrProjDesc.Value & vbCrLf
Call doc.Send(False)
Set session = Nothing 'Unload the Object
intPress2 = MsgBox("Your project information has been sent.", 64, "Email
Notification")
Call BlankForm
intPress3 = MsgBox("Enter another Project?", vbQuestion + _
vbYesNo, "Project Prompt")
If intPress3 = 7 Then
DoCmd.Close
DoCmd.OpenForm "Switchboard"
End If
If intPress3 = 6 Then
DoCmd.Close
DoCmd.OpenForm "Switchboard"
DoCmd.Close
DoCmd.OpenForm "frmAddRecord"
End If
End If
If intPress = 7 Then
Call BlankForm
DoCmd.Beep
intPress1 = MsgBox("Enter another Project?", vbQuestion + _
vbYesNo, "Project Prompt")
If intPress1 = 7 Then
Call BlankForm
DoCmd.Close
End If
If intPress1 = 6 Then
DoCmd.Close
DoCmd.OpenForm "Switchboard"
DoCmd.Close
DoCmd.OpenForm "frmAddRecord"
End If
End If
End If
End If
End If
End If
End If
End If
End If
Exit_Label282_Click:
Exit Sub
CleanUp:
DoCmd.Close acForm, "frmAddRecord"
DoCmd.OpenForm "Switchboard"
Exit Sub
Err_Label282_Click:
MsgBox Err.Description
Resume Exit_Label282_Click
ErrorHandler2:
MsgBox "Project request has been saved in the database, but email
notification could not be sent at this time. Please contact your analytics
partner with project specifics."
Resume CleanUp
End Sub