Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2013-06-14
7
Medium Priority
?
518 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 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
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 
LVL 49

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 49

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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
If you’re using QODBC to update QuickBooks data from Microsoft® Access but Access is not showing the updated data, you could have set up QODBC incorrectly.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

916 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