Using a Label to Sort

  • Thread starter Thread starter channell
  • Start date Start date
C

channell

I have the following code for my label to sort:

Private Sub txtLabel124_Click()
If Me.OrderBy = "[LastNameFirstName]" Then
Me.OrderBy = "[LastNameFirstName] DESC"
Else
Me.OrderBy = "[LastNameFirstName]"
End If
End Sub

The code works just fine, but here is my problem:

The Form which contains this code is based on a Query which requires certain
criteria (Parameter Query). When I click the label to sort the information
present, the Parameter Box reappears asking me to put in the date. When I
do, it sorts it like it should. I need to know if there is a way to bypass
re-entering the same data (Date) in the parameter box. Thank you very much
in advance!
 
Parameters are really not a very good interface. You have just given one
example of where they are too frustrating.

A better approach might be to add an unbound text box to your form (e.g. in
the Form Header), and use its AfterUpdate event procedure to apply a filter
to the form. This will avoid the parameter re-request problem.

You may also need to set:
Me.OrderByOn = True
 
Allen, I realized this just after I posted this. I then posted another
question asking how I would go about putting an unbound text box on the form
with an "OK" button to press after entering the date. Could you possibly
help me out? Thanks a lot!

Allen Browne said:
Parameters are really not a very good interface. You have just given one
example of where they are too frustrating.

A better approach might be to add an unbound text box to your form (e.g. in
the Form Header), and use its AfterUpdate event procedure to apply a filter
to the form. This will avoid the parameter re-request problem.

You may also need to set:
Me.OrderByOn = True

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

channell said:
I have the following code for my label to sort:

Private Sub txtLabel124_Click()
If Me.OrderBy = "[LastNameFirstName]" Then
Me.OrderBy = "[LastNameFirstName] DESC"
Else
Me.OrderBy = "[LastNameFirstName]"
End If
End Sub

The code works just fine, but here is my problem:

The Form which contains this code is based on a Query which requires
certain
criteria (Parameter Query). When I click the label to sort the
information
present, the Parameter Box reappears asking me to put in the date. When I
do, it sorts it like it should. I need to know if there is a way to
bypass
re-entering the same data (Date) in the parameter box. Thank you very
much
in advance!
Click to expand...
Click to expand...
 
Allen, I realized this just after I posted my question. I then posted
another question asking how to put an unbound textbox and an "OK" button next
to it. That way, when I put in the date in the Unbound Textbox and then
Press "OK", it will return the desired results. Could you help me with this?
Thank you so much!

Allen Browne said:
Parameters are really not a very good interface. You have just given one
example of where they are too frustrating.

A better approach might be to add an unbound text box to your form (e.g. in
the Form Header), and use its AfterUpdate event procedure to apply a filter
to the form. This will avoid the parameter re-request problem.

You may also need to set:
Me.OrderByOn = True

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

channell said:
I have the following code for my label to sort:

Private Sub txtLabel124_Click()
If Me.OrderBy = "[LastNameFirstName]" Then
Me.OrderBy = "[LastNameFirstName] DESC"
Else
Me.OrderBy = "[LastNameFirstName]"
End If
End Sub

The code works just fine, but here is my problem:

The Form which contains this code is based on a Query which requires
certain
criteria (Parameter Query). When I click the label to sort the
information
present, the Parameter Box reappears asking me to put in the date. When I
do, it sorts it like it should. I need to know if there is a way to
bypass
re-entering the same data (Date) in the parameter box. Thank you very
much
in advance!
Click to expand...
Click to expand...
 
Here's an example of creating a filter based on a date range:
http://allenbrowne.com/casu-08.html
The example applies the filter to a report, but you build the filter string
in exactly the same way for a form.

In the end, you just use:
Me.filter = strWhere
Me.FilterOn = True
instead of the OpenReport line.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

channell said:
Allen, I realized this just after I posted my question. I then posted
another question asking how to put an unbound textbox and an "OK" button
next
to it. That way, when I put in the date in the Unbound Textbox and then
Press "OK", it will return the desired results. Could you help me with
this?
Thank you so much!

Allen Browne said:
Parameters are really not a very good interface. You have just given one
example of where they are too frustrating.

A better approach might be to add an unbound text box to your form (e.g.
in
the Form Header), and use its AfterUpdate event procedure to apply a
filter
to the form. This will avoid the parameter re-request problem.

You may also need to set:
Me.OrderByOn = True

channell said:
I have the following code for my label to sort:

Private Sub txtLabel124_Click()
If Me.OrderBy = "[LastNameFirstName]" Then
Me.OrderBy = "[LastNameFirstName] DESC"
Else
Me.OrderBy = "[LastNameFirstName]"
End If
End Sub

The code works just fine, but here is my problem:

The Form which contains this code is based on a Query which requires
certain
criteria (Parameter Query). When I click the label to sort the
information
present, the Parameter Box reappears asking me to put in the date.
When I
do, it sorts it like it should. I need to know if there is a way to
bypass
re-entering the same data (Date) in the parameter box. Thank you very
much
in advance!
Click to expand...
Click to expand...
Click to expand...
 
I had the same problem -- re-sorting on a form based on a parameter query --
and was dismayed to hear that "Parameters are really not a very good
interface. "
I use parameter queries quite a lot and do not want to have to rework my
application to use form filters which seems like a step backwards.
Especially since all I want to do is sort the data I've already loaded -- why
does this require a re-query?!

Luckily, I seem to have found a workaround, but I would like some advice if
it is a reliable method or if there is a more efficient way. It involves
bypassing the Form.OrderBy and OrderByOn properties entirely, and setting the
Recordset.Sort property.

So instead of:
Me.OrderBy = "[LastNameFirstName]"
Me.OrderByOn = True

Use:
Me.Recordset.Sort = "[LastNameFirstName]"
Set Me.Recordset = Me.Recordset.OpenRecordset

When you do this, it does not re-prompt you for parameters (perhaps because
it does not re-query). It was surprising to me this worked, but it did. And
it seemed fast.

(I tried Set Me.Recordset = Me.Recordset.Clone, but apparently you only get
the sorted recordset when you re-open.)

Eric
I have the following code for my label to sort:

Private Sub txtLabel124_Click()
If Me.OrderBy = "[LastNameFirstName]" Then
Me.OrderBy = "[LastNameFirstName] DESC"
Else
Me.OrderBy = "[LastNameFirstName]"
End If
End Sub

The code works just fine, but here is my problem:

The Form which contains this code is based on a Query which requires
certain
criteria (Parameter Query). When I click the label to sort the
information
present, the Parameter Box reappears asking me to put in the date.
When I
do, it sorts it like it should. I need to know if there is a way to
bypass
re-entering the same data (Date) in the parameter box. Thank you very
much
in advance!
Click to expand...
Click to expand...
Click to expand...
 
That's creative: not a bad idea.

Examples of cases where you might like to test if this sorting gets lost:

- Does the sorting get lost if something reloads the form?
E.g. after a Requery, or after a FilterByForm?

- If you do this in a subform, does it get lost when you move record in the
main form? (This reloads the subform.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

ericgj said:
I had the same problem -- re-sorting on a form based on a parameter
query --
and was dismayed to hear that "Parameters are really not a very good
interface. "
I use parameter queries quite a lot and do not want to have to rework my
application to use form filters which seems like a step backwards.
Especially since all I want to do is sort the data I've already loaded --
why
does this require a re-query?!

Luckily, I seem to have found a workaround, but I would like some advice
if
it is a reliable method or if there is a more efficient way. It involves
bypassing the Form.OrderBy and OrderByOn properties entirely, and setting
the
Recordset.Sort property.

So instead of:
Me.OrderBy = "[LastNameFirstName]"
Me.OrderByOn = True

Use:
Me.Recordset.Sort = "[LastNameFirstName]"
Set Me.Recordset = Me.Recordset.OpenRecordset

When you do this, it does not re-prompt you for parameters (perhaps
because
it does not re-query). It was surprising to me this worked, but it did.
And
it seemed fast.

(I tried Set Me.Recordset = Me.Recordset.Clone, but apparently you only
get
the sorted recordset when you re-open.)

Eric
I have the following code for my label to sort:

Private Sub txtLabel124_Click()
If Me.OrderBy = "[LastNameFirstName]" Then
Me.OrderBy = "[LastNameFirstName] DESC"
Else
Me.OrderBy = "[LastNameFirstName]"
End If
End Sub

The code works just fine, but here is my problem:

The Form which contains this code is based on a Query which requires
certain
criteria (Parameter Query). When I click the label to sort the
information
present, the Parameter Box reappears asking me to put in the date.
When I
do, it sorts it like it should. I need to know if there is a way to
bypass
re-entering the same data (Date) in the parameter box. Thank you
very
much
in advance!
Click to expand...
:

Parameters are really not a very good interface. You have just given
one
example of where they are too frustrating.

A better approach might be to add an unbound text box to your form
(e.g.
in
the Form Header), and use its AfterUpdate event procedure to apply a
filter
to the form. This will avoid the parameter re-request problem.

You may also need to set:
Me.OrderByOn = True
Click to expand...
Click to expand...
Click to expand...
 
Back
Top