Sorting records within a subform based on two control fields

Hi all team members,
I have an issue with sorting records within a subform based on two control
fields. I have been trying to assign a sorting of records to appear in an
ascending order when a new record is added or changed in the table.I have
created the bound control called [ProceduralNo] which is not an autonumber
(Long integer). In the Table the data will be scattered as new records are
added arbitrarily with time. I want those records to be sorted in the Subform
in ascending order to read for example according to the following two fields:

ITP_Master_ID ProceduralNo
----------------- ---------------
1 1
1 2
1 3
1 4
2 1
2 2
2 3
: :
: :

I wrote the following SQL code in a Public Function within the class module

Private Function SortProceduralSteps()

Dim rst As Recordset
Dim db As Database
Set db = CurrentDb()

' Open a Recordset from an SQL statement that specifies a sort order.
Set rst = CurrentDb.OpenRecordset("SELECT * FROM
Tbl_QA_ITP_MasterList_Child ORDER BY ITP_Master_ID, ProceduralNo",
dbOpenDynaset)
rst.Close

End Function

I then call this function at the end of the Save Button procedure. I tested
this procedure by the debug test and everything is running without a warning
about any errors. However, the records are not being sorted. I even wrote a
Me.requery at the end of the save procedure but still no luck.

Can someone help me please to know where the problem lies within the coded
procedure? By the way, I was successful when I applied a different approach
which is to establish an SQL query built within the subform data Record
source and setting the desired sorting orders. However, I find the SQL code
valuable at other times and would greatly appreciate anyone's help to sort my
problem. Thanks.

Zikar

On Wed, 28 Jan 2009 20:55:01 -0800, Zikar
wrote:

Hi all team members,
I have an issue with sorting records within a subform based on two control
fields. I have been trying to assign a sorting of records to appear in an
ascending order when a new record is added or changed in the table.I have
created the bound control called [ProceduralNo] which is not an autonumber
(Long integer). In the Table the data will be scattered as new records are
added arbitrarily with time. I want those records to be sorted in the Subform
in ascending order to read for example according to the following two fields:

ITP_Master_ID ProceduralNo
----------------- ---------------
1 1
1 2
1 3
1 4
2 1
2 2
2 3
: :
: :

I wrote the following SQL code in a Public Function within the class module

Private Function SortProceduralSteps()

Dim rst As Recordset
Dim db As Database
Set db = CurrentDb()

' Open a Recordset from an SQL statement that specifies a sort order.
Set rst = CurrentDb.OpenRecordset("SELECT * FROM
Tbl_QA_ITP_MasterList_Child ORDER BY ITP_Master_ID, ProceduralNo",
dbOpenDynaset)
rst.Close

End Function

I then call this function at the end of the Save Button procedure. I tested
this procedure by the debug test and everything is running without a warning
about any errors. However, the records are not being sorted. I even wrote a
Me.requery at the end of the save procedure but still no luck.

Well, your code does two things: it creates a sorted recordset in memory; then
it closes it. It does nothing whatsoever with the form.
\
Can someone help me please to know where the problem lies within the coded
procedure? By the way, I was successful when I applied a different approach
which is to establish an SQL query built within the subform data Record
source and setting the desired sorting orders. However, I find the SQL code
valuable at other times and would greatly appreciate anyone's help to sort my
problem. Thanks.

You could set the subform's Recordsource property to the same sql string; or
you could set the subform's OrderBy property to

ITP_MasterID;ProceduralNo

and set its OrderByOn property to True.

There are all sorts of neat things you can do with recordsets... but you're
not doing any of them above g

--

John W. Vinson [MVP]

Thanks John for your advice of where the problem lies. I fixed it accordingly.
Zikar

"John W. Vinson" wrote:

On Wed, 28 Jan 2009 20:55:01 -0800, Zikar
wrote:

Hi all team members,
I have an issue with sorting records within a subform based on two control
fields. I have been trying to assign a sorting of records to appear in an
ascending order when a new record is added or changed in the table.I have
created the bound control called [ProceduralNo] which is not an autonumber
(Long integer). In the Table the data will be scattered as new records are
added arbitrarily with time. I want those records to be sorted in the Subform
in ascending order to read for example according to the following two fields:

ITP_Master_ID ProceduralNo
----------------- ---------------
1 1
1 2
1 3
1 4
2 1
2 2
2 3
: :
: :

I wrote the following SQL code in a Public Function within the class module

Private Function SortProceduralSteps()

Dim rst As Recordset
Dim db As Database
Set db = CurrentDb()

' Open a Recordset from an SQL statement that specifies a sort order.
Set rst = CurrentDb.OpenRecordset("SELECT * FROM
Tbl_QA_ITP_MasterList_Child ORDER BY ITP_Master_ID, ProceduralNo",
dbOpenDynaset)
rst.Close

End Function

I then call this function at the end of the Save Button procedure. I tested
this procedure by the debug test and everything is running without a warning
about any errors. However, the records are not being sorted. I even wrote a
Me.requery at the end of the save procedure but still no luck.

Well, your code does two things: it creates a sorted recordset in memory; then
it closes it. It does nothing whatsoever with the form.
\
Can someone help me please to know where the problem lies within the coded
procedure? By the way, I was successful when I applied a different approach
which is to establish an SQL query built within the subform data Record
source and setting the desired sorting orders. However, I find the SQL code
valuable at other times and would greatly appreciate anyone's help to sort my
problem. Thanks.

You could set the subform's Recordsource property to the same sql string; or
you could set the subform's OrderBy property to

ITP_MasterID;ProceduralNo

and set its OrderByOn property to True.

There are all sorts of neat things you can do with recordsets... but you're
not doing any of them above g

--

John W. Vinson [MVP]