Solved

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

Posted on 2013-06-14
7
463 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
ID: 39248615
You need to define the strsql as the following:

Dim strsql as String

John
0
 

Author Comment

by:KJKAccess
ID: 39248644
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
ID: 39249028
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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 47

Assisted Solution

by:Dale Fye (Access MVP)
Dale Fye (Access MVP) earned 166 total points
ID: 39249086
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)
ID: 39249107
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
ID: 39249329
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
ID: 39420612
Thank you all.
0

Featured Post

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Flowing down data to other tables 13 35
Exporting from Access 2016 to a CSV file 4 50
What if i make webbased alternative for MS Access 5 71
ADODB problem 20 38
This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

830 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