Solved

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

Posted on 2013-06-14
7
428 Views
Last Modified: 2013-08-19
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
0
Comment
Question by:KJKAccess
7 Comments
 

Expert Comment

by:John Sheehy
Comment Utility
You need to define the strsql as the following:

Dim strsql as String

John
0
 

Author Comment

by:KJKAccess
Comment Utility
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
 
LVL 92

Assisted Solution

by:Patrick Matthews
Patrick Matthews earned 167 total points
Comment Utility
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
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 47

Assisted Solution

by:Dale Fye (Access MVP)
Dale Fye (Access MVP) earned 166 total points
Comment Utility
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
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
Comment Utility
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
 
LVL 26

Accepted Solution

by:
Nick67 earned 167 total points
Comment Utility
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
 

Author Closing Comment

by:KJKAccess
Comment Utility
Thank you all.
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now