Setting form bookmark - but form is already filtered! Can anybody help?

[Raymond Luxury-Yacht]

Can anyone help?

I have a form bound to a table. When a drop down list on the form is
changed, I want to change the current record the form displays. I
tried using the 'usual' code to do this:


Private Sub cmbCourseAlsoRunning_AfterUpdate()

Dim rs As DAO.Recordset
Set rs = Me.RecordsetClone

rs.FindFirst "intCourseRefno = " & Me.cmbCourseAlsoRunning

If Not rs.NoMatch Then
Me.Bookmark = rs.Bookmark
End If

End Sub


However, this of course doesn't work because the form is opened with a
WHERECONDITION specified, meaning tha a filter is applied, and as such
the recordsetclone contains only the ONE record currently displayed by
the form, rather than the whole table it is based on!

So I tried changing the 'Set rs' line to this...


Set rs = CurrentDb.OpenRecordset(Me.RecordSource, dbOpenDynaset)


Unfortunately, I now get a 'not a valid bookmark' error, and have
since discovered that the bookmark can only be set using the
recordsetclone! But the recordsetclone is only one record in length!
Aaaarghh!

I would be so, so grateful for any advice anyone can offer with this
one...

 

[missinglinq via AccessMonster.com]

Why are you opening the form with the WHERECONDITION specified?

 

[Marshall Barton]

I have a form bound to a table. When a drop down list on the form is
changed, I want to change the current record the form displays. I
tried using the 'usual' code to do this:


Private Sub cmbCourseAlsoRunning_AfterUpdate()

Dim rs As DAO.Recordset
Set rs = Me.RecordsetClone

rs.FindFirst "intCourseRefno = " & Me.cmbCourseAlsoRunning

If Not rs.NoMatch Then
Me.Bookmark = rs.Bookmark
End If

End Sub


However, this of course doesn't work because the form is opened with a
WHERECONDITION specified, meaning tha a filter is applied, and as such
the recordsetclone contains only the ONE record currently displayed by
the form, rather than the whole table it is based on!

So I tried changing the 'Set rs' line to this...


Set rs = CurrentDb.OpenRecordset(Me.RecordSource, dbOpenDynaset)


Unfortunately, I now get a 'not a valid bookmark' error, and have
since discovered that the bookmark can only be set using the
recordsetclone! But the recordsetclone is only one record in length!
Aaaarghh!

It sounds like you are trying to apply a navigation
technique to a filter problem.

If you want to change the filter, then try this:

Me.Filter = "intCourseRefno = " & Me.cmbCourseAlsoRunning
Me.FilterOn = True

If that's not what you want, please provide more info about
what you want to accomplish (not how you are trying to do
it).

 

[Raymond Luxury-Yacht]

Thanks guys, I see what you mean, I need to open the form then apply
the filter maybe using openargs? Will give that a try, thanks for your
help.

I have a form bound to a table. When a drop down list on the form is
changed, I want to change the current record the form displays. I
tried using the 'usual' code to do this:

Private Sub cmbCourseAlsoRunning_AfterUpdate()

Dim rs As DAO.Recordset
Set rs = Me.RecordsetClone

rs.FindFirst "intCourseRefno = " & Me.cmbCourseAlsoRunning

If Not rs.NoMatch Then
Me.Bookmark = rs.Bookmark
End If

End Sub

However, this of course doesn't work because the form is opened with a
WHERECONDITION specified, meaning tha a filter is applied, and as such
the recordsetclone contains only the ONE record currently displayed by
the form, rather than the whole table it is based on!

So I tried changing the 'Set rs' line to this...

Set rs = CurrentDb.OpenRecordset(Me.RecordSource, dbOpenDynaset)

Unfortunately, I now get a 'not a valid bookmark' error, and have
since discovered that the bookmark can only be set using the
recordsetclone! But the recordsetclone is only one record in length!
Aaaarghh!

It sounds like you are trying to apply a navigation
technique to a filter problem.

If you want to change the filter, then try this:

Me.Filter = "intCourseRefno = " & Me.cmbCourseAlsoRunning
Me.FilterOn = True

If that's not what you want, please provide more info about
what you want to accomplish (not how you are trying to do
it).

--
Marsh
MVP [MS Access]- Hide quoted text -

- Show quoted text -

 

[missinglinq via AccessMonster.com]

Exactly!

Thanks guys, I see what you mean, I need to open the form then apply
the filter maybe using openargs? Will give that a try, thanks for your
help.
[quoted text clipped - 44 lines]

- Show quoted text -

 

[Marshall Barton]

If you also want to open the form filtered to some specified
record(s?), then you can probably skip using OpenArgs and
use WhereCondition instead.

OTOH, since your form already has a filtering mechanism (as
soon as you get it working), you might want to consider
opening the form with no records (using a WhereCondition of
False).