Navigation with Bookmarks

[tstevens]

I am embarrassed to have to ask because what I wish to do seems so trivial.
Basically, I have a form with an underlying table and am using the latter to
prepare a new record. At a certain point, I want to filter all the records
with certain details in common with the new one. After filtering, I wish to
return to my new record. So it looks as if I want to set a bookmark prior to
filtering and to reset my form to it afterwards. I am attempting this from
the click event handler of a command button. My latest attempt looks as
follows:

Option Compare Database

Private Sub cmdApplyFilter_Click()
On Error GoTo Err_cmdApplyFilter_Click
Dim strWhere As String
Dim rst As Recordset
Set rst = Me.RecordsetClone
rst.Bookmark = Me.Bookmark

Code to construct strWhere

Me.Filter = strWhere
Me.FilterOn = True
Me.Bookmark = rst.Bookmark
Me.Counter.Value = DCount("[Counter]", "[Archive]", strWhere)
Exit_cmdApplyFilter_Click:
Exit Sub

Err_cmdApplyFilter_Click:
MsgBox Err.Description
Resume Exit_cmdApplyFilter_Click
End Sub

With the above, I get an immediate type mismatch when I try to Set rst. If
I declare rst as Object, the set command goes through - but when I try to
apply the bookmark later, I get a message that rst is no longer set! (Can't
think what I did to unset it.) It I try to store the bookmark as a variant,
I get an illegal bookmark message when I try to reapply it.

I am using MSAccess 2002. This looks to be relevant since there are
different styles evident in various historic postings; so it likely affects
how I should be declaring rst.

I am new at databases but have experience in VB.Net programming for other
Windows XP applications. The rules for Access appear to me to be something
like VB6.

 

[Ken Sheridan]

No need to be embarrassed. I recall asking a very similar question many
years ago in the old CompuServe MS Access forum. John Vinson, who was then a
mere slip of a lad, answered.

Filtering the form will mess up the bookmark, so what you need to do is grab
the value of the current record's primary key to a variable, filter the form,
find the value of the variable in the filtered form's recordset's clone, then
synchronize the bookmarks. I'll assume the key is called MyID and is a
number data type:

Dim strWhere As String
Dim lngMyID As Long
Dim rst As DAO.Recordset

lngMyID = Me.MyID

' Code to construct strWhere

Me.Filter = strWhere
Me.FilterOn = True

Set rst = Me.RecordsetClone
With rst
.FindFirst "MyID = " & lngMyID
If Not .NoMatch Then
Me.Bookmark = rst.Bookmark
End If
End With

Ken Sheridan
Stafford, England

 

[tstevens]

Thanks, Ken,

That is most helpful. One related detail which I must get right concerns
the DAO declaration. Presumably, I must import a suitable library for this
to work. (When I tried the DAO.Recordset declaration at present, I get a no
find message.) If I just declare As Recordset, I get the Type mismatch to
which I alluded earlier.

So what must I do to bring in the DAO library? I looked for it in related
postings - but have not spotted it because most responders can take it for
granted that the questioner has managed that detail. I imagine there is an
"Import" or some other such command?

Tom Stevens
Toronto

No need to be embarrassed. I recall asking a very similar question many
years ago in the old CompuServe MS Access forum. John Vinson, who was then a
mere slip of a lad, answered.

Filtering the form will mess up the bookmark, so what you need to do is grab
the value of the current record's primary key to a variable, filter the form,
find the value of the variable in the filtered form's recordset's clone, then
synchronize the bookmarks. I'll assume the key is called MyID and is a
number data type:

Dim strWhere As String
Dim lngMyID As Long
Dim rst As DAO.Recordset

lngMyID = Me.MyID

' Code to construct strWhere

Me.Filter = strWhere
Me.FilterOn = True

Set rst = Me.RecordsetClone
With rst
.FindFirst "MyID = " & lngMyID
If Not .NoMatch Then
Me.Bookmark = rst.Bookmark
End If
End With

Ken Sheridan
Stafford, England

I am embarrassed to have to ask because what I wish to do seems so trivial.
Basically, I have a form with an underlying table and am using the latter to
prepare a new record. At a certain point, I want to filter all the records
with certain details in common with the new one. After filtering, I wish to
return to my new record. So it looks as if I want to set a bookmark prior to
filtering and to reset my form to it afterwards. I am attempting this from
the click event handler of a command button. My latest attempt looks as
follows:

Option Compare Database

Private Sub cmdApplyFilter_Click()
On Error GoTo Err_cmdApplyFilter_Click
Dim strWhere As String
Dim rst As Recordset
Set rst = Me.RecordsetClone
rst.Bookmark = Me.Bookmark

Code to construct strWhere

Me.Filter = strWhere
Me.FilterOn = True
Me.Bookmark = rst.Bookmark
Me.Counter.Value = DCount("[Counter]", "[Archive]", strWhere)
Exit_cmdApplyFilter_Click:
Exit Sub

Err_cmdApplyFilter_Click:
MsgBox Err.Description
Resume Exit_cmdApplyFilter_Click
End Sub

With the above, I get an immediate type mismatch when I try to Set rst. If
I declare rst as Object, the set command goes through - but when I try to
apply the bookmark later, I get a message that rst is no longer set! (Can't
think what I did to unset it.) It I try to store the bookmark as a variant,
I get an illegal bookmark message when I try to reapply it.

I am using MSAccess 2002. This looks to be relevant since there are
different styles evident in various historic postings; so it likely affects
how I should be declaring rst.

I am new at databases but have experience in VB.Net programming for other
Windows XP applications. The rules for Access appear to me to be something
like VB6.

 

[tstevens]

Ken,

I folded past experience into your suggestion and declared rst As Object.
That done, your idea worked swimmingly. However, I suspect I am still
missing a fine point. I would appreciate your comment on the declaration of
rst per se.

Tom Stevens,
Toronto

Thanks, Ken,

That is most helpful. One related detail which I must get right concerns
the DAO declaration. Presumably, I must import a suitable library for this
to work. (When I tried the DAO.Recordset declaration at present, I get a no
find message.) If I just declare As Recordset, I get the Type mismatch to
which I alluded earlier.

So what must I do to bring in the DAO library? I looked for it in related
postings - but have not spotted it because most responders can take it for
granted that the questioner has managed that detail. I imagine there is an
"Import" or some other such command?

Tom Stevens
Toronto

No need to be embarrassed. I recall asking a very similar question many
years ago in the old CompuServe MS Access forum. John Vinson, who was then a
mere slip of a lad, answered.

Filtering the form will mess up the bookmark, so what you need to do is grab
the value of the current record's primary key to a variable, filter the form,
find the value of the variable in the filtered form's recordset's clone, then
synchronize the bookmarks. I'll assume the key is called MyID and is a
number data type:

Dim strWhere As String
Dim lngMyID As Long
Dim rst As DAO.Recordset

lngMyID = Me.MyID

' Code to construct strWhere

Me.Filter = strWhere
Me.FilterOn = True

Set rst = Me.RecordsetClone
With rst
.FindFirst "MyID = " & lngMyID
If Not .NoMatch Then
Me.Bookmark = rst.Bookmark
End If
End With

Ken Sheridan
Stafford, England

I am embarrassed to have to ask because what I wish to do seems so trivial.
Basically, I have a form with an underlying table and am using the latter to
prepare a new record. At a certain point, I want to filter all the records
with certain details in common with the new one. After filtering, I wish to
return to my new record. So it looks as if I want to set a bookmark prior to
filtering and to reset my form to it afterwards. I am attempting this from
the click event handler of a command button. My latest attempt looks as
follows:

Option Compare Database

Private Sub cmdApplyFilter_Click()
On Error GoTo Err_cmdApplyFilter_Click
Dim strWhere As String
Dim rst As Recordset
Set rst = Me.RecordsetClone
rst.Bookmark = Me.Bookmark

Code to construct strWhere

Me.Filter = strWhere
Me.FilterOn = True
Me.Bookmark = rst.Bookmark
Me.Counter.Value = DCount("[Counter]", "[Archive]", strWhere)
Exit_cmdApplyFilter_Click:
Exit Sub

Err_cmdApplyFilter_Click:
MsgBox Err.Description
Resume Exit_cmdApplyFilter_Click
End Sub

With the above, I get an immediate type mismatch when I try to Set rst. If
I declare rst as Object, the set command goes through - but when I try to
apply the bookmark later, I get a message that rst is no longer set! (Can't
think what I did to unset it.) It I try to store the bookmark as a variant,
I get an illegal bookmark message when I try to reapply it.

I am using MSAccess 2002. This looks to be relevant since there are
different styles evident in various historic postings; so it likely affects
how I should be declaring rst.

I am new at databases but have experience in VB.Net programming for other
Windows XP applications. The rules for Access appear to me to be something
like VB6.

 

[Ken Sheridan]

For some time now ADODB (ActiveX Data Objects) has been the default data
access technology in Access. However, there is no reason why DAO can't be
used, or indeed why both technologies can't be used together in the same
database. There is of course a potential ambiguity if references to both
libraries are in place, which is why I specified the DAO library. Otherwise
the ADODB library would probably have been assumed and the error you
experienced would occur. I say 'probably' because the priority of different
libraries can be changed.

There are other object names which are shared by both libraries, including
the Database object, so its normally recommended these days that when
declaring a data access object variable the relevant library is specified.

To create a reference to the DAO library select References from the Tools
menu on the VBA menu bar, scroll down to the Microsoft DAO 3.6 Object Library
and check it.

Ken Sheridan
Stafford, England

Ken,

I folded past experience into your suggestion and declared rst As Object.
That done, your idea worked swimmingly. However, I suspect I am still
missing a fine point. I would appreciate your comment on the declaration of
rst per se.

Tom Stevens,
Toronto

Thanks, Ken,

That is most helpful. One related detail which I must get right concerns
the DAO declaration. Presumably, I must import a suitable library for this
to work. (When I tried the DAO.Recordset declaration at present, I get a no
find message.) If I just declare As Recordset, I get the Type mismatch to
which I alluded earlier.

So what must I do to bring in the DAO library? I looked for it in related
postings - but have not spotted it because most responders can take it for
granted that the questioner has managed that detail. I imagine there is an
"Import" or some other such command?

Tom Stevens
Toronto

No need to be embarrassed. I recall asking a very similar question many
years ago in the old CompuServe MS Access forum. John Vinson, who was then a
mere slip of a lad, answered.

Filtering the form will mess up the bookmark, so what you need to do is grab
the value of the current record's primary key to a variable, filter the form,
find the value of the variable in the filtered form's recordset's clone, then
synchronize the bookmarks. I'll assume the key is called MyID and is a
number data type:

Dim strWhere As String
Dim lngMyID As Long
Dim rst As DAO.Recordset

lngMyID = Me.MyID

' Code to construct strWhere

Me.Filter = strWhere
Me.FilterOn = True

Set rst = Me.RecordsetClone
With rst
.FindFirst "MyID = " & lngMyID
If Not .NoMatch Then
Me.Bookmark = rst.Bookmark
End If
End With

Ken Sheridan
Stafford, England

:

I am embarrassed to have to ask because what I wish to do seems so trivial.
Basically, I have a form with an underlying table and am using the latter to
prepare a new record. At a certain point, I want to filter all the records
with certain details in common with the new one. After filtering, I wish to
return to my new record. So it looks as if I want to set a bookmark prior to
filtering and to reset my form to it afterwards. I am attempting this from
the click event handler of a command button. My latest attempt looks as
follows:

Option Compare Database

Private Sub cmdApplyFilter_Click()
On Error GoTo Err_cmdApplyFilter_Click
Dim strWhere As String
Dim rst As Recordset
Set rst = Me.RecordsetClone
rst.Bookmark = Me.Bookmark

Code to construct strWhere

Me.Filter = strWhere
Me.FilterOn = True
Me.Bookmark = rst.Bookmark
Me.Counter.Value = DCount("[Counter]", "[Archive]", strWhere)
Exit_cmdApplyFilter_Click:
Exit Sub

Err_cmdApplyFilter_Click:
MsgBox Err.Description
Resume Exit_cmdApplyFilter_Click
End Sub

With the above, I get an immediate type mismatch when I try to Set rst. If
I declare rst as Object, the set command goes through - but when I try to
apply the bookmark later, I get a message that rst is no longer set! (Can't
think what I did to unset it.) It I try to store the bookmark as a variant,
I get an illegal bookmark message when I try to reapply it.

I am using MSAccess 2002. This looks to be relevant since there are
different styles evident in various historic postings; so it likely affects
how I should be declaring rst.

I am new at databases but have experience in VB.Net programming for other
Windows XP applications. The rules for Access appear to me to be something
like VB6.

 

[tstevens]

Thanks, Ken,

I am glad you mentioned ADODB's being the default. My database is to
interact with a VB.Net program and all my documentation on how to make this
happen is ADO oriented. So perhaps I had best leave well enough alone and
not invoke the DAO library - unless you see a contraindication.

Tom Stevens
Toronto

For some time now ADODB (ActiveX Data Objects) has been the default data
access technology in Access. However, there is no reason why DAO can't be
used, or indeed why both technologies can't be used together in the same
database. There is of course a potential ambiguity if references to both
libraries are in place, which is why I specified the DAO library. Otherwise
the ADODB library would probably have been assumed and the error you
experienced would occur. I say 'probably' because the priority of different
libraries can be changed.

There are other object names which are shared by both libraries, including
the Database object, so its normally recommended these days that when
declaring a data access object variable the relevant library is specified.

To create a reference to the DAO library select References from the Tools
menu on the VBA menu bar, scroll down to the Microsoft DAO 3.6 Object Library
and check it.

Ken Sheridan
Stafford, England

Ken,

I folded past experience into your suggestion and declared rst As Object.
That done, your idea worked swimmingly. However, I suspect I am still
missing a fine point. I would appreciate your comment on the declaration of
rst per se.

Tom Stevens,
Toronto

Thanks, Ken,

That is most helpful. One related detail which I must get right concerns
the DAO declaration. Presumably, I must import a suitable library for this
to work. (When I tried the DAO.Recordset declaration at present, I get a no
find message.) If I just declare As Recordset, I get the Type mismatch to
which I alluded earlier.

So what must I do to bring in the DAO library? I looked for it in related
postings - but have not spotted it because most responders can take it for
granted that the questioner has managed that detail. I imagine there is an
"Import" or some other such command?

Tom Stevens
Toronto

:

No need to be embarrassed. I recall asking a very similar question many
years ago in the old CompuServe MS Access forum. John Vinson, who was then a
mere slip of a lad, answered.

Filtering the form will mess up the bookmark, so what you need to do is grab
the value of the current record's primary key to a variable, filter the form,
find the value of the variable in the filtered form's recordset's clone, then
synchronize the bookmarks. I'll assume the key is called MyID and is a
number data type:

Dim strWhere As String
Dim lngMyID As Long
Dim rst As DAO.Recordset

lngMyID = Me.MyID

' Code to construct strWhere

Me.Filter = strWhere
Me.FilterOn = True

Set rst = Me.RecordsetClone
With rst
.FindFirst "MyID = " & lngMyID
If Not .NoMatch Then
Me.Bookmark = rst.Bookmark
End If
End With

Ken Sheridan
Stafford, England

:

I am embarrassed to have to ask because what I wish to do seems so trivial.
Basically, I have a form with an underlying table and am using the latter to
prepare a new record. At a certain point, I want to filter all the records
with certain details in common with the new one. After filtering, I wish to
return to my new record. So it looks as if I want to set a bookmark prior to
filtering and to reset my form to it afterwards. I am attempting this from
the click event handler of a command button. My latest attempt looks as
follows:

Option Compare Database

Private Sub cmdApplyFilter_Click()
On Error GoTo Err_cmdApplyFilter_Click
Dim strWhere As String
Dim rst As Recordset
Set rst = Me.RecordsetClone
rst.Bookmark = Me.Bookmark

Code to construct strWhere

Me.Filter = strWhere
Me.FilterOn = True
Me.Bookmark = rst.Bookmark
Me.Counter.Value = DCount("[Counter]", "[Archive]", strWhere)
Exit_cmdApplyFilter_Click:
Exit Sub

Err_cmdApplyFilter_Click:
MsgBox Err.Description
Resume Exit_cmdApplyFilter_Click
End Sub

With the above, I get an immediate type mismatch when I try to Set rst. If
I declare rst as Object, the set command goes through - but when I try to
apply the bookmark later, I get a message that rst is no longer set! (Can't
think what I did to unset it.) It I try to store the bookmark as a variant,
I get an illegal bookmark message when I try to reapply it.

I am using MSAccess 2002. This looks to be relevant since there are
different styles evident in various historic postings; so it likely affects
how I should be declaring rst.

I am new at databases but have experience in VB.Net programming for other
Windows XP applications. The rules for Access appear to me to be something
like VB6.

 

[Ken Sheridan]

Tom:

My inclination if I were in your shoes would be to stick with the one
technology. You'd probably have no problems if you did reference the DAO
library, but using both in one application has always struck me as a bit
inelegant.

Ken Sheridan
Stafford, England

Thanks, Ken,

I am glad you mentioned ADODB's being the default. My database is to
interact with a VB.Net program and all my documentation on how to make this
happen is ADO oriented. So perhaps I had best leave well enough alone and
not invoke the DAO library - unless you see a contraindication.

Tom Stevens
Toronto

For some time now ADODB (ActiveX Data Objects) has been the default data
access technology in Access. However, there is no reason why DAO can't be
used, or indeed why both technologies can't be used together in the same
database. There is of course a potential ambiguity if references to both
libraries are in place, which is why I specified the DAO library. Otherwise
the ADODB library would probably have been assumed and the error you
experienced would occur. I say 'probably' because the priority of different
libraries can be changed.

There are other object names which are shared by both libraries, including
the Database object, so its normally recommended these days that when
declaring a data access object variable the relevant library is specified.

To create a reference to the DAO library select References from the Tools
menu on the VBA menu bar, scroll down to the Microsoft DAO 3.6 Object Library
and check it.

Ken Sheridan
Stafford, England

Ken,

I folded past experience into your suggestion and declared rst As Object.
That done, your idea worked swimmingly. However, I suspect I am still
missing a fine point. I would appreciate your comment on the declaration of
rst per se.

Tom Stevens,
Toronto

:

Thanks, Ken,

That is most helpful. One related detail which I must get right concerns
the DAO declaration. Presumably, I must import a suitable library for this
to work. (When I tried the DAO.Recordset declaration at present, I get a no
find message.) If I just declare As Recordset, I get the Type mismatch to
which I alluded earlier.

So what must I do to bring in the DAO library? I looked for it in related
postings - but have not spotted it because most responders can take it for
granted that the questioner has managed that detail. I imagine there is an
"Import" or some other such command?

Tom Stevens
Toronto

:

No need to be embarrassed. I recall asking a very similar question many
years ago in the old CompuServe MS Access forum. John Vinson, who was then a
mere slip of a lad, answered.

Filtering the form will mess up the bookmark, so what you need to do is grab
the value of the current record's primary key to a variable, filter the form,
find the value of the variable in the filtered form's recordset's clone, then
synchronize the bookmarks. I'll assume the key is called MyID and is a
number data type:

Dim strWhere As String
Dim lngMyID As Long
Dim rst As DAO.Recordset

lngMyID = Me.MyID

' Code to construct strWhere

Me.Filter = strWhere
Me.FilterOn = True

Set rst = Me.RecordsetClone
With rst
.FindFirst "MyID = " & lngMyID
If Not .NoMatch Then
Me.Bookmark = rst.Bookmark
End If
End With

Ken Sheridan
Stafford, England

:

I am embarrassed to have to ask because what I wish to do seems so trivial.
Basically, I have a form with an underlying table and am using the latter to
prepare a new record. At a certain point, I want to filter all the records
with certain details in common with the new one. After filtering, I wish to
return to my new record. So it looks as if I want to set a bookmark prior to
filtering and to reset my form to it afterwards. I am attempting this from
the click event handler of a command button. My latest attempt looks as
follows:

Option Compare Database

Private Sub cmdApplyFilter_Click()
On Error GoTo Err_cmdApplyFilter_Click
Dim strWhere As String
Dim rst As Recordset
Set rst = Me.RecordsetClone
rst.Bookmark = Me.Bookmark

Code to construct strWhere

Me.Filter = strWhere
Me.FilterOn = True
Me.Bookmark = rst.Bookmark
Me.Counter.Value = DCount("[Counter]", "[Archive]", strWhere)
Exit_cmdApplyFilter_Click:
Exit Sub

Err_cmdApplyFilter_Click:
MsgBox Err.Description
Resume Exit_cmdApplyFilter_Click
End Sub

With the above, I get an immediate type mismatch when I try to Set rst. If
I declare rst as Object, the set command goes through - but when I try to
apply the bookmark later, I get a message that rst is no longer set! (Can't
think what I did to unset it.) It I try to store the bookmark as a variant,
I get an illegal bookmark message when I try to reapply it.

I am using MSAccess 2002. This looks to be relevant since there are
different styles evident in various historic postings; so it likely affects
how I should be declaring rst.

I am new at databases but have experience in VB.Net programming for other
Windows XP applications. The rules for Access appear to me to be something
like VB6.