Filtering subform and updating main form

[matteo.trombetti]

I had posted a smaller problem just before, but I think it will be nice
to post my main problem as well

There is a master table, myTable, which two queries are based on, myIDs
(which simply returns primary key values for all records in the table)
and myQuery (which returns the whole table with minor modifications).

I have a subform which has its own independent RecordSource set to
myQuery and is not linked to its parent form. This subform returns
filtered records based on controls in the parent form.

The Parent form has its own RecordSource set to myIDs query.

The idea is that when the user selects a record in the subform, this
triggers the OnCurrent procedure, which will look up in the parent
form's .Recordsetclone to find the primary key selected in the subform
and set parent form's bookmark. This will allow to synchronize another
subform in the main form which is linked (master/child fields) to the
parent form.

At the beginning, the subform opens with an already set filter.

All this worked fine until the day when I came across the case where
the first record in the table was (correctly) filtered out: this caused
Access to crash - and that is systematical.
All other cases (set/unset filters, switch from one record to another
and so on) work perfectly when the first record is not filtered out
(but for example others are).

Also, I found out that upon commenting the code line that updates the
bookmark in parent form, Access does not crash - and of course nothing
is synchronized anymore - This is the cursed procedure:

Private Sub mySubForm_OnCurrent
Dim rst As DAO.Recordset
Set rst = Me.Parent.RecordsetClone
With rst
.FindFirst "[myID]=" & Me!myID
' Me.Parent.Bookmark = rst.Bookmark '****************comment
End With
End Sub

Do you have any idea to solve or go around the problem??
Thanks a lot to everybody,

Matteo

 

[matteo.trombetti]

I managed to isolate the exact circumstances (so you can try yourself!)
and I have come to the conclusion that this is just another Access' bug
-I'll have to find some tricky ways to avoid it. That's it.
Any suggestions welcome!

1.Create a plain form that source the primary key from a table and
create one subform. The subform will display info from the same table
(or a query that includes primary key) and upon OnCurrent event, will
set the parent form bookmark to the matching record. Now, say you don't
want the subform to start with the first record but with the second and
use WHERE clause or a filter to cut it out.

Everything works fine, right?

2.Now I just add a second subform, no matter which but not blank (even
with no RecordSource whatsoever and no Child/Master link fields!!) and
for what concerns me Access crashes. If you experience something
different, please let me know!

Have fun,

Matteo

I had posted a smaller problem just before, but I think it will be nice
to post my main problem as well

There is a master table, myTable, which two queries are based on, myIDs
(which simply returns primary key values for all records in the table)
and myQuery (which returns the whole table with minor modifications).

I have a subform which has its own independent RecordSource set to
myQuery and is not linked to its parent form. This subform returns
filtered records based on controls in the parent form.

The Parent form has its own RecordSource set to myIDs query.

The idea is that when the user selects a record in the subform, this
triggers the OnCurrent procedure, which will look up in the parent
form's .Recordsetclone to find the primary key selected in the subform
and set parent form's bookmark. This will allow to synchronize another
subform in the main form which is linked (master/child fields) to the
parent form.

At the beginning, the subform opens with an already set filter.

All this worked fine until the day when I came across the case where
the first record in the table was (correctly) filtered out: this caused
Access to crash - and that is systematical.
All other cases (set/unset filters, switch from one record to another
and so on) work perfectly when the first record is not filtered out
(but for example others are).

Also, I found out that upon commenting the code line that updates the
bookmark in parent form, Access does not crash - and of course nothing
is synchronized anymore - This is the cursed procedure:

Private Sub mySubForm_OnCurrent
Dim rst As DAO.Recordset
Set rst = Me.Parent.RecordsetClone
With rst
.FindFirst "[myID]=" & Me!myID
' Me.Parent.Bookmark = rst.Bookmark '****************comment
End With
End Sub

Do you have any idea to solve or go around the problem??
Thanks a lot to everybody,

Matteo

 

[matteo.trombetti]

I managed to isolate the exact circumstances (so you can try yourself!)
and I have come to the conclusion that this is just another Access' bug
-I'll have to find some tricky ways to avoid it. That's it.
Any suggestions welcome!

1.Create a plain form that source the primary key from a table and
create one subform. The subform will display info from the same table
(or a query that includes primary key) and upon OnCurrent event, will
set the parent form bookmark to the matching record. Now, say you don't
want the subform to start with the first record but with the second and
use WHERE clause or a filter to cut it out.

Everything works fine, right?

2.Now I just add a second subform, no matter which but not blank (even
with no RecordSource whatsoever and no Child/Master link fields!!) and
for what concerns me Access crashes. If you experience something
different, please let me know!

Have fun,

Matteo

I had posted a smaller problem just before, but I think it will be nice
to post my main problem as well

There is a master table, myTable, which two queries are based on, myIDs
(which simply returns primary key values for all records in the table)
and myQuery (which returns the whole table with minor modifications).

I have a subform which has its own independent RecordSource set to
myQuery and is not linked to its parent form. This subform returns
filtered records based on controls in the parent form.

The Parent form has its own RecordSource set to myIDs query.

The idea is that when the user selects a record in the subform, this
triggers the OnCurrent procedure, which will look up in the parent
form's .Recordsetclone to find the primary key selected in the subform
and set parent form's bookmark. This will allow to synchronize another
subform in the main form which is linked (master/child fields) to the
parent form.

At the beginning, the subform opens with an already set filter.

All this worked fine until the day when I came across the case where
the first record in the table was (correctly) filtered out: this caused
Access to crash - and that is systematical.
All other cases (set/unset filters, switch from one record to another
and so on) work perfectly when the first record is not filtered out
(but for example others are).

Also, I found out that upon commenting the code line that updates the
bookmark in parent form, Access does not crash - and of course nothing
is synchronized anymore - This is the cursed procedure:

Private Sub mySubForm_OnCurrent
Dim rst As DAO.Recordset
Set rst = Me.Parent.RecordsetClone
With rst
.FindFirst "[myID]=" & Me!myID
' Me.Parent.Bookmark = rst.Bookmark '****************comment
End With
End Sub

Do you have any idea to solve or go around the problem??
Thanks a lot to everybody,

Matteo