Sorting within a form

Hi,
I’m working on a continuous forms subform that has a simple query as the
record source. Originally the query sorted the records for the form. I want
to change the form so that users can click buttons over fields and change the
sort order while viewing the records in the form.
I’ve read several posts about this, and it’s clear that others have had this
problem and that there are several ways to solve it. I am not VBA literate;
I am trying for now to avoid pasting a bunch of code I don’t understand into
my database objects. I’ve tried using Order By in the form’s data properties
but I can’t get that to work at all, even though I’ve read the help screens
and looked here as well.
Is there a way using macros on buttons and/or simple expressions in the
form’s data properties to solve this problem or do I have to put my project
on hold until I learn VBA coding?

Try this:
table: DataTable
columns: Name text 50
Rank text 50
Serial Number text 50

Fill it with some data.

Create continous form bound to "DataTable" (or a query that references, etc.)

In form's FORM HEADER, create a FRAME named SortBy

Within the frame, put 3 option "radio" buttons - Name, Rank, and Serial Number
They should have property "Option Value" of 1, 2, and 3 respectively.

Put this code in the form:

Option Compare Database
Option Explicit

Private Sub frame_SortBy_Click()
Select Case frame_SortBy
Case 1:
Me.OrderBy = "[Name]"
Case 2:
Me.OrderBy = "[Rank]"
Case 3:
Me.OrderBy = "[SerialNumber]"
End Select
End Sub

Note: to put the code in, right click on the frame (not the label of the
frame, but the frame itself, then select PROPERTIES, then select EVENTS and
then On Click, select EVENT PROCEDURE, and then click the "..." button to get
into VBA editor.

Save the form, open it, and click the various radio buttons.



"Ted M H" wrote:

Hi,
I’m working on a continuous forms subform that has a simple query as the
record source. Originally the query sorted the records for the form. I want
to change the form so that users can click buttons over fields and change the
sort order while viewing the records in the form.
I’ve read several posts about this, and it’s clear that others have had this
problem and that there are several ways to solve it. I am not VBA literate;
I am trying for now to avoid pasting a bunch of code I don’t understand into
my database objects. I’ve tried using Order By in the form’s data properties
but I can’t get that to work at all, even though I’ve read the help screens
and looked here as well.
Is there a way using macros on buttons and/or simple expressions in the
form’s data properties to solve this problem or do I have to put my project
on hold until I learn VBA coding?

On Oct 24, 7:09 pm, Ted M H wrote:
Hi,
I'm working on a continuous forms subform that has a simple query as the
record source. Originally the query sorted the records for the form. I want
to change the form so that users can click buttons over fields and change the
sort order while viewing the records in the form.
I've read several posts about this, and it's clear that others have had this
problem and that there are several ways to solve it. I am not VBA literate;
I am trying for now to avoid pasting a bunch of code I don't understand into
my database objects. I've tried using Order By in the form's data properties
but I can't get that to work at all, even though I've read the help screens
and looked here as well.
Is there a way using macros on buttons and/or simple expressions in the
form's data properties to solve this problem or do I have to put my project
on hold until I learn VBA coding?

Hi Ted,

I had to solve the exact problem you are trying to create and deployed
the solutuion you use

Meaning at first I had my forms record source as the table itself. The
problem was the users had the Sort Ascending/Decending buttons on the
toolbar. I looked it up in MS access and MS support said that every
time a user clicks on a field and uses the sort Ascending/Descending
command that sort order sticks. So each time a user would sort the
records, evry other user would also get it sorted in that fashon. So
to remedy this I created a simple query for the record source and used
it for default sorting. So now when users use the sort ascending/
descending buttons, it will stick for that person for that session,
but resets to the query default each time the user opens the form and
when one user sorts it doesn't affect another user. We use Access 03.

So in short, just put the sort buttons on the toolbar. The the user
clicks in the field and picks sort ascending or descending. Works for
sub tables too, Hope this helps.

Hi and thanks for helping. I tried this approach on my existing form rather
than creating a new table, etc. But it doesn't work. I set up the frame and
the option buttons and they are displayed on the form header, but I can't
activate any of the three buttons. I first let the frame default to option
1, and it showed option 1 activated, but I couldn't unactivate it and I
couldn't activate any other buttons. Any idea what's causing this?

"NKTower" wrote:

Try this:
table: DataTable
columns: Name text 50
Rank text 50
Serial Number text 50

Fill it with some data.

Create continous form bound to "DataTable" (or a query that references, etc.)

In form's FORM HEADER, create a FRAME named SortBy

Within the frame, put 3 option "radio" buttons - Name, Rank, and Serial Number
They should have property "Option Value" of 1, 2, and 3 respectively.

Put this code in the form:

Option Compare Database
Option Explicit

Private Sub frame_SortBy_Click()
Select Case frame_SortBy
Case 1:
Me.OrderBy = "[Name]"
Case 2:
Me.OrderBy = "[Rank]"
Case 3:
Me.OrderBy = "[SerialNumber]"
End Select
End Sub

Note: to put the code in, right click on the frame (not the label of the
frame, but the frame itself, then select PROPERTIES, then select EVENTS and
then On Click, select EVENT PROCEDURE, and then click the "..." button to get
into VBA editor.

Save the form, open it, and click the various radio buttons.



"Ted M H" wrote:

Hi,
I’m working on a continuous forms subform that has a simple query as the
record source. Originally the query sorted the records for the form. I want
to change the form so that users can click buttons over fields and change the
sort order while viewing the records in the form.
I’ve read several posts about this, and it’s clear that others have had this
problem and that there are several ways to solve it. I am not VBA literate;
I am trying for now to avoid pasting a bunch of code I don’t understand into
my database objects. I’ve tried using Order By in the form’s data properties
but I can’t get that to work at all, even though I’ve read the help screens
and looked here as well.
Is there a way using macros on buttons and/or simple expressions in the
form’s data properties to solve this problem or do I have to put my project
on hold until I learn VBA coding?

Hi and thanks for helping. This solution works in that it sorts the records,
but for a number of reasons I want to build the sort function into the form.
Is there a way to copy the sort icons into the form? Probably not I think...

"Richnep" wrote:

On Oct 24, 7:09 pm, Ted M H wrote:
Hi,
I'm working on a continuous forms subform that has a simple query as the
record source. Originally the query sorted the records for the form. I want
to change the form so that users can click buttons over fields and change the
sort order while viewing the records in the form.
I've read several posts about this, and it's clear that others have had this
problem and that there are several ways to solve it. I am not VBA literate;
I am trying for now to avoid pasting a bunch of code I don't understand into
my database objects. I've tried using Order By in the form's data properties
but I can't get that to work at all, even though I've read the help screens
and looked here as well.
Is there a way using macros on buttons and/or simple expressions in the
form's data properties to solve this problem or do I have to put my project
on hold until I learn VBA coding?

Hi Ted,

I had to solve the exact problem you are trying to create and deployed
the solutuion you use

Meaning at first I had my forms record source as the table itself. The
problem was the users had the Sort Ascending/Decending buttons on the
toolbar. I looked it up in MS access and MS support said that every
time a user clicks on a field and uses the sort Ascending/Descending
command that sort order sticks. So each time a user would sort the
records, evry other user would also get it sorted in that fashon. So
to remedy this I created a simple query for the record source and used
it for default sorting. So now when users use the sort ascending/
descending buttons, it will stick for that person for that session,
but resets to the query default each time the user opens the form and
when one user sorts it doesn't affect another user. We use Access 03.

So in short, just put the sort buttons on the toolbar. The the user
clicks in the field and picks sort ascending or descending. Works for
sub tables too, Hope this helps.

Hi again,

I went back and tried to get your simple example to work to see if I could
learn something by doing so. Well I can't get even the simple example to
work. When I click on the radio buttons the number 1, 2 or 3 shows up in the
name field in my form, but no sorting occurs. For example, if I activate the
rank button, the number 2 appears in the first record in the name column. If
I active the serial number button, 3 appears in the first record in the name
column. Any suggestions as to what I'm doing wrong?

Thanks again for helping me with this.

"NKTower" wrote:

Try this:
table: DataTable
columns: Name text 50
Rank text 50
Serial Number text 50

Fill it with some data.

Create continous form bound to "DataTable" (or a query that references, etc.)

In form's FORM HEADER, create a FRAME named SortBy

Within the frame, put 3 option "radio" buttons - Name, Rank, and Serial Number
They should have property "Option Value" of 1, 2, and 3 respectively.

Put this code in the form:

Option Compare Database
Option Explicit

Private Sub frame_SortBy_Click()
Select Case frame_SortBy
Case 1:
Me.OrderBy = "[Name]"
Case 2:
Me.OrderBy = "[Rank]"
Case 3:
Me.OrderBy = "[SerialNumber]"
End Select
End Sub

Note: to put the code in, right click on the frame (not the label of the
frame, but the frame itself, then select PROPERTIES, then select EVENTS and
then On Click, select EVENT PROCEDURE, and then click the "..." button to get
into VBA editor.

Save the form, open it, and click the various radio buttons.



"Ted M H" wrote:

Hi,
I’m working on a continuous forms subform that has a simple query as the
record source. Originally the query sorted the records for the form. I want
to change the form so that users can click buttons over fields and change the
sort order while viewing the records in the form.
I’ve read several posts about this, and it’s clear that others have had this
problem and that there are several ways to solve it. I am not VBA literate;
I am trying for now to avoid pasting a bunch of code I don’t understand into
my database objects. I’ve tried using Order By in the form’s data properties
but I can’t get that to work at all, even though I’ve read the help screens
and looked here as well.
Is there a way using macros on buttons and/or simple expressions in the
form’s data properties to solve this problem or do I have to put my project
on hold until I learn VBA coding?

=?Utf-8?B?VGVkIE0gSA==?= wrote in
:

Hi,
I’m working on a continuous forms subform that has a simple
query as the record source. Originally the query sorted the
records for the form. I want to change the form so that users can
click buttons over fields and change the sort order while viewing
the records in the form. I’ve read several posts about this,
and it’s clear that others have had this problem and that there
are several ways to solve it. I am not VBA literate; I am trying
for now to avoid pasting a bunch of code I don’t understand into
my database objects. I’ve tried using Order By in the form’s
data properties but I can’t get that to work at all, even though
I’ve read the help screens and looked here as well.
Is there a way using macros on buttons and/or simple expressions
in the form’s data properties to solve this problem or do I have
to put my project on hold until I learn VBA coding?

The code is really simple, so it's a good start to lear to code.
in each button's on Click property just put the following two lines

me.orderby = "fieldname"
me.orderbyon = true

change the fieldname above to the name of the field you want that
button to sort on. If the field name has spaces or punctuation,
enclose in [] so me.orderby = "[field name]"

this will not sort descending, but it's a start.


--
Bob Quintal

PA is y I've altered my email address.

--
Posted via a free Usenet account from http://www.teranews.com

On Oct 25, 4:23 pm, Ted M H wrote:
Hi again,

I went back and tried to get your simple example to work to see if I could
learn something by doing so. Well I can't get even the simple example to
work. When I click on the radio buttons the number 1, 2 or 3 shows up in the
name field in my form, but no sorting occurs. For example, if I activate the
rank button, the number 2 appears in the first record in the name column. If
I active the serial number button, 3 appears in the first record in the name
column. Any suggestions as to what I'm doing wrong?

Thanks again for helping me with this.



"NKTower" wrote:
Try this:
table: DataTable
columns: Name text 50
Rank text 50
Serial Number text 50

Fill it with some data.

Create continous form bound to "DataTable" (or a query that references, etc.)

In form's FORM HEADER, create a FRAME named SortBy

Within the frame, put 3 option "radio" buttons - Name, Rank, and Serial Number
They should have property "Option Value" of 1, 2, and 3 respectively.

Put this code in the form:

Option Compare Database
Option Explicit

Private Sub frame_SortBy_Click()
Select Case frame_SortBy
Case 1:
Me.OrderBy = "[Name]"
Case 2:
Me.OrderBy = "[Rank]"
Case 3:
Me.OrderBy = "[SerialNumber]"
End Select
End Sub

Note: to put the code in, right click on the frame (not the label of the
frame, but the frame itself, then select PROPERTIES, then select EVENTS and
then On Click, select EVENT PROCEDURE, and then click the "..." button to get
into VBA editor.

Save the form, open it, and click the various radio buttons.

"Ted M H" wrote:

Hi,
I'm working on a continuous forms subform that has a simple query as the
record source. Originally the query sorted the records for the form. I want
to change the form so that users can click buttons over fields and change the
sort order while viewing the records in the form.
I've read several posts about this, and it's clear that others have had this
problem and that there are several ways to solve it. I am not VBA literate;
I am trying for now to avoid pasting a bunch of code I don't understand into
my database objects. I've tried using Order By in the form's data properties
but I can't get that to work at all, even though I've read the help screens
and looked here as well.
Is there a way using macros on buttons and/or simple expressions in the
form's data properties to solve this problem or do I have to put my project
on hold until I learn VBA coding?- Hide quoted text -

- Show quoted text -

Ted,

They are not radio buttons and they are not on the form, they are
toolbar buttons. the AZ with Up or down arrows.

On Oct 25, 4:23 pm, Ted M H wrote:
Hi again,

I went back and tried to get your simple example to work to see if I could
learn something by doing so. Well I can't get even the simple example to
work. When I click on the radio buttons the number 1, 2 or 3 shows up in the
name field in my form, but no sorting occurs. For example, if I activate the
rank button, the number 2 appears in the first record in the name column. If
I active the serial number button, 3 appears in the first record in the name
column. Any suggestions as to what I'm doing wrong?

Thanks again for helping me with this.



"NKTower" wrote:
Try this:
table: DataTable
columns: Name text 50
Rank text 50
Serial Number text 50

Fill it with some data.

Create continous form bound to "DataTable" (or a query that references, etc.)

In form's FORM HEADER, create a FRAME named SortBy

Within the frame, put 3 option "radio" buttons - Name, Rank, and Serial Number
They should have property "Option Value" of 1, 2, and 3 respectively.

Put this code in the form:

Option Compare Database
Option Explicit

Private Sub frame_SortBy_Click()
Select Case frame_SortBy
Case 1:
Me.OrderBy = "[Name]"
Case 2:
Me.OrderBy = "[Rank]"
Case 3:
Me.OrderBy = "[SerialNumber]"
End Select
End Sub

Note: to put the code in, right click on the frame (not the label of the
frame, but the frame itself, then select PROPERTIES, then select EVENTS and
then On Click, select EVENT PROCEDURE, and then click the "..." button to get
into VBA editor.

Save the form, open it, and click the various radio buttons.

"Ted M H" wrote:

Hi,
I'm working on a continuous forms subform that has a simple query as the
record source. Originally the query sorted the records for the form. I want
to change the form so that users can click buttons over fields and change the
sort order while viewing the records in the form.
I've read several posts about this, and it's clear that others have had this
problem and that there are several ways to solve it. I am not VBA literate;
I am trying for now to avoid pasting a bunch of code I don't understand into
my database objects. I've tried using Order By in the form's data properties
but I can't get that to work at all, even though I've read the help screens
and looked here as well.
Is there a way using macros on buttons and/or simple expressions in the
form's data properties to solve this problem or do I have to put my project
on hold until I learn VBA coding?- Hide quoted text -

- Show quoted text -

Yeah I'm not sure how to do this on the form. The code must be out
there somewhere. Sorry.

Hi Bob, and thanks for helping me with this. It just shouldn't be so hard!
I tried your solution but couldn't get it to work. The on click property on
the property sheet for the button will only let me put things on one line, so
I can't follow your instruction (although I tried). I also tried putting in
the two lines using the Epression Builder and then the Code builder, but
neither of these worked.

The expression builder attempt put both statements in the on click property,
but then I get a msg "The expression you entered contains invalid syntax You
may have entered an operand without an operator." Expression builder also
inserts an equals sign at the front of the code.

It feels like I'm getting closer here... any addtional ideas?

Many thanks.

"Bob Quintal" wrote:

=?Utf-8?B?VGVkIE0gSA==?= wrote in
:

Hi,
I’m working on a continuous forms subform that has a simple
query as the record source. Originally the query sorted the
records for the form. I want to change the form so that users can
click buttons over fields and change the sort order while viewing
the records in the form. I’ve read several posts about this,
and it’s clear that others have had this problem and that there
are several ways to solve it. I am not VBA literate; I am trying
for now to avoid pasting a bunch of code I don’t understand into
my database objects. I’ve tried using Order By in the form’s
data properties but I can’t get that to work at all, even though
I’ve read the help screens and looked here as well.
Is there a way using macros on buttons and/or simple expressions
in the form’s data properties to solve this problem or do I have
to put my project on hold until I learn VBA coding?

The code is really simple, so it's a good start to lear to code.
in each button's on Click property just put the following two lines

me.orderby = "fieldname"
me.orderbyon = true

change the fieldname above to the name of the field you want that
button to sort on. If the field name has spaces or punctuation,
enclose in [] so me.orderby = "[field name]"

this will not sort descending, but it's a start.


--
Bob Quintal

PA is y I've altered my email address.

--
Posted via a free Usenet account from http://www.teranews.com