Control dimension in OrderBy

[Silvio]

Hello, I am using a VBA code (OnClick on the column’s label) to sort that
specific column ascending or descending. The code works fine, however reduce
repetitive coding, I would like to be able declare the column control as a
dimension so I don’t need to type the Colum *name* over and over again for
each sort instance.

I tried some ting like below but it does not work:

Dim MyControl as Control
MyControl = “EmployeeNameâ€

If Me.OrderBy = "MyControl" Or Me.OrderBy <> " MyControl DESC" Then
Me.OrderBy = " MyControl DESC"
Me.OrderByOn = True
Else
Me.OrderBy = " MyControl "
Me.OrderByOn = True
End If

 

[Dirk Goldgar]

Hello, I am using a VBA code (OnClick on the column’s label) to sort that
specific column ascending or descending. The code works fine, however
reduce
repetitive coding, I would like to be able declare the column control as a
dimension so I don’t need to type the Colum *name* over and over again for
each sort instance.

I tried some ting like below but it does not work:

Dim MyControl as Control
MyControl = “EmployeeNameâ€

If Me.OrderBy = "MyControl" Or Me.OrderBy <> " MyControl DESC" Then
Me.OrderBy = " MyControl DESC"
Me.OrderByOn = True
Else
Me.OrderBy = " MyControl "
Me.OrderByOn = True
End If

As I understand what you are asking, you want to make the name of the field
a variable, and use that variable to set the form's OrderBy property,
instead of hard-coding for every possible field name. If I'm right about
what you want, then your code could be modelled on this:

'----- start of code -----
Dim strFieldName As String

' For this example, we pick a field name.
strFieldName = "EmployeeName"

If Me.OrderBy = strFieldName _
Or Me.OrderBy <> (strFieldName & " DESC") _
Then
Me.OrderBy = strFieldName & " DESC"
Else
Me.OrderBy = strFieldName
End If

Me.OrderByOn = True
'----- end of code -----

 

[Silvio]

Thanks Dirk, this works just as I wanted it! Taking this a step further, is
there a way to write the code once for each filed (Public constant) and use
it over and over in my project throughout my forms? If so, would you be so
kind to guide me step-by-step on how to do so?

 

[Dirk Goldgar]

Thanks Dirk, this works just as I wanted it! Taking this a step further,
is
there a way to write the code once for each filed (Public constant) and
use
it over and over in my project throughout my forms? If so, would you be so
kind to guide me step-by-step on how to do so?

I'm not completely sure I understand what you want, but it wouldn't be hard
to turn the code into a function that could be called from any form. One
would want to pass a reference to the form and the name of the field. Try
pasting this function into a standard module:

'----- start of code -----
Function fncOrderByField( _
frm As Access.Form, _
strFieldName As String, _
Optional blnDesc As Boolean)

' Order the form <frm> by the field whose name is
' passed as <strFieldName>. If the optional argument
' <blnDesc> is specified, then the requested sequence
' is Descending; otherwise it is Ascending. However,
' if the form is already ordered by the specified field
' in the specified sequence (ascending/decending), then
' reverse the sequence on that field.

Dim strSeqCurrent As String
Dim strSeqWanted As String

strSeqWanted = strFieldName & IIf(blnDesc, " DESC", "")

With frm

' Determine current sequence, standardized for
' comparison to strSeqWanted.
If .OrderByOn Then
strSeqCurrent = .OrderBy
If strSeqCurrent Like "* ASC" Then
strSeqCurrent = Left$(strSeqCurrent, Len(strSeqCurrent) - 4)
End If
End If

If strSeqCurrent <> strSeqWanted Then
.OrderBy = strSeqWanted
Else
' We're currently sorted as originally requested,
' so flip the sort sequence on that field.
If blnDesc Then
' Currently sorted DESC; change to ASC.
.OrderBy = strFieldName
Else
.OrderBy = strFieldName & " DESC"
End If
End If

.OrderByOn = True

End With

End Function
'----- end of code -----

Then, on any form where you want to use this feature, you can put a direct
call to the function into the OnClick property of each label, like this:

=fncOrderByField([Form],"EmployeeName",True)

The above would cause the form to be sorted by EmployeeName descending,
unless the form is already sorted that way, in which case it would reverse
the sort order to EmployeeName ascending. Note that the keyword [Form] is
exactly what should be passed by the function expression; I do *not* intend
that you should replace that with the name of the form.

The way the function is written, you can leave off the third argument
(specified above as True) if you want the form to be sorted first as
ascending, rather than descending.

 

[Silvio]

Dirk you are THE man! That's exactly what I was looking for, you made my day!
Thanks a million.

Thanks Dirk, this works just as I wanted it! Taking this a step further,
is
there a way to write the code once for each filed (Public constant) and
use
it over and over in my project throughout my forms? If so, would you be so
kind to guide me step-by-step on how to do so?

I'm not completely sure I understand what you want, but it wouldn't be hard
to turn the code into a function that could be called from any form. One
would want to pass a reference to the form and the name of the field. Try
pasting this function into a standard module:

'----- start of code -----
Function fncOrderByField( _
frm As Access.Form, _
strFieldName As String, _
Optional blnDesc As Boolean)

' Order the form <frm> by the field whose name is
' passed as <strFieldName>. If the optional argument
' <blnDesc> is specified, then the requested sequence
' is Descending; otherwise it is Ascending. However,
' if the form is already ordered by the specified field
' in the specified sequence (ascending/decending), then
' reverse the sequence on that field.

Dim strSeqCurrent As String
Dim strSeqWanted As String

strSeqWanted = strFieldName & IIf(blnDesc, " DESC", "")

With frm

' Determine current sequence, standardized for
' comparison to strSeqWanted.
If .OrderByOn Then
strSeqCurrent = .OrderBy
If strSeqCurrent Like "* ASC" Then
strSeqCurrent = Left$(strSeqCurrent, Len(strSeqCurrent) - 4)
End If
End If

If strSeqCurrent <> strSeqWanted Then
.OrderBy = strSeqWanted
Else
' We're currently sorted as originally requested,
' so flip the sort sequence on that field.
If blnDesc Then
' Currently sorted DESC; change to ASC.
.OrderBy = strFieldName
Else
.OrderBy = strFieldName & " DESC"
End If
End If

.OrderByOn = True

End With

End Function
'----- end of code -----

Then, on any form where you want to use this feature, you can put a direct
call to the function into the OnClick property of each label, like this:

=fncOrderByField([Form],"EmployeeName",True)

The above would cause the form to be sorted by EmployeeName descending,
unless the form is already sorted that way, in which case it would reverse
the sort order to EmployeeName ascending. Note that the keyword [Form] is
exactly what should be passed by the function expression; I do *not* intend
that you should replace that with the name of the form.

The way the function is written, you can leave off the third argument
(specified above as True) if you want the form to be sorted first as
ascending, rather than descending.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)