Solved

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

Posted on 2013-11-11
6
901 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 119

Expert Comment

by:Rey Obrero
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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
LVL 119

Expert Comment

by:Rey Obrero
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 119

Accepted Solution

by:
Rey Obrero 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

932 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

Need Help in Real-Time?

Connect with top rated Experts

6 Experts available now in Live!

Get 1:1 Help Now