Access - Object variable or with block not set

Question

I have a Issue System where user can open tickets and input their comments in it. The tables are SQL linked tables.

I am trying to implement auto fetch contents of emails by adding a Button.

Basically, it will goes to a particular folder "Pendings" in Outlook and get all the emails which are not marked as "Copied".

Here is the code.

Public Sub myinbox()
Dim TempRst As DAO.Recordset
Dim Olapp As Outlook.Application
Dim Inbox As Outlook.MAPIFolder
Dim InboxItems As Outlook.Items
Dim Mailobject As Object
Dim db As DAO.Database
Dim Olfolder As Outlook.MAPIFolder


'MsgBox "It will take some time. So, Hang On!"

Set db = CurrentDb

Set Olapp = CreateObject("Outlook.Application")
Set Inbox = Olapp.GetNamespace("Mapi").GetFolderFromID("000000001A447390AA6611CD9BC800AA002FC45A03003683A021347CC54C82688B880BB383EC000000B95F710000") ' working folder ID

'
Set InboxItems = Inbox.Items
Set TempRst = CurrentDb.OpenRecordset("working") ' Table

For Each Mailobject In InboxItems
    'If Mailobject.UnRead Then
    If Mailobject.Categories <> "Copied" Then

    With TempRst

        .AddNew

        !Title = Mailobject.Subject
       ' !From = Mailobject.SenderName
       ' !To = Mailobject.To
       ' !Body = Mailobject.Body
        !OpenedDate = Mailobject.ReceivedTime
        '!email = Mailobject.SenderEmailAddress
        !OpenedBy = "Group1"
        !Priority = "(2) Normal"
        !Status = "Pending"
        .Update
        Mailobject.Categories = "Copied"
        Mailobject.Save
        Mailobject.UnRead = False


    End With

End If
Next

Set Olapp = Nothing
Set Inbox = Nothing
Set InboxItems = Nothing
Set Mailobject = Nothing
Set TempRst = Nothing

MsgBox "Emails updated successfully"

End Sub

The above code is working fine with the Local tables (which i tested Locally without linking to SQL). But, when I am trying to run the same code with the Linked SQL tables. I am getting this error:

Error : "Object variable or with block not set"

at this line:

Set TempRst = CurrentDb.OpenRecordset("working")

If "working" is an existing table in your database, I have trouble imagining how this line could throw this error. Since you assigned a db variable, you should use Set TempRst = db.OpenRecordset("working") but that probably won't make a difference. — Andre
– Andre, Commented Aug 29, 2016 at 8:36
P.S. Is this the actual code, that reproduces the problem? See minimal reproducible example — Andre
– Andre, Commented Aug 29, 2016 at 8:43
@Andre Thank you so much for your reply. Working is the Table in the database. I have dummy database in my local (not linked tables) and the above code works fine. But, the error is throwing when I run the same code on SQL Linked Tables. I have "Set TempRst = db.OpenRecordset("working"), still getting the same error. — rocky09
– rocky09, Commented Aug 29, 2016 at 9:01
@Andre Correctoin, the folder is "Pendings" not working. I edited my question. :) — rocky09
– rocky09, Commented Aug 29, 2016 at 9:12
Check if Set TempRst = db.OpenRecordset("SELECT * FROM working", dbOpenDynaset) still produce the error — LostReality
– LostReality, Commented Aug 29, 2016 at 10:00

AVG · Accepted Answer · 2016-08-29 10:23:39Z

1

This should work:

Set TempRst = db.OpenRecordset("SELECT * FROM working WHERE <yourPkField> IS NULL", dbOpenDynaset, dbSeeChanges)

You need to use dbSeeChanges with SQL Server linked tables. Since you are only adding records, there is no need to select any existing records. Hence, the where clause.

answered Aug 29, 2016 at 10:23

AVG

1,4621 gold badge9 silver badges12 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

rocky09 Over a year ago

still it throws the same error. Thank you very much for your reply.

AVG Over a year ago

Please post your actual code and indicate the exact line of the error.

rocky09 Over a year ago

i have already posted the actual code and mentioned the exact line of the error in my Original Post. Thank you for taking time to look into this.

AVG Over a year ago

Your original code is posted. I see no reason for what I posted, to not work as I have written thousands of processes similarly. Please post your current code.

Collectives™ on Stack Overflow

Access - Object variable or with block not set

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related