• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 942
  • Last Modified:

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

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
andrewpiconnect
Asked:
andrewpiconnect
  • 3
  • 2
1 Solution
 
Rey Obrero (Capricorn1)Commented:
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
 
andrewpiconnectAuthor Commented:
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
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
<<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
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 
Rey Obrero (Capricorn1)Commented:
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
 
andrewpiconnectAuthor Commented:
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
 
Rey Obrero (Capricorn1)Commented:
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

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now