Solved

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

Posted on 2013-11-11
6
892 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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
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

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

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…
It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
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…

707 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

13 Experts available now in Live!

Get 1:1 Help Now