sort command buttons on forms

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

what's the best way to create sort buttons (in the design) of a form to sort
the various fields in either ascending or descending order???
 
what's the best way to create sort buttons (in the design) of a form to sort
the various fields in either ascending or descending order???
Click to expand...


"The various fields"? Your not giving us much to go on here.

This code in a command button click event will sort the named control
ascending:
[ControlName].SetFocus
DoCmd.RunCommand acCmdSortAscending

Change Ascending to Descending if wanted.

If you wish to be able to select the field by first clicking in it,
then:

Screen.PreviousControl.SetFocus
DoCmd.RunCommand acCmdSortAscending

Any reason why the built in tool buttons are not suitable?
 
Hey that was my question too, but boss-man wants separate buttons on the form
to sort each field. In other words, a botton to sort Account, a botton to
sort Group and a button to sort Customer (each in ascending and descending
order). If that's whay he wants, that's what he'll get (SMILE). I do not
want the person to have to click on the field first. Just click the button
and it sorts the particular field automatically....

I hope that's a little more clear. Will the code you provided work in this
scenerio???

fredg said:
what's the best way to create sort buttons (in the design) of a form to sort
the various fields in either ascending or descending order???
Click to expand...


"The various fields"? Your not giving us much to go on here.

This code in a command button click event will sort the named control
ascending:
[ControlName].SetFocus
DoCmd.RunCommand acCmdSortAscending

Change Ascending to Descending if wanted.

If you wish to be able to select the field by first clicking in it,
then:

Screen.PreviousControl.SetFocus
DoCmd.RunCommand acCmdSortAscending

Any reason why the built in tool buttons are not suitable?
Click to expand...
 
Hey that was my question too, but boss-man wants separate buttons on the form
to sort each field. In other words, a botton to sort Account, a botton to
sort Group and a button to sort Customer (each in ascending and descending
order). If that's whay he wants, that's what he'll get (SMILE). I do not
want the person to have to click on the field first. Just click the button
and it sorts the particular field automatically....

I hope that's a little more clear. Will the code you provided work in this
scenerio???

fredg said:
what's the best way to create sort buttons (in the design) of a form to sort
the various fields in either ascending or descending order???
Click to expand...

"The various fields"? Your not giving us much to go on here.

This code in a command button click event will sort the named control
ascending:
[ControlName].SetFocus
DoCmd.RunCommand acCmdSortAscending

Change Ascending to Descending if wanted.

If you wish to be able to select the field by first clicking in it,
then:

Screen.PreviousControl.SetFocus
DoCmd.RunCommand acCmdSortAscending

Any reason why the built in tool buttons are not suitable?
Click to expand...
Click to expand...

Why don't you try it?
 
I built a form where the user can simply click on the header text and the
form sorts (either asc or desc). The trick I used is to make the text a
hyperlink with an "OnClick" event. NO BUTTONS NEEDED!

Make the properties of each column header the following:
Type: LABEL
Hyperlink Subaddress: "Form [your form name]"
ControlTipText: "Sort by [TextName] . . ."
On Click: [Event Procedure]

'**********************************************
Option Compare Database
Option Explicit
Dim iFlag As Integer


Private Sub btn_Open_Click()
strMSG = "Opening Existing Record"
strDocName = "frm_FormDetail"
strLinkCriteria = "[policyNumber] = " & Me.[policyNumber] & ";"
DoCmd.Close acForm, "frm_myForm"

sSQL = "SELECT [fill in your SQL Statement here...] " & _
"WHERE " & strLinkCriteria
End Sub

'THE FOLLOWING "Re-Sort" subroutines ar
Private Sub Resort_By_Name_Click()
If Label_SortNameAscending.Visible = False Then
iFlag = 1
Else
iFlag = 2
End If
strCaption = " Re-Sorted by Name "
fReSortData
End Sub


Private Sub ReSort_By_Loss_Date_Click()
If Label_SortDateAscending.Visible = False Then
iFlag = 3
Else
iFlag = 4
End If
strCaption = " Re-Sorted by Date of Loss "
fReSortData
End Sub


Private Sub ReSort_By_Prog_Click()
If Label_SortProgAscending.Visible = False Then
iFlag = 5
Else
iFlag = 6
End If
strCaption = " Re-Sorted by Program "
fReSortData
End Sub


Private Sub ReSort_By_Claim_Click()
If Label_SortClaimAscending.Visible = False Then
iFlag = 7
Else
iFlag = 8
End If
strCaption = " Re-Sorted by Policy Number "
fReSortData
End Sub


Public Function fReSortData()
On Error GoTo Err_ResortData
Refresh_Labels:
Label_SortNameAscending.Visible = False
Label_SortNameDescending.Visible = False
Label_SortDateAscending.Visible = False
Label_SortDateDescending.Visible = False
Label_SortProgAscending.Visible = False
Label_SortProgDescending.Visible = False
Label_SortClaimAscending.Visible = False
Label_SortClaimDescending.Visible = False
Select Case iFlag
Case Is = 1
Label_SortNameAscending.Visible = True
strSortOrderSQL = "ORDER BY [LastName] & ', ' & [FirstName];"
Case Is = 2
Label_SortNameDescending.Visible = True
strSortOrderSQL = "ORDER BY [LastName] & ', ' & [FirstName] DESC;"
Case Is = 3
Label_SortDateAscending.Visible = True
strSortOrderSQL = "ORDER BY [sysDate], [LastName];"
Case Is = 4
Label_SortDateDescending.Visible = True
strSortOrderSQL = "ORDER BY [sysDate] DESC, [LastName];"
Case Is = 5
Label_SortProgAscending.Visible = True
strSortOrderSQL = "ORDER BY [prog], [policyNumber];"
Case Is = 6
Label_SortProgDescending.Visible = True
strSortOrderSQL = "ORDER BY [prog] DESC, [policyNumber];"
Case Is = 7
Label_SortClaimAscending.Visible = True
strSortOrderSQL = "ORDER BY [policyNumber];"
Case Is = 8
Label_SortClaimDescending.Visible = True
strSortOrderSQL = "ORDER BY [policyNumber] DESC;"
End Select
Select Case iFlag
Case Is = 1, 3, 5, 7
strCaption = strCaption & " (Ascending)"
Case Else
strCaption = strCaption & " (Descending)"
End Select

Set_SQL:
sSQL = strBaseSQL & strCriteriaSQL & strSortOrderSQL

Refresh_Forms:
Forms![frm_ClaimListing].RecordSource = sSQL
Forms![frm_ClaimListing].[btn_Close].Enabled = True
Forms![frm_ClaimListing].[btn_Close].SetFocus
Forms![frm_ClaimListing].Caption = strCaption
Forms![frm_ClaimListing].Refresh
Exit Function

Err_ResortData:
fDisplayError
Exit Function

End Function
' EOM ********************************
Lesley said:
Hey that was my question too, but boss-man wants separate buttons on the form
to sort each field. In other words, a botton to sort Account, a botton to
sort Group and a button to sort Customer (each in ascending and descending
order). If that's whay he wants, that's what he'll get (SMILE). I do not
want the person to have to click on the field first. Just click the button
and it sorts the particular field automatically....

I hope that's a little more clear. Will the code you provided work in this
scenerio???

fredg said:
what's the best way to create sort buttons (in the design) of a form to sort
the various fields in either ascending or descending order???
Click to expand...


"The various fields"? Your not giving us much to go on here.

This code in a command button click event will sort the named control
ascending:
[ControlName].SetFocus
DoCmd.RunCommand acCmdSortAscending

Change Ascending to Descending if wanted.

If you wish to be able to select the field by first clicking in it,
then:

Screen.PreviousControl.SetFocus
DoCmd.RunCommand acCmdSortAscending

Any reason why the built in tool buttons are not suitable?
Click to expand...
Click to expand...
 
ok Jim P. I tried your method and the only thing that works is the
ControlTipText when I scroll over the label. Here's my code. Can you look
at it and tell me why it isn't working??? Also, are the "Refresh_Forms"
statements needed? If so, have I dont that part correctly? Absolutely
NOTHING happens when I click on any of the form labels - no error message or
anything...


Option Compare Database
Option Explicit
Dim iFlag As Integer


Private Sub btn_Open_Click()
StrMSG = "Opening Existing Record"
StrDocName = "frm_FormDetail"
StrLinkCriteria = "[Group Name]=" & ";"
DoCmd.Close acForm, "Group Name Table"

sSQL = "SELECT [ }"

End Sub

Private Sub Resort_by_GroupNm_Click()
If Label_SortGroupNmAscending.Visible = False Then
iFlag = 1
Else
iFlag = 2
End If
strCaption = "Resort by Group Name"
fReSortData
End Sub

Private Sub Resort_by_AcctNo_Click()
If Label_SortAcctNoAscending.Visible = False Then
iFlag = 3
Else
iFlag = 4
End If
strCaption = "Resort by Account No"
fReSortData
End Sub

Private Sub Resort_by_CustNm_Click()
If Label_SortCustNmAscending.Visible = False Then
iFlag = 5
Else
iFlag = 6
End If
strCaption = "Re-Sorted by Customer Name"
fReSortData
End Sub

Public Function fReSortData()
On Error GoTo Err_ResortData
Refresh_Labels:
Label_SortGroupNmAscending.Visible
Label_SortGroupNmDescending.Visible
Label_SortAcctNoAscending.Visible
Label_SortAcctNoDescending.Visible
Label_SortCustNmAscending.Visible
Label_SortCustNmDescending.Visible
Select Case iFlag
Case Is = 1
Label_SortGroupNmAscending.Visible = True
strSortOrderSQL = "ORDER BY [Group Name];"
Case Is = 2
Label_SortGroupNmDescending.Visible = True
strSortOrderSQL = "ORDER By [Group Name] DESC;"
Case Is = 3
Label_SortAcctNoAscending.Visible = True
strSortOrderSQL = "ORDER By [Reference #], [Group Name];"
Case Is = 4
Label_SortAcctNoDescending.Visible = True
strSortOrderSQL = "ORDER By [Reference #] DESC, [Group Name];"
Case Is = 5
Label_SortCustNmAscending.Visible = True
strSortOrderSQL = "ORDER By [Customer Name], [Reference #];"
Case Is = 6
Label_SortCustNmDescending.Visible = True
strSortOrderSQL = "ORDER By [Customer Name] DESC, [Reference #];"
End Select
Select Case iFlag
Case Is = 1, 3, 5, 7
strCaption = strCaption & " (Ascending)"
Case Else
strCaption = strCaption & " (Descending)"
End Select

Set_SQL:
sSQL = strBaseSQL & strCriteriaSQL & strSortOrderSQL

Refresh_Forms:
Forms![Group Name Table].RecordSource = sSQL
Forms![Group Name Table].[btn_Main_Menu].Enabled = True
Forms![Group Name Table].[btn_Main_Menu].SetFocus
Forms![Group Name Table].Caption = strCaption
Forms![Group Name Table].Refresh
Exit Function

Err_ResortData:
fDisplayError
Exit Function



jim p said:
I built a form where the user can simply click on the header text and the
form sorts (either asc or desc). The trick I used is to make the text a
hyperlink with an "OnClick" event. NO BUTTONS NEEDED!

Make the properties of each column header the following:
Type: LABEL
Hyperlink Subaddress: "Form [your form name]"
ControlTipText: "Sort by [TextName] . . ."
On Click: [Event Procedure]

'**********************************************
Option Compare Database
Option Explicit
Dim iFlag As Integer


Private Sub btn_Open_Click()
strMSG = "Opening Existing Record"
strDocName = "frm_FormDetail"
strLinkCriteria = "[policyNumber] = " & Me.[policyNumber] & ";"
DoCmd.Close acForm, "frm_myForm"

sSQL = "SELECT [fill in your SQL Statement here...] " & _
"WHERE " & strLinkCriteria
End Sub

'THE FOLLOWING "Re-Sort" subroutines ar
Private Sub Resort_By_Name_Click()
If Label_SortNameAscending.Visible = False Then
iFlag = 1
Else
iFlag = 2
End If
strCaption = " Re-Sorted by Name "
fReSortData
End Sub


Private Sub ReSort_By_Loss_Date_Click()
If Label_SortDateAscending.Visible = False Then
iFlag = 3
Else
iFlag = 4
End If
strCaption = " Re-Sorted by Date of Loss "
fReSortData
End Sub


Private Sub ReSort_By_Prog_Click()
If Label_SortProgAscending.Visible = False Then
iFlag = 5
Else
iFlag = 6
End If
strCaption = " Re-Sorted by Program "
fReSortData
End Sub


Private Sub ReSort_By_Claim_Click()
If Label_SortClaimAscending.Visible = False Then
iFlag = 7
Else
iFlag = 8
End If
strCaption = " Re-Sorted by Policy Number "
fReSortData
End Sub


Public Function fReSortData()
On Error GoTo Err_ResortData
Refresh_Labels:
Label_SortNameAscending.Visible = False
Label_SortNameDescending.Visible = False
Label_SortDateAscending.Visible = False
Label_SortDateDescending.Visible = False
Label_SortProgAscending.Visible = False
Label_SortProgDescending.Visible = False
Label_SortClaimAscending.Visible = False
Label_SortClaimDescending.Visible = False
Select Case iFlag
Case Is = 1
Label_SortNameAscending.Visible = True
strSortOrderSQL = "ORDER BY [LastName] & ', ' & [FirstName];"
Case Is = 2
Label_SortNameDescending.Visible = True
strSortOrderSQL = "ORDER BY [LastName] & ', ' & [FirstName] DESC;"
Case Is = 3
Label_SortDateAscending.Visible = True
strSortOrderSQL = "ORDER BY [sysDate], [LastName];"
Case Is = 4
Label_SortDateDescending.Visible = True
strSortOrderSQL = "ORDER BY [sysDate] DESC, [LastName];"
Case Is = 5
Label_SortProgAscending.Visible = True
strSortOrderSQL = "ORDER BY [prog], [policyNumber];"
Case Is = 6
Label_SortProgDescending.Visible = True
strSortOrderSQL = "ORDER BY [prog] DESC, [policyNumber];"
Case Is = 7
Label_SortClaimAscending.Visible = True
strSortOrderSQL = "ORDER BY [policyNumber];"
Case Is = 8
Label_SortClaimDescending.Visible = True
strSortOrderSQL = "ORDER BY [policyNumber] DESC;"
End Select
Select Case iFlag
Case Is = 1, 3, 5, 7
strCaption = strCaption & " (Ascending)"
Case Else
strCaption = strCaption & " (Descending)"
End Select

Set_SQL:
sSQL = strBaseSQL & strCriteriaSQL & strSortOrderSQL

Refresh_Forms:
Forms![frm_ClaimListing].RecordSource = sSQL
Forms![frm_ClaimListing].[btn_Close].Enabled = True
Forms![frm_ClaimListing].[btn_Close].SetFocus
Forms![frm_ClaimListing].Caption = strCaption
Forms![frm_ClaimListing].Refresh
Exit Function

Err_ResortData:
fDisplayError
Exit Function

End Function
' EOM ********************************
Lesley said:
Hey that was my question too, but boss-man wants separate buttons on the form
to sort each field. In other words, a botton to sort Account, a botton to
sort Group and a button to sort Customer (each in ascending and descending
order). If that's whay he wants, that's what he'll get (SMILE). I do not
want the person to have to click on the field first. Just click the button
and it sorts the particular field automatically....

I hope that's a little more clear. Will the code you provided work in this
scenerio???

fredg said:
On Mon, 28 Aug 2006 16:34:01 -0700, Lesley wrote:

what's the best way to create sort buttons (in the design) of a form to sort
the various fields in either ascending or descending order???


"The various fields"? Your not giving us much to go on here.

This code in a command button click event will sort the named control
ascending:
[ControlName].SetFocus
DoCmd.RunCommand acCmdSortAscending

Change Ascending to Descending if wanted.

If you wish to be able to select the field by first clicking in it,
then:

Screen.PreviousControl.SetFocus
DoCmd.RunCommand acCmdSortAscending

Any reason why the built in tool buttons are not suitable?
Click to expand...
Click to expand...
Click to expand...
 
Leslie,
Make the properties of each column header the following:
Hyperlink Subaddress: "Form [your form name]"
On Click: [Event Procedure]

Try the following...
When you open the properties of your column header text, [Resort_By Name]
then scroll down to [On Click] and make sure you choose the [Event Procedure]
in the value. (Don't simply verify... ACTUALLY CHOOSE THE OPTION.) Then
click on the ellipses to go to the VBA editor. Youre cursor should land
here...

Private Sub Resort_By_Name_Click()
If Label_SortNameAscending.Visible = False Then
Click to expand...
iFlag = 1
Else
iFlag = 2
End If
strCaption = " Re-Sorted by Name "
fReSortData
'ADD THIS CODE.................
MSGBOX "The Hypertext event is working"
End Sub
'End of code..............

If the form still doesn't work, make sure your references contain at least
the following four references:
Visual Basic for Applications
Microsoft Access 9.0 Object Library
OLE Automation
Microsoft ActiveX Data Objects 2.# Library
Click to expand...
(ActiveX DAO can be 2.1 thru 2.8, but only choose one!)

If all else fails, you contact me and I'll be happy to e-mail the form to
you. I'll provide my Internet email... but e-mail you the form from my work
email.
-jim p [mailto:[email protected]]


Lesley said:
ok Jim P. I tried your method and the only thing that works is the
ControlTipText when I scroll over the label. Here's my code. Can you look
at it and tell me why it isn't working??? Also, are the "Refresh_Forms"
statements needed? If so, have I dont that part correctly? Absolutely
NOTHING happens when I click on any of the form labels - no error message or
anything...


Option Compare Database
Option Explicit
Dim iFlag As Integer


Private Sub btn_Open_Click()
StrMSG = "Opening Existing Record"
StrDocName = "frm_FormDetail"
StrLinkCriteria = "[Group Name]=" & ";"
DoCmd.Close acForm, "Group Name Table"

sSQL = "SELECT [ }"

End Sub

Private Sub Resort_by_GroupNm_Click()
If Label_SortGroupNmAscending.Visible = False Then
iFlag = 1
Else
iFlag = 2
End If
strCaption = "Resort by Group Name"
fReSortData
End Sub

Private Sub Resort_by_AcctNo_Click()
If Label_SortAcctNoAscending.Visible = False Then
iFlag = 3
Else
iFlag = 4
End If
strCaption = "Resort by Account No"
fReSortData
End Sub

Private Sub Resort_by_CustNm_Click()
If Label_SortCustNmAscending.Visible = False Then
iFlag = 5
Else
iFlag = 6
End If
strCaption = "Re-Sorted by Customer Name"
fReSortData
End Sub

Public Function fReSortData()
On Error GoTo Err_ResortData
Refresh_Labels:
Label_SortGroupNmAscending.Visible
Label_SortGroupNmDescending.Visible
Label_SortAcctNoAscending.Visible
Label_SortAcctNoDescending.Visible
Label_SortCustNmAscending.Visible
Label_SortCustNmDescending.Visible
Select Case iFlag
Case Is = 1
Label_SortGroupNmAscending.Visible = True
strSortOrderSQL = "ORDER BY [Group Name];"
Case Is = 2
Label_SortGroupNmDescending.Visible = True
strSortOrderSQL = "ORDER By [Group Name] DESC;"
Case Is = 3
Label_SortAcctNoAscending.Visible = True
strSortOrderSQL = "ORDER By [Reference #], [Group Name];"
Case Is = 4
Label_SortAcctNoDescending.Visible = True
strSortOrderSQL = "ORDER By [Reference #] DESC, [Group Name];"
Case Is = 5
Label_SortCustNmAscending.Visible = True
strSortOrderSQL = "ORDER By [Customer Name], [Reference #];"
Case Is = 6
Label_SortCustNmDescending.Visible = True
strSortOrderSQL = "ORDER By [Customer Name] DESC, [Reference #];"
End Select
Select Case iFlag
Case Is = 1, 3, 5, 7
strCaption = strCaption & " (Ascending)"
Case Else
strCaption = strCaption & " (Descending)"
End Select

Set_SQL:
sSQL = strBaseSQL & strCriteriaSQL & strSortOrderSQL

Refresh_Forms:
Forms![Group Name Table].RecordSource = sSQL
Forms![Group Name Table].[btn_Main_Menu].Enabled = True
Forms![Group Name Table].[btn_Main_Menu].SetFocus
Forms![Group Name Table].Caption = strCaption
Forms![Group Name Table].Refresh
Exit Function

Err_ResortData:
fDisplayError
Exit Function



jim p said:
I built a form where the user can simply click on the header text and the
form sorts (either asc or desc). The trick I used is to make the text a
hyperlink with an "OnClick" event. NO BUTTONS NEEDED!

Make the properties of each column header the following:
Type: LABEL
Hyperlink Subaddress: "Form [your form name]"
ControlTipText: "Sort by [TextName] . . ."
On Click: [Event Procedure]

'**********************************************
Option Compare Database
Option Explicit
Dim iFlag As Integer


Private Sub btn_Open_Click()
strMSG = "Opening Existing Record"
strDocName = "frm_FormDetail"
strLinkCriteria = "[policyNumber] = " & Me.[policyNumber] & ";"
DoCmd.Close acForm, "frm_myForm"

sSQL = "SELECT [fill in your SQL Statement here...] " & _
"WHERE " & strLinkCriteria
End Sub

'THE FOLLOWING "Re-Sort" subroutines ar
Private Sub Resort_By_Name_Click()
If Label_SortNameAscending.Visible = False Then
iFlag = 1
Else
iFlag = 2
End If
strCaption = " Re-Sorted by Name "
fReSortData
End Sub


Private Sub ReSort_By_Loss_Date_Click()
If Label_SortDateAscending.Visible = False Then
iFlag = 3
Else
iFlag = 4
End If
strCaption = " Re-Sorted by Date of Loss "
fReSortData
End Sub


Private Sub ReSort_By_Prog_Click()
If Label_SortProgAscending.Visible = False Then
iFlag = 5
Else
iFlag = 6
End If
strCaption = " Re-Sorted by Program "
fReSortData
End Sub


Private Sub ReSort_By_Claim_Click()
If Label_SortClaimAscending.Visible = False Then
iFlag = 7
Else
iFlag = 8
End If
strCaption = " Re-Sorted by Policy Number "
fReSortData
End Sub


Public Function fReSortData()
On Error GoTo Err_ResortData
Refresh_Labels:
Label_SortNameAscending.Visible = False
Label_SortNameDescending.Visible = False
Label_SortDateAscending.Visible = False
Label_SortDateDescending.Visible = False
Label_SortProgAscending.Visible = False
Label_SortProgDescending.Visible = False
Label_SortClaimAscending.Visible = False
Label_SortClaimDescending.Visible = False
Select Case iFlag
Case Is = 1
Label_SortNameAscending.Visible = True
strSortOrderSQL = "ORDER BY [LastName] & ', ' & [FirstName];"
Case Is = 2
Label_SortNameDescending.Visible = True
strSortOrderSQL = "ORDER BY [LastName] & ', ' & [FirstName] DESC;"
Case Is = 3
Label_SortDateAscending.Visible = True
strSortOrderSQL = "ORDER BY [sysDate], [LastName];"
Case Is = 4
Label_SortDateDescending.Visible = True
strSortOrderSQL = "ORDER BY [sysDate] DESC, [LastName];"
Case Is = 5
Label_SortProgAscending.Visible = True
strSortOrderSQL = "ORDER BY [prog], [policyNumber];"
Case Is = 6
Label_SortProgDescending.Visible = True
strSortOrderSQL = "ORDER BY [prog] DESC, [policyNumber];"
Case Is = 7
Label_SortClaimAscending.Visible = True
strSortOrderSQL = "ORDER BY [policyNumber];"
Case Is = 8
Label_SortClaimDescending.Visible = True
strSortOrderSQL = "ORDER BY [policyNumber] DESC;"
End Select
Select Case iFlag
Case Is = 1, 3, 5, 7
strCaption = strCaption & " (Ascending)"
Case Else
strCaption = strCaption & " (Descending)"
End Select

Set_SQL:
sSQL = strBaseSQL & strCriteriaSQL & strSortOrderSQL

Refresh_Forms:
Forms![frm_ClaimListing].RecordSource = sSQL
Forms![frm_ClaimListing].[btn_Close].Enabled = True
Forms![frm_ClaimListing].[btn_Close].SetFocus
Forms![frm_ClaimListing].Caption = strCaption
Forms![frm_ClaimListing].Refresh
Exit Function

Err_ResortData:
fDisplayError
Exit Function

End Function
' EOM ********************************
Lesley said:
Hey that was my question too, but boss-man wants separate buttons on the form
to sort each field. In other words, a botton to sort Account, a botton to
sort Group and a button to sort Customer (each in ascending and descending
order). If that's whay he wants, that's what he'll get (SMILE). I do not
want the person to have to click on the field first. Just click the button
and it sorts the particular field automatically....

I hope that's a little more clear. Will the code you provided work in this
scenerio???

:

On Mon, 28 Aug 2006 16:34:01 -0700, Lesley wrote:

what's the best way to create sort buttons (in the design) of a form to sort
the various fields in either ascending or descending order???


"The various fields"? Your not giving us much to go on here.

This code in a command button click event will sort the named control
ascending:
[ControlName].SetFocus
DoCmd.RunCommand acCmdSortAscending

Change Ascending to Descending if wanted.

If you wish to be able to select the field by first clicking in it,
then:

Screen.PreviousControl.SetFocus
DoCmd.RunCommand acCmdSortAscending

Any reason why the built in tool buttons are not suitable?
Click to expand...
Click to expand...
Click to expand...
 
Jim,

The four references in the database are:
- Visual Basic for Applications
- Microsoft Access 11.0 Object Library
- OLE Automation
- Microsoft Forms 2.0 Object Library
***Microsoft ActiveX Data Objects 2.# Library IS NOT available on the
reference list***

The PROPERTIES for each LABEL are as follows:
Label: Resort_by_Group
Hyperlink subaddress: Form frm_GroupName
On Click: [Event Procedure]

QUESTION??? Should there be brackets around the form name in the hyperlinck
subaddress (Ex. Form [frm_GroupName])??? Or, can it be written as 'Form
frm_GroupName'??? Please advise.

When I click on the On Click [Event Procedure] for each label the cursor
lands here:
Private Sub Resort_by_Group_Click()
If Label_SortGroupAscending.Visible = False Then
iFlag = 1
Else
iFlag = 2
End If
strCaption = "Resort by Group Name"
MsgBox "The Hypertext event is working"
End Sub

**********************
The On Click Event Procedure for each label is pointing to the correct VBA
code. However, when I go back to Form View and click on the respective
label, I get the following error message: "Run-time error '424': Object
Required". The error points to the following line when I click on each label:

When I click on the Group label...
IF Label_SortGroupAscending.Visible = False Then

When I click on the Account label...
IF Label_SortAccountAscending.Visible = False Then

When I click on the Customer label...
IF Label_SortCustomerAscending.Visible = False Then

HELP! HELP! HELP! HELP!
 
Sorry about the confusion...

NO brackets around the form name in the hyperlink subaddress. Should read:
Form frm_GroupName

The reason for the Run-Time '424' Error... I forgot to have you add eight
additional controls on the form. They visually show the user which column is
being sorted (ascending or descending - or eight possibilities in this
example.)

Each pair is placed on the form directly to the right of the corresponding
column hypertexts. For example, Label_SortNameAscending and
Label_SorNameDescending should be placed on top of each other but positioned
immediately to the right of the column label: "ReSort_By_Name". Seven of the
labels are invisible, and the code makes the text visible or invisible
depending on the "Case" statement.

The four pairs of texboxes are named:
Label_SortNameAscending
Label_SortNameDescending

Label_SortDateAscending
Label_SortDateDescending

Label_SortProgAscending
Label_SortProgDescending

Label_SortClaimAscending
Label_SortClaimDescending

I make the property of all except the first one...
Visible = NO

I use a Wingdings ASCENDING symbol and a DESCENDING symbol in the Caption
field depending on the label.
Ascending = Wingdings symbol: 0xD9
Descending = Wingdings symbol: 0xDA

Your ***Microsoft ActiveX Data Objects 2.# can be:
"Microsoft ActiveX Data Objects 2.1" or
"Microsoft ActiveX Data Objects 2.5" or
"Microsoft ActiveX Data Objects 2.6" or
"Microsoft ActiveX Data Objects 2.7"

(I'm not sure if "Microsoft ActiveX Data Objects 2.0" works... I'm guessing
that it will work.)

As I mentioned before, I will be happy to e-mail an empty DB with just the
form... then you can simply import the form into your database and look at
the code.

-jim p

======================================

Lesley said:
Jim,

The four references in the database are:
- Visual Basic for Applications
- Microsoft Access 11.0 Object Library
- OLE Automation
- Microsoft Forms 2.0 Object Library
***Microsoft ActiveX Data Objects 2.# Library IS NOT available on the
reference list***

The PROPERTIES for each LABEL are as follows:
Label: Resort_by_Group
Hyperlink subaddress: Form frm_GroupName
On Click: [Event Procedure]

QUESTION??? Should there be brackets around the form name in the hyperlinck
subaddress (Ex. Form [frm_GroupName])??? Or, can it be written as 'Form
frm_GroupName'??? Please advise.

When I click on the On Click [Event Procedure] for each label the cursor
lands here:
Private Sub Resort_by_Group_Click()
If Label_SortGroupAscending.Visible = False Then
iFlag = 1
Else
iFlag = 2
End If
strCaption = "Resort by Group Name"
MsgBox "The Hypertext event is working"
End Sub

**********************
The On Click Event Procedure for each label is pointing to the correct VBA
code. However, when I go back to Form View and click on the respective
label, I get the following error message: "Run-time error '424': Object
Required". The error points to the following line when I click on each label:

When I click on the Group label...
IF Label_SortGroupAscending.Visible = False Then

When I click on the Account label...
IF Label_SortAccountAscending.Visible = False Then

When I click on the Customer label...
IF Label_SortCustomerAscending.Visible = False Then

HELP! HELP! HELP! HELP!
Click to expand...
 
Ok, you've lost me again....

Are you creating four pairs of LABELS or TEXTBOXES to the right of the
hypertext columns??? I'm having difficulties understanding how you added the
Wingdings symbols in the 'Caption' field. Could you explain this to me in
more detail because it doesn't appear to be working for me as you've laid it
out...

The "caption" field is associated with LABELS and not Textboxes.

jim p said:
Sorry about the confusion...

NO brackets around the form name in the hyperlink subaddress. Should read:
Form frm_GroupName

The reason for the Run-Time '424' Error... I forgot to have you add eight
additional controls on the form. They visually show the user which column is
being sorted (ascending or descending - or eight possibilities in this
example.)

Each pair is placed on the form directly to the right of the corresponding
column hypertexts. For example, Label_SortNameAscending and
Label_SorNameDescending should be placed on top of each other but positioned
immediately to the right of the column label: "ReSort_By_Name". Seven of the
labels are invisible, and the code makes the text visible or invisible
depending on the "Case" statement.

The four pairs of texboxes are named:
Label_SortNameAscending
Label_SortNameDescending

Label_SortDateAscending
Label_SortDateDescending

Label_SortProgAscending
Label_SortProgDescending

Label_SortClaimAscending
Label_SortClaimDescending

I make the property of all except the first one...
Visible = NO

I use a Wingdings ASCENDING symbol and a DESCENDING symbol in the Caption
field depending on the label.
Ascending = Wingdings symbol: 0xD9
Descending = Wingdings symbol: 0xDA

Your ***Microsoft ActiveX Data Objects 2.# can be:
"Microsoft ActiveX Data Objects 2.1" or
"Microsoft ActiveX Data Objects 2.5" or
"Microsoft ActiveX Data Objects 2.6" or
"Microsoft ActiveX Data Objects 2.7"

(I'm not sure if "Microsoft ActiveX Data Objects 2.0" works... I'm guessing
that it will work.)

As I mentioned before, I will be happy to e-mail an empty DB with just the
form... then you can simply import the form into your database and look at
the code.

-jim p

======================================

Lesley said:
Jim,

The four references in the database are:
- Visual Basic for Applications
- Microsoft Access 11.0 Object Library
- OLE Automation
- Microsoft Forms 2.0 Object Library
***Microsoft ActiveX Data Objects 2.# Library IS NOT available on the
reference list***

The PROPERTIES for each LABEL are as follows:
Label: Resort_by_Group
Hyperlink subaddress: Form frm_GroupName
On Click: [Event Procedure]

QUESTION??? Should there be brackets around the form name in the hyperlinck
subaddress (Ex. Form [frm_GroupName])??? Or, can it be written as 'Form
frm_GroupName'??? Please advise.

When I click on the On Click [Event Procedure] for each label the cursor
lands here:
Private Sub Resort_by_Group_Click()
If Label_SortGroupAscending.Visible = False Then
iFlag = 1
Else
iFlag = 2
End If
strCaption = "Resort by Group Name"
MsgBox "The Hypertext event is working"
End Sub

**********************
The On Click Event Procedure for each label is pointing to the correct VBA
code. However, when I go back to Form View and click on the respective
label, I get the following error message: "Run-time error '424': Object
Required". The error points to the following line when I click on each label:

When I click on the Group label...
IF Label_SortGroupAscending.Visible = False Then

When I click on the Account label...
IF Label_SortAccountAscending.Visible = False Then

When I click on the Customer label...
IF Label_SortCustomerAscending.Visible = False Then

HELP! HELP! HELP! HELP!
Click to expand...
Click to expand...
 
Back
Top