Prev: Error 2147352567
Next: button and path question
From: Larry06Green on 28 May 2010 12:21
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

From: Douglas J. Steele on 28 May 2010 15:04
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!)



"Larry06Green" <Larry06Green(a)discussions.microsoft.com> wrote in message
news:C31F457A-6F63-4FF7-9B94-0C96B9DBF346(a)microsoft.com...
> 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
>

From: Linq Adams via AccessMonster.com on 28 May 2010 16:13
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?

--
There's ALWAYS more than one way to skin a cat!

Answers/posts based on Access 2000/2003

Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/201005/1

 | 
Pages: 1
Prev: Error 2147352567
Next: button and path question