Bookmark not working (again) in Access 2007

[DIH]

I posted the following (see below) a number of months ago. The response
I got back then was to make sure that the reference to Microsoft DAO 3.6
Object Library was selected and set above the Microsoft Active X data
Objects 2.5 Library. I did this and it solved the issue.
Well, the problem is back and I don't know why. I haven't changed
anything in the database (and the references are set correctly).

Here is the original post:
=======================================================================
I have a database that tracks accidents. There are about 800 employees.
Each employee in the employee table called, "tblEmployees" has an id
number (this is the primary key - called EmpNumber, data type is text).
This table is related to a table called, "tblAccidentInvestData". The
related foreign key field in this table is called, "EMPNUM".
The primary key in tblAccidentInvestData is called, "AccidentInvestID".

A main data entry form called, "frmInputAccidentInvest" has a subform
called, "subfrmAccidentInvest". This subform puts the data into the
tblAccidentInvestData table (hence the one to many relationship between
the employee and the accident investigation data).

Also, this data entry form gets opened by a main menu form that has a
combobox list of all employee's. The user selects an employee from the
combobox and clicks a command button to open to that employee in the
data entry form.

The main data entry form shows the employee's name, id number and
department (all taken from the tblEmployees table).
The form has a combobox that lets the user select another employee and
then the subform will show the record of any accidents that employee had.

I also have another form (that gets opened from either the main menu
form or the main data entry form) that will show (in read only) all the
accidents that have been entered to date. On this form is a command
button which when clicked will hide the form and reopen the
frmInputAccidentInvest form and go directly to that employee and the
correct accident report.

The following code works perfectly in all versions of Access except
2007. In 2007, the frmInputAccidentInvest will open up to an arbitrary
employee/accident report (not the correct one).

Private Sub Command21_Click()
Dim frm As Form
Dim frmSub As Form

'Hide the read only form
Me.Visible = False
'Open the form data entry form.
DoCmd.OpenForm "frmInputAccidentInvest"
Set frm = Forms("frminputaccidentinvest")

'Find the employee on the main data entry form.
With frm.RecordsetClone
.FindFirst "Empnumber = '" & Me.EMPNUM & "'"

'Move to the found record.
frm.Bookmark = .Bookmark

'Find the correct accident investigation record in the subform.

Set frmSub = frm.subfrmAccidentInvest.Form
With frmSub.RecordsetClone
.FindFirst "AccidentInvestID = " & Me.AccidentInvestID
'Move to the found record.
If .NoMatch Then
Exit Sub
Else
frmSub.Bookmark = .Bookmark

Set frmSub = Nothing
Set frm = Nothing

End If
End With
End With
End Sub

Please note that this code was discovered through some internet
searching as well as trial and error (I am just starting to learn a bit
of vba). Also remember that it works fine in all versions of Access
except 2007.

If there is anything else I need to supply for clarification please let
me know. Thanks in advance for any assistance.

==========================================================================

As usual, any help is greatly appreciated.

Dave

 

[strive4peace]

Hi Dave

after
frm.Bookmark = .Bookmark
try this:
DoEvents

~~~ DoEvents ~~~

DoEvents is used to make VBA pay attention to what is currently
happening and look to see if the OS (Operating System) has any requests
-- including the keyboard

ie: if you have a loop and want to be able to BREAK it with CTRL-BREAK,
put DoEvents into the loop

DoEvents will also update values written to a form by a general
procedure or code behind another form or report

A DoEvents is done when you use MsgBox, or are stepping through code
(since it has to pay attention to the keyboard)

It is a good way to say, "Wake Up!"


~~~~~~~~~~

also, I couldn't help but to notice that you did not change the NAME
property of the command button to something meaningful before you wrote
code ... Command21 is ambiguous; Access does not know what to name
things to it chooses something generic -- you should make all control
NAMEs more specific before you reference them in code

~~~~~~~~~~
"frmInputAccidentInvest will open up to an arbitrary
employee/accident report"

ensure that the form design shows NOTHING in the form filter property

and, to ensure that no filter is saved when it closes, do not save the form:

DoCmd.Close acform, Me.name. acSaveNo

~~~~~~~~~~~
"reopen the frmInputAccidentInvest form and go directly to that employee"

this is not necessarily what your code does...since the form may already
be open, perhaps there is already a filter in effect in which case, you
would need to specifically remove it before your code -- or specifically
CLOSE the form and then open it ... also, if the form is open and you
have added or changed records with another process, they may not show

~~~~~~~~~~~~~

instead of
If .NoMatch Then
Exit Sub
Else
frmSub.Bookmark = .Bookmark

Set frmSub = Nothing
Set frm = Nothing

End If
you should use:

If Not .NoMatch Then
frmSub.Bookmark = .Bookmark
else
msgbox "No match for AccidentInvestID was found"
end if

Set frmSub = Nothing
Set frm = Nothing

~~~~~~~~~~~~~

Warm Regards,
Crystal

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*

 

[DIH]

Hi Dave

after
frm.Bookmark = .Bookmark
try this:
DoEvents

~~~ DoEvents ~~~

DoEvents is used to make VBA pay attention to what is currently
happening and look to see if the OS (Operating System) has any requests
-- including the keyboard

ie: if you have a loop and want to be able to BREAK it with CTRL-BREAK,
put DoEvents into the loop

DoEvents will also update values written to a form by a general
procedure or code behind another form or report

A DoEvents is done when you use MsgBox, or are stepping through code
(since it has to pay attention to the keyboard)

It is a good way to say, "Wake Up!"


~~~~~~~~~~

also, I couldn't help but to notice that you did not change the NAME
property of the command button to something meaningful before you wrote
code ... Command21 is ambiguous; Access does not know what to name
things to it chooses something generic -- you should make all control
NAMEs more specific before you reference them in code

~~~~~~~~~~
"frmInputAccidentInvest will open up to an arbitrary
employee/accident report"

ensure that the form design shows NOTHING in the form filter property

and, to ensure that no filter is saved when it closes, do not save the
form:

DoCmd.Close acform, Me.name. acSaveNo

~~~~~~~~~~~
"reopen the frmInputAccidentInvest form and go directly to that employee"

this is not necessarily what your code does...since the form may already
be open, perhaps there is already a filter in effect in which case, you
would need to specifically remove it before your code -- or specifically
CLOSE the form and then open it ... also, if the form is open and you
have added or changed records with another process, they may not show

~~~~~~~~~~~~~

instead of
If .NoMatch Then
Exit Sub
Else
frmSub.Bookmark = .Bookmark

Set frmSub = Nothing
Set frm = Nothing

End If
you should use:

If Not .NoMatch Then
frmSub.Bookmark = .Bookmark
else
msgbox "No match for AccidentInvestID was found"
end if

Set frmSub = Nothing
Set frm = Nothing

~~~~~~~~~~~~~

Warm Regards,
Crystal

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*

Thank you so much for your help. When I get back to work on Tuesday,
I'll do all the things you mentioned. For now, I restored a backup copy
of the database and everything works fine. I'm now thinking there might
have been some kind of corruption with the database (or some of the
records held within it). What doesn't exactly make sense with the
corruption angle is that the code works fine in Access XP, but doesn't
with Access 2007. I'll put your suggested code into the quote unquote
corrupted copy of the database and see what happens on Tuesday.

Thanks again!

Dave

 

[strive4peace]

Hi Dave,

you're welcome

did you set up your Access 2007 database to be trusted?

MVP Garry Robinson made a video about how to set up a trusted location
for your databases in Access 2007:

http://vb123.blogspot.com/

MVP Tom Wickerath also has a web page for managing macro protection:

Dealing with the Trust Center (Access 2007), by Tom Wickerath
http://www.accessmvp.com/TWickerath/articles/trust.htm


Create, remove, or change a trusted location for your files
http://office.microsoft.com/en-us/access/HA100319991033.aspx

Configure trusted locations and trusted publishers settings in the 2007
Office system
http://technet2.microsoft.com/Offic...122c-42f2-a4a5-1f09a066558f1033.mspx?mfr=true


'~~~~~~~~~ Compile ~~~~~~~~~

Whenever you change code, references, or switch versions, you should
always compile before executing code

from the menu in a VBE (module) window: Debug, Compile

fix any errors on the yellow highlighted lines

keep compiling until nothing happens (this is good!)


Warm Regards,
Crystal

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*

 

[DIH]

Hi Dave,

you're welcome

did you set up your Access 2007 database to be trusted?

MVP Garry Robinson made a video about how to set up a trusted location
for your databases in Access 2007:

http://vb123.blogspot.com/

MVP Tom Wickerath also has a web page for managing macro protection:

Dealing with the Trust Center (Access 2007), by Tom Wickerath
http://www.accessmvp.com/TWickerath/articles/trust.htm


Create, remove, or change a trusted location for your files
http://office.microsoft.com/en-us/access/HA100319991033.aspx

Configure trusted locations and trusted publishers settings in the 2007
Office system
http://technet2.microsoft.com/Offic...122c-42f2-a4a5-1f09a066558f1033.mspx?mfr=true



'~~~~~~~~~ Compile ~~~~~~~~~

Whenever you change code, references, or switch versions, you should
always compile before executing code

from the menu in a VBE (module) window: Debug, Compile

fix any errors on the yellow highlighted lines

keep compiling until nothing happens (this is good!)


Warm Regards,
Crystal

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*

Yes, the db is fully trusted. I also make sure to do a compile. I still
think the db got corrupted somehow. I wonder if Access 2007 is more
finicky than the older versions. I now remember in a different db that
there was one corrupted record in a table (it had what looked like
oriental characters in one of the fields). When I went to run a report
based on a query from that table, Access 2007 gave an error message (I
don't remember now what it was), but Access XP ran the report OK.

Thanks again for your help and the links you provided. It's obvious that
you truly care about helping fellow access users! I will post back next
week with the results.

Dave

 

[strive4peace]

Hi Dave,

"Thanks again for your help and the links you provided. It's obvious
that you truly care about helping fellow access users! "

thank you! ... you're welcome

"there was one corrupted record in a table"

it might be best to make a blank database and import your objects.

~~~~~~~~~~~~~
Export tables
If you think there may be corruption in tables, export each table to a
format that Access recognizes from your working database -- try XML
format since it retains more structure information -- second choice
would be Excel, then CSV (text)

then, import the tables into a blank database

change data types as necessary, set up relationships, and lay out your
relationship diagram

close, backup database, then open again

then, import the other objects you need -- queries, forms, report,
macros, and modules

~~~

then, link to any library references you know you need, compile code, ...


Warm Regards,
Crystal

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*