Access2000: Subform record order

[Arvi Laanemets]

Hi

I have a form fmDevices with a continous subform fmTransactions on it.
Form fmDevices is based on table tblDevices: DeviceID, RegDate, DevGroup,
....
Form fmTransactions is based on table tblTransactions: TransactID,
TransactDate, DeviceID, Account, TabN.

TblDevices has 2 index fields: DeviceID (unique master key) and DevGroup
(not unique)
TblTransactions fields are all indexed except TransactDate, TransactID is
only field having unique index as a master key.
All non-unique indexes in both tables are used to set relationships to other
tables (DeviceID is used to set one-to-many relation between tblDevices and
tblTransactions).

My problem is, that when user selects a device in form fmDevices,
transactions for this device in subform fmTransactions are not always
ordered by transaction date (by TransactID really at moment, but user
doesn't see it, and practically there is little difference). In
fmTransaction properties OrderBy=TransactDate, but this doesn't have any
effect at all!

How can I force the subform always to be ordered by TransactDate?


Thanks in advance!

 

[JK]

Avri,

If you are using the Form property sheet The syntax is [TransacDate]

Regards/JK

 

[Arvi Laanemets]

Hi

Avri,

If you are using the Form property sheet

Yes, I'm!

The syntax is [TransacDate]

Changed it, but I'm sorry - no difference there!

Mostly the order is OK, but I suspect it's so accidentally. For some devices
the order is messed up - and without any visible pattern. Currently, only
device users are changed through transactions (TransactID is an autonumber,
TabN is user ID and is a text field), but tranactions in subform aren't
ordered by TabN either! An example:

TransactID TaransactDate, DeviceID, ..., TabN
87 01.11.2005 A20050525004 1010
825 22.09.2006 A20050525004 Tark0000
681 12.04.2006 A20050525004 Tark0000
686 18.04.2006 A20050525004 606

This is how transactions records are displayed for one of devices. In
tblTransactions all records are at moment ordered by TransactID. All other
fields in tblTransactions are currently same for all records with same
DeviceID.

 

[JK]

Avri,

We are talking about the SubForm "fmTransactions" which is a subform of the
form fmDevices.
You can change the order on the subform regardless of the order in the
table.

Check that the spelling of the Field TransactDate is correct (I misspelled
it before) and enclose it with square brackets in the Order By Property
If that does not work (it should), *remove* any OrderBy in fmTransactions
and do one of the two

1. Create a query with *all* the fields that are on fmTransactions, sort
the query on [TransactDate] and in the property sheet of the form change the
Record Source to the name you gave the query

OR

2 .In fmTransactions create On Open Event Procedure ( or add to it, if you
have one):

Private Sub Form_OnOpen()

Me.OrderBy="[TransactDate]"
me.OrderByOn=True

End Sub


Regards/JK

Hi

Avri,

If you are using the Form property sheet

Yes, I'm!

The syntax is [TransacDate]

Changed it, but I'm sorry - no difference there!

Mostly the order is OK, but I suspect it's so accidentally. For some
devices the order is messed up - and without any visible pattern.
Currently, only device users are changed through transactions (TransactID
is an autonumber, TabN is user ID and is a text field), but tranactions in
subform aren't ordered by TabN either! An example:

TransactID TaransactDate, DeviceID, ..., TabN
87 01.11.2005 A20050525004 1010
825 22.09.2006 A20050525004 Tark0000
681 12.04.2006 A20050525004 Tark0000
686 18.04.2006 A20050525004 606

This is how transactions records are displayed for one of devices. In
tblTransactions all records are at moment ordered by TransactID. All other
fields in tblTransactions are currently same for all records with same
DeviceID.

 

[Arvi Laanemets]

Hi

Thanks for your efforts!

Avri,

We are talking about the SubForm "fmTransactions" which is a subform of
the form fmDevices.

Yes!
(Really the case is more complex.
I have an unbound multi-tab form fmMain.
On one page of fmMain is a subform sfDeviceGroups with a form fmDeviceGroups
as source. There is a single combobox control on fmDeviceGroups - the user
can select a device group there. All navigation controls for fmDeviceGroups
are disabled.
On form fmDeviceGroups is also a subform sfDevices with a form fmDevices as
source. Only devices from group selected in parent form are available. Any
newly inserted record in tblDevices inherits device group from parent form.
On form fmDevices is a subform sfTransactions with a continous form
fmTransactions as source.)

You can change the order on the subform regardless of the order in the
table.

I know! But it didn't work in this case somehow?

Check that the spelling of the Field TransactDate is correct (I misspelled
it before) and enclose it with square brackets in the Order By Property
If that does not work (it should), *remove* any OrderBy in fmTransactions
and do one of the two

The spelling was correct, and the order field name was enclosed in square
brackets.

1. Create a query with *all* the fields that are on fmTransactions, sort
the query on [TransactDate] and in the property sheet of the form change
the Record Source to the name you gave the query

Can't use this - user must be able to add/delete/edit records in subform.

OR

2 .In fmTransactions create On Open Event Procedure ( or add to it, if you
have one):

Private Sub Form_OnOpen()

Me.OrderBy="[TransactDate]"
me.OrderByOn=True

Did it. It was all the same!?
But anyway you did give me clues enough to find a solution. When debugging,
I noticed, that OrderByOn was set to True on subforms Open event, but
changed to False immediately when control was returned to parent form. So I
did change subform's order from parent form Open event instead - and it
worked!

Private Sub Form_OnOpen()
....
Forms!fmMain!sfDeviceGroups!sfDevices!sfTransactions.Form.OrderBy =
"[TransactDate]"
Forms!fmMain!sfDeviceGroups!sfDevices!sfTransactions.Form.OrderByOn =
True
End Sub

But it remains a mystery for me, why was there a need to set the order by
force anyway, and what a order did Access apply to subform on his own? And
another oddity was, that when I created even a dummy Open event for subform,
some calculated controls on parent form, which did use an UDF, got
nonfunctional.

 

[JK]

Glad to be of help,
If nothing else changes the order the form order is taken from the the table
order
JK

Hi

Thanks for your efforts!

Avri,

We are talking about the SubForm "fmTransactions" which is a subform of
the form fmDevices.

Yes!
(Really the case is more complex.
I have an unbound multi-tab form fmMain.
On one page of fmMain is a subform sfDeviceGroups with a form
fmDeviceGroups as source. There is a single combobox control on
fmDeviceGroups - the user can select a device group there. All navigation
controls for fmDeviceGroups are disabled.
On form fmDeviceGroups is also a subform sfDevices with a form fmDevices
as source. Only devices from group selected in parent form are available.
Any newly inserted record in tblDevices inherits device group from parent
form.
On form fmDevices is a subform sfTransactions with a continous form
fmTransactions as source.)

You can change the order on the subform regardless of the order in the
table.

I know! But it didn't work in this case somehow?

Check that the spelling of the Field TransactDate is correct (I
misspelled it before) and enclose it with square brackets in the Order By
Property
If that does not work (it should), *remove* any OrderBy in fmTransactions
and do one of the two

The spelling was correct, and the order field name was enclosed in square
brackets.

1. Create a query with *all* the fields that are on fmTransactions, sort
the query on [TransactDate] and in the property sheet of the form change
the Record Source to the name you gave the query

Can't use this - user must be able to add/delete/edit records in subform.

OR

2 .In fmTransactions create On Open Event Procedure ( or add to it, if
you have one):

Private Sub Form_OnOpen()

Me.OrderBy="[TransactDate]"
me.OrderByOn=True

Did it. It was all the same!?
But anyway you did give me clues enough to find a solution. When
debugging, I noticed, that OrderByOn was set to True on subforms Open
event, but changed to False immediately when control was returned to
parent form. So I did change subform's order from parent form Open event
instead - and it worked!

Private Sub Form_OnOpen()
....
Forms!fmMain!sfDeviceGroups!sfDevices!sfTransactions.Form.OrderBy =
"[TransactDate]"
Forms!fmMain!sfDeviceGroups!sfDevices!sfTransactions.Form.OrderByOn =
True
End Sub

But it remains a mystery for me, why was there a need to set the order by
force anyway, and what a order did Access apply to subform on his own? And
another oddity was, that when I created even a dummy Open event for
subform, some calculated controls on parent form, which did use an UDF,
got nonfunctional.

 

[Arvi Laanemets]

Hi

Glad to be of help,
If nothing else changes the order the form order is taken from the the
table order

When opened directly, the table was ordered by TransactID, i.e naturally in
the order the records were inserted. But in subform some records didn't
follow this order (or order by any other field in this table) -
approcimately 1% of them. ???!!

 

[JK]

It certainly does, you must have something else that intervene (interfere?)
in the order.

Regards