?
Solved

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

Posted on 2008-09-30
10
Medium Priority
?
475 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
  • 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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 

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 2000 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

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!

Question has a verified solution.

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

This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
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.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

850 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