Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2013-06-14
7
Medium Priority
?
505 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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 93

Assisted Solution

by:Patrick Matthews
Patrick Matthews earned 501 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
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 
LVL 48

Assisted Solution

by:Dale Fye
Dale Fye earned 498 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 48

Expert Comment

by:Dale Fye
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 501 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

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

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

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

704 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