Access 2003 to 2010 conversion issue...Object variable or with block variable not set

I have an Access db created in Access 2003 that needs to be converted to 2010.  In general the db functions in 2010, but the database was able to send out an email with the click of a button in 2003.  Now when I click the botton I get an error..."object variable or With block variable not set".  This button runs off the below code.  Any suggestions of what I need to do to make this functional in 2010?  I would really appreciate it!

Option Compare Database

Private Sub Command5_Click()
    Dim stDocName As String
    Dim stLinkCriteria As String
   
    On Error GoTo Err_Command5_Click

   
    strsql = "select Title, Name, Email_Address into [tblLocalEmailFlow] from tblEmailFlow where [frm1-Step1] = 1;"
   
    DoCmd.SetWarnings False
    DoCmd.RunSQL strsql
   
   
    DoCmd.RunMacro "mcrrefresh"
    stDocName = "frmSendEmail"
    stLinkCriteria = "[ID]=" & Me![ID]
    DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_Command5_Click:
    Exit Sub

Err_Command5_Click:
    MsgBox Err.Description
    Resume Exit_Command5_Click
   
End Sub
KJKAccessAsked:
Who is Participating?
 
Nick67Connect With a Mentor Commented:
I am with @matthewspatrick on thinking that the problem lies within the unposted procedure NotesMailSend.

Depending on what's there and how it is coded, that is where your problem lies.  I agree with @fyed that whoever left you this gem was a bit lazy.  You should ALWAYS give your controls meaningful names.  It comes back to bite you, or your successor, if you don't.

In the opening code block, you move from 'Option Compare Database' and on to procedures.
This is also an extremely lazy and bad habit.  What should ALWAYS follow is 'Option Explicit'
When you do so, you'll find that omissions, like failing to 'Dim strsql as string' will jump out at you to fix.

'NotesMailSend' makes me fearful that you are using Lotus Notes as the email client?
Is that correct?  Because that is a bit of a can of worms to help you solve if that is so.
0
 
John SheehySecurity AnalystCommented:
You need to define the strsql as the following:

Dim strsql as String

John
0
 
KJKAccessAuthor Commented:
I am sorry.  I am not a programmer and the person that developed this is gone so if I seem like I don't know what I am talking about...I don't!  Having shared that, I think I posted the wrong code.  That is the code that opens the email form so the body and recipients can be added.  This is the code that I get the error on...  Is it still the same answer?  Really sorry :(

Private Sub Command5_Click()
    Dim firschar As Integer
    Dim lastchar As Integer
    Dim strlen As Integer
   
    firstchar = 1
    lastchar = InStr(firstchar, Me!EmailAddresses, ",")
    strlen = Len(Me!EmailAddresses)
   
    If lastchar = 0 Then
   
'        MsgBox Me!EmailAddresses
        Call NotesMailSend(Me!EmailAddresses, Me!Text4, Me!EmailBody)
   
    Else
        Do While lastchar > 0
       
'            MsgBox Mid(Me!EmailAddresses, firstchar, lastchar - firstchar)
            Call NotesMailSend(Mid(Me!EmailAddresses, firstchar, lastchar - firstchar), Me!Text4, Me!EmailBody)
            firstchar = lastchar + 1
            lastchar = InStr(firstchar, Me!EmailAddresses, ",")
   
        Loop
       
'        MsgBox Mid(Me!EmailAddresses, firstchar, strlen - firstchar + 1)
        Call NotesMailSend(Mid(Me!EmailAddresses, firstchar, strlen - firstchar + 1), Me!Text4, Me!EmailBody)
           
    End If
   
    DoCmd.Close
   

End Sub

Private Sub Form_Load()
    Dim DB As Database
    Dim rst As Recordset     ' v.1: Rst as Table
    Dim InRecord As Recordset
    Dim recSelectbck As Integer
    Dim PC_Qty As Long
    Dim Gone_Neg As Boolean
    Dim tt1 As Integer
    Dim PrevProduct As String
    Dim PrevLoc As String
    Dim CurProduct As String
    Dim firsttimethru As Boolean
   
    Set DB = CurrentDb()
    Set InRecord = DB.OpenRecordset("tblLocalEmailFlow")
   
    If InRecord.RecordCount = 0 Then
        Exit Sub
    End If
       
    firsttimethru = True
   
    InRecord.MoveLast
    recSelectbck = InRecord.RecordCount
    InRecord.MoveFirst
   
    For tt1 = 1 To recSelectbck
               
        If firsttimethru Then
            Me![EmailAddresses] = InRecord![email_address]
        Else
            Me![EmailAddresses] = Me![EmailAddresses] & ", " & InRecord![email_address]
        End If
       
        firsttimethru = False
       
        InRecord.MoveNext

    Next tt1

eendofile:

    Me![EmailBody] = [Process_Flow_Type] & ": Product Flow #" & [ID] & vbCr & "Reference Number = " & [Reference_Number] & vbCr & "Notes = " & [Reference_Number_Notes] & vbCr & "Hormel Code = " & [Hormel_Code_FG]
   
End Sub
0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Patrick MatthewsConnect With a Mentor Commented:
1) What specific line do you jump to if you use the debugger?

2) It appears that another procedure, Call NotesMailSend, is getting called, but you did not include that
0
 
Dale FyeConnect With a Mentor Commented:
First thing you need to do is fix the first bit of code.  EVERY TIME you use the

docmd.SetWarnings FALSE

command, you MUST turn the warnings back on.  Failure to do so can cause all sorts of unexpected problems.  It is generally used in conjunction with the

DoCmd.RunSQL strsql

statement, which will cause warning messages to popup when you run an action query, insert, delete, append.  I generally avoid using that syntax all together in favor the

currentdb.execute strsql, dbfailonerror

command, which will execute an action query with no messages, and if you use the dbFailOnError option, it will raise an error, which can be handled in an error handler.  However, to correct this deficiency, you should add the line indicated below to the code from your first post.

Exit_Command5_Click:
    docmd.setwarnings True     '<==   add this line
    Exit Sub
0
 
Dale FyeCommented:
Second,  If you are going to be tasked with updating someone elses code, you should learn to use a naming scheme for all of your controls.

Command5  means nothing to anyone, but cmd_SendEmail does.  There are a number of naming schemes but I generally use a hybrid that uses a combination of ReddickReddick and Leszynski
0
 
KJKAccessAuthor Commented:
Thank you all.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.