Annoying Data Type Conversion Error

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
 
D

Douglas J. Steele

I'm betting you have references to both DAO and ADO, and the ADO reference
is higher in the search order.

Change

Dim rs As Recordset

to

Dim rs As DAO.Recordset

If you're not using ADO, you may as well remove the reference to it as well.


--
Doug Steele, Microsoft Access MVP
http://www.AccessMVP.com/djsteele
Co-author: Access 2010 Solutions, published by Wiley
(no e-mails, please!)
 
L

Linq Adams via AccessMonster.com

Just out of curiosity, what is the line

Me.ActiveControl.SetFocus

supposed to be doing?

Me.ActiveControl

returns the name of the control ***that currently has focus*** so why would
you need to set focus to it again?
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top