Solved

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

Posted on 2013-11-11
6
918 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
[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
  • 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 58
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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
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 …

689 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