Solved

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

Posted on 2008-10-14
3
418 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
[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
  • 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

Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

Question has a verified solution.

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

Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
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.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

623 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