Subform Sadness

[chrisM]

Hi, Hope someone can help me.

I have the following setup in an Access database
A master form 'Customers'
in 'Single Form' view
that has a RecordSource of 'myCustomerTable'
CustomerCode
CustomerName
A subform 'Items'
in 'Datasheet' view
that has a RecordSource of 'myItems'
ItemCode
Qty
CustomerCode

There are no automatic 'Links' set up between the forms, but the 'On
Current' event of the 'Items' form contains the following code:
Form_Customers.Filter = "CustomerCode = " & Me.CustomerCode
Me.FilterOn = True

This works, and causes the Customers form to keep in sync with the
currently selected record in the Items grid. (And yes I know this is a
bit back to front, but please trust me that there are good reasons for
this...)

Given the above, my problem is this. All works fine, unless I try to
change the order of the items form (either by right clicking on the
top of the columns, or by using the OrderBy property of the form) as
soon as I apply the filter on the customers table, I can no longer
move the cursor down the Items form, it is fixed at the top. Also the
order of the Items table reverts to what it was (OrderByOn on the
Items form gets reset to false by the FilterOn on the Customer form).
Can anyone tell me why this happens, and how I can stop it?

This all sounds really complicated, but is actually quite straight
forwards if you try and reproduce it!

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

The correct way to link a subform to the main form is by the Child and
Master Link properties on the subform. IOW, your subform Items link
properties should be like this:

Link Child Fields: CustomerCode
Link Master Fields: CustomerCode

MGFoster:::mgf
Oakland, CA (USA)
-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBP8eYpIechKqOuFEgEQJzNgCfSyxEmNfDPhJjqjmLKSJuCW NyMtkAoOld
5Ug1Ihha7E1K5VfXqcOxbilx
=t/Z8
-----END PGP SIGNATURE-----

chrisM wrote:

Hi, Hope someone can help me.

I have the following setup in an Access database
A master form 'Customers'
in 'Single Form' view
that has a RecordSource of 'myCustomerTable'
CustomerCode
CustomerName
A subform 'Items'
in 'Datasheet' view
that has a RecordSource of 'myItems'
ItemCode
Qty
CustomerCode

There are no automatic 'Links' set up between the forms, but the 'On
Current' event of the 'Items' form contains the following code:
Form_Customers.Filter = "CustomerCode = " & Me.CustomerCode
Me.FilterOn = True

This works, and causes the Customers form to keep in sync with the
currently selected record in the Items grid. (And yes I know this is a
bit back to front, but please trust me that there are good reasons for
this...)

Given the above, my problem is this. All works fine, unless I try to
change the order of the items form (either by right clicking on the
top of the columns, or by using the OrderBy property of the form) as
soon as I apply the filter on the customers table, I can no longer
move the cursor down the Items form, it is fixed at the top. Also the
order of the Items table reverts to what it was (OrderByOn on the
Items form gets reset to false by the FilterOn on the Customer form).
Can anyone tell me why this happens, and how I can stop it?

This all sounds really complicated, but is actually quite straight
forwards if you try and reproduce it!

[chrisM]

Hi,

Yea, I know that the usual way of linking a subform is to use the
Child/Master link properties, but as I said in my post, there are
reasons for having to do it the way we have. The actual form is pretty
complicated, there are 4 subforms that are all interlinked, and the
Child/Master link is simply not sufficient for our needs in this case.
:-(

Having said that, we have found a solution. Rather than the OnCurrent
event of the subform controlling a Filter on the Customer form, we are
using the following in the subforms OnCurrent:

Form_Cust.RecordSource = "select * from cust where custid = " &
Me.CustID

This is doing the same thing as the filter, but has none of the
strange side-effects.

Regards,

ChrisM

MGFoster <[e-mail]> wrote in message news:<wM*****************@newsread2.news.pas.earth link.net>...

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

The correct way to link a subform to the main form is by the Child and
Master Link properties on the subform. IOW, your subform Items link
properties should be like this:

Link Child Fields: CustomerCode
Link Master Fields: CustomerCode

MGFoster:::mgf
Oakland, CA (USA)
-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBP8eYpIechKqOuFEgEQJzNgCfSyxEmNfDPhJjqjmLKSJuCW NyMtkAoOld
5Ug1Ihha7E1K5VfXqcOxbilx
=t/Z8
-----END PGP SIGNATURE-----

chrisM wrote:

Hi, Hope someone can help me.

I have the following setup in an Access database
A master form 'Customers'
in 'Single Form' view
that has a RecordSource of 'myCustomerTable'
CustomerCode
CustomerName
A subform 'Items'
in 'Datasheet' view
that has a RecordSource of 'myItems'
ItemCode
Qty
CustomerCode

There are no automatic 'Links' set up between the forms, but the 'On
Current' event of the 'Items' form contains the following code:
Form_Customers.Filter = "CustomerCode = " & Me.CustomerCode
Me.FilterOn = True

This works, and causes the Customers form to keep in sync with the
currently selected record in the Items grid. (And yes I know this is a
bit back to front, but please trust me that there are good reasons for
this...)

Given the above, my problem is this. All works fine, unless I try to
change the order of the items form (either by right clicking on the
top of the columns, or by using the OrderBy property of the form) as
soon as I apply the filter on the customers table, I can no longer
move the cursor down the Items form, it is fixed at the top. Also the
order of the Items table reverts to what it was (OrderByOn on the
Items form gets reset to false by the FilterOn on the Customer form).
Can anyone tell me why this happens, and how I can stop it?

This all sounds really complicated, but is actually quite straight
forwards if you try and reproduce it!