Solved

Question on "frm.Bookmark = rst.Bookmark" Error

Posted on 2008-10-14
3
407 Views
Last Modified: 2013-11-28
User sometimes get a Run-Time Error '2001": "You cancel the previous operation"
....and thie error occur when performing a search for a record using the code in the Code Snippet.

How can the user get rid of this error and still be able to perform the seach for a record as per the routine dictates.

frm.Bookmark = rst.Bookmark           '  <<=== Error line
Private Sub DoSearch(myMode As Integer)

   'myMode = 0 means first search

   'myMode = 1 means next search

   'Validation, just double check

    If IsNull(OpenArgs) = True Then

        MsgBox "Missing argument from calling Form"

        Exit Sub

    End If

    Set frm = Forms(Me.OpenArgs)
 

    If frm.Dirty = True Then

    Dim txStr As String

          txStr = "You made changes of form '" & frm.Name & "'?. Click the Update button to continue"

                Select Case MsgBox(txStr & vbCrLf & _

                "(To cancel, press Cancel button or ('ESC') key.).")

            End Select

                Exit Sub

            End If
 

    strSearch = ""

       If Not IsNull(cboFindBNo) And Len(Trim(cboFindBNo)) > 0 Then

        strSearch = "BNo = " & Val(cboFindBNo)

        End If

' .........................

' ........................ 

Dim rst As DAO.Recordset

    Set rst = frm.RecordsetClone 

    If rst.EOF = False Or rst.BOF = False Then

      If myMode = 0 Then

        rst.MoveLast

        rst.MoveFirst

        rst.FindFirst strSearch

        GoTo Mode0

      Else

        rst.FindNext strSearch

        GoTo Mode1

      End If

    End If

Mode0:

    If rst.NoMatch Then

        MsgBox "No matching record was found", vbInformation, "No Record Found"

    Else

        ' set the form to the found record

        frm.Bookmark = rst.Bookmark 

        rst.FindNext strSearch

    End If
 

 If rst.NoMatch Then

        rst.Close

 '       Call requery1         '<<=== Ignore this part for now

        Set rst = Nothing

    

    Else

        rst.FindPrevious strSearch

        btnFindNext.Enabled = True

        btnFindNext.SetFocus

        CmdFind.Enabled = False

        btnStop.Enabled = True

    End If

    Exit Sub

Mode1:

    If rst.NoMatch Then

      CmdFind.Enabled = True

      CmdFind.SetFocus

      btnFindNext.Enabled = False

      MsgBox "There are no more matching records", vbInformation, "No Record Found"

      btnStop.Enabled = False

    Else

      ' set the form to the found record

      frm.Bookmark = rst.Bookmark  

    End If

Open in new window

0
Comment
Question by:billcute
  • 2
3 Comments
 
LVL 4

Expert Comment

by:oldmanbim
ID: 22711795
I tried to reproduce your error using just your code, but I couldn't.  It works fine using table and forms that I made.

One thing that can cause this error is if DLookup, DCount, etc is called with an inappropriate parameter:
for example: DLookup("x", "tblData","ID=4")  when "x" is NOT a field in tblData.  If you have such a function call in a control source (or other property of your form) that has an inappropriate parameter,
it would cause this error when frm.Bookmark is set equal to rst.Bookmark.
0
 
LVL 4

Author Comment

by:billcute
ID: 22716516
I do call some subs at the On Current Event handler of my data entry form - that seems to affect the Search function.
For example, using some "Ifs Statement", certain controls do "reset" the controls either by "setting the controls to nothing" or "by placing some data in the controls" depending on the "If Statement". If you need a sample db you can request it by clicking on my userid so you could drop me a note. Unfortunately I cannot place my sample db here permanently due to the sensitive nature of my db.
0
 
LVL 4

Accepted Solution

by:
oldmanbim earned 500 total points
ID: 22747737
After looking over your code, I have a few suggestions.

1) The ClearFlow procedure sets many values to "".  When searching, it changes values in table, not just on the form, hence making the form "dirty".  What I would suggest is that the Control Source for each control affected by ClearFlow be removed.  Then whenever the user manually leaves the current record (I believe only through search or adding a new record), you can record the changes in the table; eg:
        Me!Flow = Me.txtFlow.Value
        Me!BldgPlan = Me.cboBPlan.Value
        etc...
This will avoid unnecessary modifications to the data when only searching through records.

2) I might misunderstand but your code in frmW:
    Private Sub Form_BeforeUpdate(Cancel As Integer)
        If Not MandatoryFields Then
            Cancel = True
        End If
        Call UpdateEntryDate
        On Error GoTo 0
        Exit Sub
    End Sub
UpdateEntryDate is called even if Cancel is set to true.  Since the user is required to enter the data before they can leave the record, there is no reason to record the change until they do so.

3) If possible, you could open frmSearch as acDialog.  This way the user CAN'T modify the data and you don't have to check for a dirty form (assuming you use #1 above).  Of course, the user would have to close frmSearch to edit the data in frmS or frmW, and this might not be desireable.

4) Using "BeforeUpdate" can be a little tricky, since there are some functions/actions that are not allowed - for example Me.Refresh and some modifications to the current recordset.  I believe the changes suggested in #1 might make it possible not to use BeforeUpdate.  If there is some way around using Form_BeforeUpdate, I suspect your life might be a lot easier.

All that being said, I have written this type of search routine before and in most cases it turns out users are just as comfortable using the built-in Access search function.  In your case have the search look in Address. Another alternative is to simply add a "Search" Listbox to each frmW and frmS.  The attached snippet could be used to search:

Private LastSearch as String
Private Sub lbxSearch_Click()
    Dim booFound as Boolean
    If LastSearch <> Me.lbxSearch Then
        ' Find First
        booFound = SearchDAO("Address=""" & Me.lbxSearch & """, Me.Recordset, True)
    Else
        ' Find Next
        booFound = SearchDAO("Address=""" & Me.lbxSearch & """, Me.Recordset, False)
    End If

    If Not booFound Then
        ' This should never happen(!?) because all addresses in lbxSearch come from the recordset
        MsgBox "That address was not found"
    end if
End Sub

Since I just type this in by hand the code might be a bit buggy, but I have used similar code many times before.

-b
' Method: SearchDAO

' Param:  FindString (String);

'   A SQL find string to be used for FindFirst/FindNext

' Param:  MeRst (ByRef DAO.Recordset);

'   A recordset - if from a form, then Me.Recordset

' Param:  FindFirst (Optional Boolean = True);

'   Use FindFirst or FindNext

' Param:  Follow (Optional Boolean = True);

'   If true, sets MeRst Bookmark to the found record

' Return: Boolean;

'   True if a matching record was found, false if not.

Public Function SearchDAO( _

        ByVal FindString As String, _

        ByRef MeRst As DAO.Recordset, _

        Optional ByVal FindFirst As Boolean = True, _

        Optional ByVal Follow As Boolean = True _

        ) As Boolean
 

    Dim rst As DAO.Recordset
 

    SearchDAO = False ' Default

    Set rst = MeRst.Clone

    

    If rst.EOF And rst.BOF Then Exit Function

    

    If FindFirst Then

        rst.MoveFirst

        rst.FindFirst FindString

    Else

        rst.Bookmark = MeRst.Bookmark

        rst.FindNext FindString

    End If

    

    If rst.NoMatch Then Exit Function
 

    If Follow Then MeRst.Bookmark = rst.Bookmark

    

    SearchDAO = True

End Function

Open in new window

0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
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 …

743 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

14 Experts available now in Live!

Get 1:1 Help Now