Working in Access 2003 but not Access 2007

[genegal]

Afternoon everyone,

I'm having a problem with a database I'm making; I'm using a set of code
that is being used to dynamically update two subforms within a main form,
following the structure of:

MainForm

SubForm1

The code that I'm using in SubForm1 is:

Private Sub Form_Current()

If Me![ID] <> Me.SUBFORM_AGENDA_DATASHEET![ID] Then
Me!SUBFORM_AGENDA_DATASHEET.Form.Bookmark = Me.Bookmark
End If

End Sub

The code in SubForm2 is:

Private Sub Form_Click()

If Me![ID] <> Me.Parent![ID] Then
Me.Parent.Bookmark = Me.Bookmark
End If

End Sub

And I seem to have a problem, I'm given run-time error '3159': "Not a valid
bookmark". The database is made in the ACCDB format (as I constructed it in
Access 2007). But when I sent it to a friend to have a look, I converted the
database to a 2003 format (MDB file). Surprisingly, he didn't have any
problems with operating the mainform and subforms. But when I opened the same
MDB file in my Access, I still arrived at the same error '3159'.

I asked him if he had changed anything to the format of the database, and he
said no, it still retains the original integrity when I sent him the database.

So I'm inclined to think the following:

Either a) my version of Database has something corrupt in VBA, or b) there
is a general problem with Access 2007 in operating this code.

The link to the database can be found here:

http://forums.techguy.org/software-...ccess-automatically-determine-value-2.html#29 (MDB format)

http://forums.techguy.org/software-...ccess-automatically-determine-value-3.html#32 (ACCDB format)

So could I please ask if there's someone who would be willing to check this
for me? I know that the MDB version works in 2003 Access, but I want to know
if the MDB and/or the ACCDB works in Access 2007. Like I said, I'm not sure
if this could be a generic bug in Access 2007, or whether there's something
specifically wrong with my installation.

Thank you very much for your time.

 

[Alex Dybenko]

Hi,
not sure that you can share bookmarks among forms, which has different
recordsources. I suggest to use this method:

Private Sub Form_Current()
If Me![ID] <> Me.SUBFORM_AGENDA_DATASHEET![ID] Then
with Me!SUBFORM_AGENDA_DATASHEET.Form
.recordsetclone.findfirst "id=" & me.ID
if not .recordsetclone.Nomatch then _
.Bookmark = .recordsetclone.Bookmark
end with
End If


--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com

Afternoon everyone,

I'm having a problem with a database I'm making; I'm using a set of code
that is being used to dynamically update two subforms within a main form,
following the structure of:

MainForm

SubForm1

The code that I'm using in SubForm1 is:

Private Sub Form_Current()

If Me![ID] <> Me.SUBFORM_AGENDA_DATASHEET![ID] Then
Me!SUBFORM_AGENDA_DATASHEET.Form.Bookmark = Me.Bookmark
End If

End Sub

The code in SubForm2 is:

Private Sub Form_Click()

If Me![ID] <> Me.Parent![ID] Then
Me.Parent.Bookmark = Me.Bookmark
End If

End Sub

And I seem to have a problem, I'm given run-time error '3159': "Not a
valid
bookmark". The database is made in the ACCDB format (as I constructed it
in
Access 2007). But when I sent it to a friend to have a look, I converted
the
database to a 2003 format (MDB file). Surprisingly, he didn't have any
problems with operating the mainform and subforms. But when I opened the
same
MDB file in my Access, I still arrived at the same error '3159'.

I asked him if he had changed anything to the format of the database, and
he
said no, it still retains the original integrity when I sent him the
database.

So I'm inclined to think the following:

Either a) my version of Database has something corrupt in VBA, or b) there
is a general problem with Access 2007 in operating this code.

The link to the database can be found here:

http://forums.techguy.org/software-...ccess-automatically-determine-value-2.html#29
(MDB format)

http://forums.techguy.org/software-...ccess-automatically-determine-value-3.html#32
(ACCDB format)

So could I please ask if there's someone who would be willing to check
this
for me? I know that the MDB version works in 2003 Access, but I want to
know
if the MDB and/or the ACCDB works in Access 2007. Like I said, I'm not
sure
if this could be a generic bug in Access 2007, or whether there's
something
specifically wrong with my installation.

Thank you very much for your time.

 

[genegal]

Thanks for your response, but it technically doesn't answer my question.

I'm interested in knowing why this code works fine in Access 2003 as an MDB
file, but it doesn't work in Access 2007, neither as an MDB or ACCDB file. I
just did a repair of Access, and the same problem still occurs, so now I'm
left to assume that this is a generic problem with Access 2007 rather than an
individual case.

Could someone else comment on this?

 

[Alex Dybenko]

Hi,
I would rather try to find why it works in 2003, here what access help
states:
"However, you cannot use bookmarks from different Recordset objects
interchangeably, even if they were created from the same source or command."

--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com

 

[genegal]

Your last point is rather interesting. I'm currently asking other people for
help on this matter, and double checking to see why it works in Access 2003,
but not in Access 2007.

Where exactly did the information come from? Was it from Access itself, or
from MSDN? Is there any Access revision that this applies to (i.e.
97/2000/2003/etc onwards)?

Thanks for that piece of information.

 

[Alex Dybenko]

In access help, also MSDN:
http://msdn.microsoft.com/en-us/library/bb221142.aspx

--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com

 

[genegal]

Well, just to get your opinion, do you think this is a case of a bug in
Access 2007 (since it works in Access 2003 without any errors), or maybe
there's something new in Access 2007 that catches this error?

Should I maybe try to trap this error, and some how have it forced to work?

 

[John Spencer]

I'm surprised that the code has ever worked with any degree of reliability.
Bookmarks are unique to the recordset of each form and subform.

If I have one record in the recordset for the main form and 30 records for the
recordset in the sub-form then you can see immediately that it would not be
possible to match all the records in the subform with records in the main
form. Even if you have the same records in the two recordsets, the bookmarks
may be different if the two recordsets are in a different order.

I would use a different technique to do this. Assumption is that ID is a
number field - if not then you would need to slightly change the FindFirst

'UNTESTED code snippet follows
If Me![ID] <> Me.SUBFORM_AGENDA_DATASHEET![ID] Then
Dim rstAny as DAO.RecordSet
Set rstAny = Me.SUBFORM_AGENDA_DATASHEET.Form.RecordSetClone

rstAny.FindFirst "ID=" & Me.ID
If rstAny.NoMatch = False Then
Me.SUBFORM_AGENDA_DATASHEET.Form.BookMark = rstAny.BookMark
Else
'Do something to respond to missing record in the recordset
End If
End If

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

Afternoon everyone,

I'm having a problem with a database I'm making; I'm using a set of code
that is being used to dynamically update two subforms within a main form,
following the structure of:

MainForm

SubForm1

The code that I'm using in SubForm1 is:

Private Sub Form_Current()

If Me![ID] <> Me.SUBFORM_AGENDA_DATASHEET![ID] Then
Me!SUBFORM_AGENDA_DATASHEET.Form.Bookmark = Me.Bookmark
End If

End Sub

The code in SubForm2 is:

Private Sub Form_Click()

If Me![ID] <> Me.Parent![ID] Then
Me.Parent.Bookmark = Me.Bookmark
End If

End Sub

And I seem to have a problem, I'm given run-time error '3159': "Not a valid
bookmark". The database is made in the ACCDB format (as I constructed it in
Access 2007). But when I sent it to a friend to have a look, I converted the
database to a 2003 format (MDB file). Surprisingly, he didn't have any
problems with operating the mainform and subforms. But when I opened the same
MDB file in my Access, I still arrived at the same error '3159'.

I asked him if he had changed anything to the format of the database, and he
said no, it still retains the original integrity when I sent him the database.

So I'm inclined to think the following:

Either a) my version of Database has something corrupt in VBA, or b) there
is a general problem with Access 2007 in operating this code.

The link to the database can be found here:

http://forums.techguy.org/software-...ccess-automatically-determine-value-2.html#29 (MDB format)

http://forums.techguy.org/software-...ccess-automatically-determine-value-3.html#32 (ACCDB format)

So could I please ask if there's someone who would be willing to check this
for me? I know that the MDB version works in 2003 Access, but I want to know
if the MDB and/or the ACCDB works in Access 2007. Like I said, I'm not sure
if this could be a generic bug in Access 2007, or whether there's something
specifically wrong with my installation.

Thank you very much for your time.

 

[genegal]

I appreciate your input John, however, the question still remains: Why does
it work in Access 2003 (and it has worked for many other people who use it,
and they haven't complained about it) and not Access 2007?

I essentially wanted to mimic a splitform as a sub from as:

MainForm

Splitform

But since Splitforms don't exist as subforms, I tried creating my own in the
form of:

MainForm

SubForm1

which has worked for others before. But seeing how I can't find a solution
as of yet, I'm thinking about changing it to:

MainForm

SubForm1
SubForm 2

and see if anything that can be linked.

 

[Jeanette Cunningham]

A2007 is a bit more inflexible when code is not written as expected.
Some versions of access have an uncanny ability to guess correctly when code
is less than perfect.
Most likely, that code that works in A2003 will one day refuse to work in
that same application, when access guesses wrongly about your bookmark code.

A2007 is more picky about code that is less than perfect.
It is always a good idea to use the accepted way of writing vba code for an
access database.
There are lots of examples on these discussion groups of how to do that.


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

 

[genegal]

Thank you for your response Jeanette.

I suppose now I have nothing else to do but to refine the code until it's
declared properly.