Solved

You Cancelled the previous operation in bookmark function when using split form in Access 2007

Posted on 2008-09-30
10
459 Views
Last Modified: 2012-05-05
I would like to turn my single record form with a combo box lookup into a split form but I am having trouble. If I run the form as a single form all works well including my combo box lookup. However when I set the form to split form I get a message that says "you cancelled the previous operation" and the cursor highlights the line "Me.Bookmark = rs.Bookmark" when I debug. I am using the following code. Can anyone suggest why it works in single user and not in split user format?
Private Sub cboFindNurse_AfterUpdate()
    ' Find the record that matches the control.
    Dim rs As DAO.Recordset    
    Set rs = Me.Recordset.Clone
    rs.FindFirst "[NurseID] = " & Str(Nz(Me![cboFindNurse], 0))
    If Not rs.NoMatch Then Me.Bookmark = rs.Bookmark
    If Not rs Is Nothing Then
        rs.Close
        Set rs = Nothing
    End If
End Sub
0
Comment
Question by:Rob4077
[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
  • 6
  • 4
10 Comments
 
LVL 85
ID: 22603742
Place a breakpoint in your code at the "Set rs=" line ... when you run this code and enter Break mode, can the code actually find the value of cboFindNurse? A split form, IIRC, is basically a form/subform setup and your call to cbo may no longer be correct ...

You should also use

Me.RecordsetClone

instead of

Me.Recordset.Clone

There is a subtle distinction, and while you may never have trouble with your construct I've seen it bite enough people to mention this to you. The first method I show is the correct way to reference the RecordsetClone of the FORM ... in some cases, an Access form can use an ADO recordset as the Form's Recordset, which would invalidate your REcorset.Clone code ... however, calling Me.Recordsetclone will always get you a DAO recordset.
0
 

Author Comment

by:Rob4077
ID: 22603806
Thanks for the tip on use of recordset.clone versus recordsetclone. I have changed my code but the same problem persists.
I tried entering break mode and yes it seems to find the record. In the example I tested I checked the value of me.recordsetclone.NurseId in the immediate window and it was 1001 and the value of me.nurseid was 166 immediately after the rs.Findfirst .... step. It next gets to the test "If Not rs.NoMatch Then" and the value of rs.NoMatch is False so it steps to the  "me.bookmark = rs.bookmark" at which point I get the error message.
In addition I have tried doing a compress, then a decomp then I created a new database and copied accross all my objects but the problem persists.
0
 

Author Comment

by:Rob4077
ID: 22603841
Ok, I decided to create a new combo box. I renamed the other one and gave my new one the same name as the original and copied across all the formatting and code and now it works but why??? What was wrong with my original???
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:Rob4077
ID: 22603875
Ok I think I have worked out why it's happening. The combo box I am using is for lookup purposes only and so is unbound. I don't want it to show in the datasheet so I narrowed the form to zero to make it disappear, otherwise it will display the currently selected details against every row which is wrong. As soon as I do that the lookup crashes. So is there anything I can do to make that column not appear in the datasheet?
0
 
LVL 85
ID: 22603878
I'd say you've got some issues with your database, even though you just build a new one. Did you have any issues when your copied the objects?
0
 
LVL 85
ID: 22603883
You can use this code:

Me.cboFindNurse.ColumnHidden = True

in the Open/Load event of your database.
0
 

Author Comment

by:Rob4077
ID: 22603912
Tried your code but had the same problem. Seems that if the column is hidden you can't use it as a lookup. I tried setting the column width to 0.01 and that's as good as I'll get. You can't see it but it works. I think I will quit while I am ahead. Thanks for your input and for leading me to the solution.
0
 

Author Comment

by:Rob4077
ID: 22603925
Do you want to add any more comments before I close this question?
0
 
LVL 85

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 500 total points
ID: 22604879
<Seems that if the column is hidden you can't use it as a lookup>

Yes you can. I regularly do this in other applications, and refer to hidden textboxes (i.e. "columns" in datasheet view). Based on this and other questions, I believe you're running into some serious corruption issues ... you should backup your work regularly to avoid losing too much if/when this happens.
0
 

Author Comment

by:Rob4077
ID: 22610378
Oh. Thanks for the warning. Looks like another re-install. I have had a really, really bad run with MS Access 2007 both the first time I installed it a year ago and again now after my second install.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

717 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