473,836 Members | 1,582 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

recordset.bookm ark problem

Seth Schrock
2,965 Recognized Expert Specialist
I have the following subroutine that I'm using to bookmark the current record, do a requery of the form, and then go back to the record that I bookmarked. Here is my code:
Expand|Select|Wrap|Line Numbers
  1. Private Sub FormRefresh()
  2. Dim varBookmark As Variant
  3.  
  4. With Me.sfrmCrimes.Form
  5.     varBookmark = .Recordset.Bookmark
  6.     .Requery
  7.     If IsEmpty(varBookmark) Then
  8.         MsgBox "Bookmark is empty"
  9.     Else
  10.         .Recordset.Bookmark = varBookmark
  11.     End If
  12. End With
  13.  
  14. End Sub
When it runs, it stops on line #10 and says
Run-time error '3129':
Not a valid bookmark.


If I select "Debug", line #10 is highlighted. If I then drag it up to line #7 and then step through the code using F8, then the code runs fine. This defies all troubleshooting methods that I have ever heard of, so I have no idea how to test this. I initially put in the If/Then statement to test varBookmark as I wanted to see if it wasn't getting passed the bookmark for some reason. However, I never get the "Bookmark is empty" message. What am I doing wrong?

This is the first time that I have ever used the Recordset.Bookm ark property. Here is the link that I was trying to follow when I created this: MSDN Recordset.Bookm ark Property
Dec 29 '12
12 16075
zmbd
5,501 Recognized Expert Moderator Expert
:)
Here's something I've done - and very often
.
(Seth, this is an example of one of those things that Neopa pointed out that could be handled directly by the form within the Access structure; however, I have my reasons ;-) )

The form this code is in is setup much like the one in given in the example database Many-to-Many-to-Many relationship problem wherein the form is bound to a record set, direct record manipulation is prohibited, and I have a set of unbound controls in the footer for new record creation.

The reason behind this setup is that I have to have a confirmation that the information entered is correct as once the record is created, it cannot be changed as a lot number is created before being passed to this part of the code. The lot numbers can only be assigned once and have to have a strict tracking.... no mistakes, no omissions, or the paperwork nightmare never ends!

So, to the point which happens at line 12 and then 16 thru 19:

Expand|Select|Wrap|Line Numbers
  1. '<<<Leading Code Omitted>>>
  2. 'Create new entry into the tbl_inventory and refresh the form
  3.     '
  4.     'Setup for the record set
  5.     Set zrs = zdb.OpenRecordset("tbl_inventory", dbOpenDynaset)
  6.     '
  7.     'Pull the information from the form and the new id
  8.     With zrs
  9.         .AddNew
  10. '<Sorry, removed this section of the code, just updates the fields.>
  11.         .Update
  12.         .Bookmark = .LastModified
  13.     End With
  14.     '
  15.     'requery the form and move to the last modified record
  16.     With Me
  17.         .Requery
  18.         .Bookmark = zrs.Bookmark
  19.     End With
  20.     '
  21.     'Close the current recordset,
  22.     zrs.Close
  23.     Set zrs = Nothing'
  24. '<<<Ending Code Omitted>>>
SO, I take a look at the bookmark from the VBA's copy of the book (line 11) and tell the form to turn to that page in its copy of the book (lines 15 thru 18).

The point being that if we think of these as three ring binders, then when several people are working, there are several three ring binders open and pages being added to these binders - all of these binders are of course just working copies of the underlying table (over simplified of course). As the user needs to be able to find the record just added to the underlying table once the form is refreshed, and 8 other people might have just added a record too, I thought it nice to just bookmark the VBA copy and then transfer that bookmark over to the Form's copy after being updated... something like calling a friend and telling them that you're on page 8 of the same book you're both reading.
Dec 31 '12 #11
Stewart Ross
2,545 Recognized Expert Moderator Specialist
I found an interesting problem with the use of a bookmark property in the way suggested by the earlier posts in this thread. Although this was not in the context of a subform, the general principle was the same: the form's current bookmark property was stored (as a result of an After Update event in my case), a requery is done, and the bookmark is restored to locate the same record as was previously in focus.

The problem occurred in the context of a department form in which the name of the department is editable, and is ordered in alphabetical order in the underlying query on which the form was based. If the edit undertaken changes the relative order of the record that was edited then restoring the former bookmark after the form requery restores focus to the wrong record. Reason for this is that the bookmark is referring to an absolute position in the recordset, but if the relative position of the wanted record has changed this invalidates the absolute position previously stored in the bookmark.

I resolved this by going back to the approach shown in the MS examples and also advocated by Smiley - after the requery setting a recordset to the form's recordsetclone, using FindFirst to find the record concerned, then setting the bookmark property of the form to the bookmark of record found in the cloned recordset.

In short, if an edit has taken place which changes the relative order of the recordset concerned then restoring the pre-edit bookmark after a requery may result in the bookmark locating a different record than was originally set. The recordsetclone/find the record and rebookmark approach does not suffer from this problem.

I have not tested what may happen in a multi-user environment but by inference if a user adds a new record which could change the existing sort order of the recordset used by the form then the same problem with restoring a pre-edit bookmark is likely to result.

-Stewart
Jan 8 '13 #12
NeoPa
32,584 Recognized Expert Moderator MVP
Stewart:
The recordsetclone/find the record and rebookmark approach does not suffer from this problem.
I don't doubt this point Stewart, but I would point out that the beauty of the whole Bookmark concept is that it obviates the need for doing another find through an index. Getting around the problem, in such a way that loses this benefit, seems disappointing at best.

That said, if all you say is true (and I'm not so stupid that I would need to be convinced of that again.), it may still be a necessary evil :-(
Jan 9 '13 #13

Sign in to post your reply or Sign up for a free account.

Similar topics

8
2078
by: Dave | last post by:
Hi all, I've been trying to figure this out for the last day and a half and it has me stumped. I've got a web application that I wrote that keeps track of trading cards I own, and I'm moving it from an Access 2000 database to a SQL Server 2000 database. Everything worked perfectly in Access, but I'm having trouble getting data to display in SQL Server. For reference, here's the query. It's big and nasty, but I thought
6
3738
by: HKM | last post by:
Hello, I have a query engine that builds the SQL Query to obtain the recordSet. Following is an Exmaple Query that my QueryBuilder outputted SELECT * FROM BookInfo WHERE BookName LIKE '*cobol*' When I use this query to build recordset I get empty recordSet back when the BookInfo table has entries that qualify for this criteria. To
3
2388
by: DrFoo | last post by:
(Access 97 and 2003) Hi, Here's a smippit of code that works correctly about 95% of the time. This part of the algorithm... - find the first value = true - if found, find next value = false - if found, find next value = true
2
8615
by: corepaul | last post by:
I am fairly new to Access and I have a problem trying to use bookmarks with a recordset. I have a recordset dimensioned as, Dim rstFoodDesc As ADODB.Recordset ' recordset Dim bMark As Variant ' variable to store bookmark I open the recordset with the statements,
3
4807
by: Wayne Wengert | last post by:
In VB6/ADO I used to use the code below to put all the records that did not have a valid email address into an array which I used later to print mailing labels. I am not aware of a NET equivalent. What is the right way to approach this in NET? Wayne ============================================== varSentToCount = 0 'Count of recipients
2
2116
by: Tony Rice | last post by:
I"d like to hear critiques on the following method for dealing with the back button and bookmarkability problem with AJAX. Whenever I do something on a page with ajax, I add to document.location.hash (which doesn't reload the page but does include the parameters in a bookmark) like so: document.location.hash = document.location.hash + '&param=value'; So the URL gets updated like so:...
5
2470
by: Kaur | last post by:
Hi, I am having a strange bookmark problem. I have a main form called frmSurveyQSubQ. This form has a subform called sfrmRespodent. SfrmRespondent has a sfrm in it called sfrsfrmResponses. I have a list box on main form called lstQuestion that displays all the questions relevant to a survey. Clicking on a question in it fills the second list box "lstSuquestion" with relevant sub questions for selected question in lstQuestion on main...
1
1809
by: Lazster | last post by:
Hi, I'd really appreciate some help here people. I am trying to insert some values in a table in another database and not getting the results I am expecting. I am simply trying to insert values via a connection to a recordset and then close the recordset. However I keep getting a type mismatch on the variable rstMainData. Please can someone help me? The code is as below.
5
2164
by: fieldling | last post by:
I've written the following code to update a recordset but when I run it I get a Run-time error 3020: Update or CancelUpdate without AddNew or Edit. When I debug it highlights the rs.update line. I've serached this forum and others for an answer but no luck. Anyone got any ideas? Thanks Option Compare Database Public Function fImportdata() Dim db As DAO.Database Dim rs As DAO.Recordset Set db = CurrentDb() Set rs =...
1
1880
by: nimeshjaiswal | last post by:
Hi, I m doing a migration project from VB6 to VB.NET 2008. I m facing problem in migration the recordsets to dataset specially with properties in fields and bookmark with recordset. Please..Please... provide the solution asap. Thanks in advance. Nimesh
0
10843
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
10546
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
10589
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
10254
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
9371
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
0
6978
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5648
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
1
4448
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
3
3112
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.