TK421
asked on
NOT A VALID BOOKMARK ERROR
Access 97, Multi-User, Split, Secure, Windows NT (SP 5)
BACKGROUND:
I admin a multi-user split Access 97 DB. This DB is used by 30-50 users in a given day. I maintenance
the DB by compacting it weekly. Each user has a "Workstation" version with queries, forms and reports.
The data side (tables, public modules) of this DB resides on a shared server. I update the workstation
version often and upload it to the server. Each time a user logs into NT, a batch file (in their startup
folder) copies the latest workstation version to their machine.
PROCESS:
Some of the DB Users create records and modify them before they submit them to my department. After
I receive a submittal and login the data, that Record is locked from further changes until it enters
the users Inbox (continuous form). Each group has a continuous form for easier use in logging, adding
dates, etc. With the continous forms, the user does not have to type in each Record number - just select
from a list.
For an unknown reason, I have been getting the "Not A Valid Bookmark" error a lot lately. This error
is sometimes accompanied with the following error message too: "Unrecognized Database Format…". When
this error occurs, I have to repair or restore from a backup.
Until now, I would simply get all users to close the DB, I'd repair the DB or recover from a backup
(module code that outputs all objects to a separate DB each hour). After getting back "online", all
would appear okay and I would continue until the next "Not A Valid Bookmark" error occurred. I understand
Access had a bug with bookmarks, but my understanding is Service Pack (SP) 2 of Window NT fixed the
bookmark bug. We are currently running SP 5 - soon SP 6.
DISCOVERED DATA PROBLEMS:
My "Main Table" has a Primary Key called [ID]. It's an indexed Auto-Numbering field with NO duplicates.
Yesterday I discovered this NO duplicates field had 6 (six) duplicate ID numbers. This has been the
case for some time. Doing a repair, or Microsoft Access Decompile or using the Jetcomp.exe DID NOT eliminate
the duplicate records in the NO duplicates field. I did not know this was happening. So my table has
had (for a long while) duplicate records where I thought there were none. Here's how I fixed this problem:
1) Made "structure only" copy of the corrupt table.
2) Set "Structure Only" ID field to "Indexed - No Duplicates, Primary Key.
3) With an Append Query, I imported all the records into the Structure Only table. Doing this removed
the six duplicate ID numbers.
SInce I've cleaned the data, the "Not A Valid Bookmark" error has occured again. Now that I have identified
and fixed this data problem, I must stop this from reoccurring.
MY SUSPICIONS:
1) Undo Records
2) Copying of Records
3) RecordSet Clone
4) Pressing the ESC key while creating new records
1) On some of the forms, I have an "Undo" button. This Undo button behaves like the Undo command. The
command button will undo any changes if changes are made. After viewing Microsoft's Knowledge Base,
I found this page:
http://support.microsoft.com/support/kb/articles/Q123/5/95.asp?LN=EN-US&SD=gn&FR=0&qry=bookmark&rnk=1&src=DHCS_MSPSS_gn_SRCH&SPR=ACC97
Do I need this type of Undo code on my forms?
2) Copying of records. When a user creates a Record, they often like to copy all the data to a new record.
The new record will have only minor changes from the original. Here's code from a command button on
a popup form:
bAllowNew = True
DoCmd.Close
'DoCmd.SetWarnings False
DoCmd.RunCommand acCmdSelectRecord
DoCmd.RunCommand acCmdCopy
'DoCmd.GoToRecord , , acNewRec
DoCmd.RunCommand acCmdPasteAppend
'DoCmd.RunCommand acCmdPaste
DoCmd.SetWarnings True
bAllowNew = False
'DoCmd.Requery
DoCmd.GoToRecord , , acLast
End Sub
'-------------------------
Is this code okay?
3)RecordSet Clone
I have this code on some forms:
On Form Current:
If NewRecord _
And (bAllowNew = False) _
And RecordsetClone.RecordCount
' We are in a new record, but did not get there by hitting the command button
' Instead the user twiddled the mouse wheel
DoCmd.GoToRecord , , acPrevious ' just go back the the previous record
Exit Sub
bAllowNew = False ' Reset the flag so we will catch subsequent mouse fidgets.
End If
'=========================
Or on Form Open:
DoCmd.Hourglass False
On Error GoTo end1
Dim r As Recordset
Dim s As String
Set r = Me.RecordsetClone
If r.EOF And r.BOF Then
Cancel = True
s = "No Request Found. The Number you entered does not exist. " & _
"Please verify what you entered and try again."
MsgBox "Your Inbox is Empty.", vbInformation,
End If
end1:
'=========================
Is it possible that Recordset Cloning is causing bookmark errors? Should I be clearing all bookmarks
by using this docmd?:
DoCmd.RunCommand acCmdBookmarksClearAll
4) I've recently learned that pressing the ESC key (while creating new records) causes skips in autonumbering
fields. Is it possible this is affecting bookmarks and causing errors?
Basically, I am trying to solve my reoccurring "Not A Valid Bookmark" error. And I am not sure if my
suspicions are related to this error or not. I'd like to begin by ruling out some possibilities. Have
other users experienced the bookmark error reoccurring like I am? Thanks!
TK421
BACKGROUND:
I admin a multi-user split Access 97 DB. This DB is used by 30-50 users in a given day. I maintenance
the DB by compacting it weekly. Each user has a "Workstation" version with queries, forms and reports.
The data side (tables, public modules) of this DB resides on a shared server. I update the workstation
version often and upload it to the server. Each time a user logs into NT, a batch file (in their startup
folder) copies the latest workstation version to their machine.
PROCESS:
Some of the DB Users create records and modify them before they submit them to my department. After
I receive a submittal and login the data, that Record is locked from further changes until it enters
the users Inbox (continuous form). Each group has a continuous form for easier use in logging, adding
dates, etc. With the continous forms, the user does not have to type in each Record number - just select
from a list.
For an unknown reason, I have been getting the "Not A Valid Bookmark" error a lot lately. This error
is sometimes accompanied with the following error message too: "Unrecognized Database Format…". When
this error occurs, I have to repair or restore from a backup.
Until now, I would simply get all users to close the DB, I'd repair the DB or recover from a backup
(module code that outputs all objects to a separate DB each hour). After getting back "online", all
would appear okay and I would continue until the next "Not A Valid Bookmark" error occurred. I understand
Access had a bug with bookmarks, but my understanding is Service Pack (SP) 2 of Window NT fixed the
bookmark bug. We are currently running SP 5 - soon SP 6.
DISCOVERED DATA PROBLEMS:
My "Main Table" has a Primary Key called [ID]. It's an indexed Auto-Numbering field with NO duplicates.
Yesterday I discovered this NO duplicates field had 6 (six) duplicate ID numbers. This has been the
case for some time. Doing a repair, or Microsoft Access Decompile or using the Jetcomp.exe DID NOT eliminate
the duplicate records in the NO duplicates field. I did not know this was happening. So my table has
had (for a long while) duplicate records where I thought there were none. Here's how I fixed this problem:
1) Made "structure only" copy of the corrupt table.
2) Set "Structure Only" ID field to "Indexed - No Duplicates, Primary Key.
3) With an Append Query, I imported all the records into the Structure Only table. Doing this removed
the six duplicate ID numbers.
SInce I've cleaned the data, the "Not A Valid Bookmark" error has occured again. Now that I have identified
and fixed this data problem, I must stop this from reoccurring.
MY SUSPICIONS:
1) Undo Records
2) Copying of Records
3) RecordSet Clone
4) Pressing the ESC key while creating new records
1) On some of the forms, I have an "Undo" button. This Undo button behaves like the Undo command. The
command button will undo any changes if changes are made. After viewing Microsoft's Knowledge Base,
I found this page:
http://support.microsoft.com/support/kb/articles/Q123/5/95.asp?LN=EN-US&SD=gn&FR=0&qry=bookmark&rnk=1&src=DHCS_MSPSS_gn_SRCH&SPR=ACC97
Do I need this type of Undo code on my forms?
2) Copying of records. When a user creates a Record, they often like to copy all the data to a new record.
The new record will have only minor changes from the original. Here's code from a command button on
a popup form:
bAllowNew = True
DoCmd.Close
'DoCmd.SetWarnings False
DoCmd.RunCommand acCmdSelectRecord
DoCmd.RunCommand acCmdCopy
'DoCmd.GoToRecord , , acNewRec
DoCmd.RunCommand acCmdPasteAppend
'DoCmd.RunCommand acCmdPaste
DoCmd.SetWarnings True
bAllowNew = False
'DoCmd.Requery
DoCmd.GoToRecord , , acLast
End Sub
'-------------------------
Is this code okay?
3)RecordSet Clone
I have this code on some forms:
On Form Current:
If NewRecord _
And (bAllowNew = False) _
And RecordsetClone.RecordCount
' We are in a new record, but did not get there by hitting the command button
' Instead the user twiddled the mouse wheel
DoCmd.GoToRecord , , acPrevious ' just go back the the previous record
Exit Sub
bAllowNew = False ' Reset the flag so we will catch subsequent mouse fidgets.
End If
'=========================
Or on Form Open:
DoCmd.Hourglass False
On Error GoTo end1
Dim r As Recordset
Dim s As String
Set r = Me.RecordsetClone
If r.EOF And r.BOF Then
Cancel = True
s = "No Request Found. The Number you entered does not exist. " & _
"Please verify what you entered and try again."
MsgBox "Your Inbox is Empty.", vbInformation,
End If
end1:
'=========================
Is it possible that Recordset Cloning is causing bookmark errors? Should I be clearing all bookmarks
by using this docmd?:
DoCmd.RunCommand acCmdBookmarksClearAll
4) I've recently learned that pressing the ESC key (while creating new records) causes skips in autonumbering
fields. Is it possible this is affecting bookmarks and causing errors?
Basically, I am trying to solve my reoccurring "Not A Valid Bookmark" error. And I am not sure if my
suspicions are related to this error or not. I'd like to begin by ruling out some possibilities. Have
other users experienced the bookmark error reoccurring like I am? Thanks!
TK421
ASKER
JDettman,
After a couple of weeks, here's the latest:
I examined all of the code in the DB and 'commented out' all of the code where requery existed. For now, it seems to have worked. No problems!
Not sure about your #3 comment. As you stated, i am having problems cancelling the event:
"Problem is though, the load event is not cancelable, so you'll need to close the form. You could also force the issue with a movelast after opening the recordset, but I don't like forcing Access to do things out of it's normal order as it seems that's when I start having problems. Better to use the load event for the check."
How can i cancel the 'on load' event?
Thanks!
After a couple of weeks, here's the latest:
I examined all of the code in the DB and 'commented out' all of the code where requery existed. For now, it seems to have worked. No problems!
Not sure about your #3 comment. As you stated, i am having problems cancelling the event:
"Problem is though, the load event is not cancelable, so you'll need to close the form. You could also force the issue with a movelast after opening the recordset, but I don't like forcing Access to do things out of it's normal order as it seems that's when I start having problems. Better to use the load event for the check."
How can i cancel the 'on load' event?
Thanks!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
JDettman,
Glad to hear that code appears okay. Well since I removed the duplicate records (in a NO duplicates field!!!) and I commented out all the 'requery code', all appears just fine. So I am satisfied and glad to see things are stable (for now! :) Thanks for your reply and continued followups. I appreciate all of your assistance. Take care.
TK421
Glad to hear that code appears okay. Well since I removed the duplicate records (in a NO duplicates field!!!) and I commented out all the 'requery code', all appears just fine. So I am satisfied and glad to see things are stable (for now! :) Thanks for your reply and continued followups. I appreciate all of your assistance. Take care.
TK421
But let's cover your questions as there are legitimate reasons for "invalid bookmark" as well.
1. No. The code presented in the article creates a "AfterUndo event" where you can pop-up a message or perform some action. It's not changing the undo process itself.
2. Nothing wrong with that code, but here's one gotcha. Requerying a form invalidates all bookmarks. So if you have any saved, after the requery they’re no good. Instead, save the primary key of a record and use that to find the record again.
3. Only thing that jumps out at me is that your exiting the sub before bAllowNew gets set.
On the form Open code, it's in the wrong place. Move it to the Load event. In the open event, the first record may or may not be available as yet and you check may fail. Problem is though, the load event is not cancelable, so you'll need to close the form. You could also force the issue with a movelast after opening the recordset, but I don't like forcing Access to do things out of it's normal order as it seems that's when I start having problems. Better to use the load event for the check.
4. The skipping of Autonumbers is normal when undo is done or canceling adding a record.
I *know* there are internal bugs that cause the invalid bookmark error message, but I'm not sure what conditions cause the problem.
I think also that you need to clean up that MDB by importing everything into a fresh MDB. "Unrecognized Database Format messages are not normal and indicate a problem in the internal structure.
Jim.