Solved

ms access vba recordsetclone object variable or with block variable not set

Posted on 2013-11-11
6
906 Views
Last Modified: 2013-11-11
Hi,

I am having trouble with access throwing error no 91: object variable or with block variable not set on my recordsetclone.

In my form I am trying to compare the oldvalue with the newvalue so i can run an audit process if there have been any changes made.

Option Compare Database
Option Explicit
Dim rs As Recordset


Private Sub cmdApprove_Click()

    If Me.FinalTotalPrem.Value <> rs![FinalTotalPrem] Then
    ' run audit process
    End If

End Sub

Private Sub Form_Current()
    Set rs = Me.RecordsetClone
    rs.Bookmark = Me.Bookmark
End Sub

Can anyone assist as to why am i getting this error?

Many thanks
0
Comment
Question by:andrewpiconnect
  • 3
  • 2
6 Comments
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 39638715
test this

Private Sub Form_Current()
    with Me.RecordsetClone
         Me.Bookmark = .Bookmark

    end with
End Sub


to compare the current value for a field previous value, you can use

if Me.FinalTotalPrem.oldValue<> Me.FinalTotalPrem.value then
0
 

Author Comment

by:andrewpiconnect
ID: 39638733
Hi Capricorn1,

I tried your sugsestion and it still never worked:
<<<<with Me.RecordsetClone
         Me.Bookmark = .Bookmark

    end with>>>>

I cannot seem to use the straightforward oldvalue <> value method as my form has two subforms in it and if either is clicked then the main form loses the recordsetclone
0
 
LVL 57
ID: 39638740
<<I cannot seem to use the straightforward oldvalue <> value method as my form has two subforms in it and if either is clicked then the main form loses the recordsetclone >>

 What's not obvious is that when you jump into a subform, the main form record is automatically saved.

 Also, anytime you cause a requery (doing a requery, changing the filter or recordsource, etc), then your bookmarks are all invalidated.

Jim.
0
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.

 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 39638746
you must use the beforeupdate event of the form if you want to have an audit process for records change in the form.


see this link for reference

http://support.microsoft.com/?kbid=197592
0
 

Author Comment

by:andrewpiconnect
ID: 39638790
Ive used this method before on a previous database....i.e using a cmd button to perform other tasks besides the audit and then run the audit process and it has worked fine.

I have just discovered that i am decalring the same rs twice for another task in the same form so i have renamed the cloning recordset as follows and all works fine:

Option Compare Database
Option Explicit
Dim rsClone As Recordset


Private Sub cmdApprove_Click()

    If Me.FinalTotalPrem.Value <> rsClone![FinalTotalPrem] Then
    ' run audit process
    End If

End Sub

Private Sub Form_Current()
    Set rsClone = Me.RecordsetClone
    rsClone.Bookmark = Me.Bookmark
End Sub
0
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 500 total points
ID: 39638817
ooh, i did the reversed, this should do it without declaring another recordset

Private Sub Form_Current()
    with Me.RecordsetClone
        .Bookmark =  Me.Bookmark

    end with
End Sub
0

Featured Post

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

Suggested Solutions

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

777 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