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???
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???
"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???
"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?
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???
"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?
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?
iFlag = 1If Label_SortNameAscending.Visible = False Then
(ActiveX DAO can be 2.1 thru 2.8, but only choose one!)Visual Basic for Applications
Microsoft Access 9.0 Object Library
OLE Automation
Microsoft ActiveX Data Objects 2.# Library
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?
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!
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!
Want to reply to this thread or ask your own question?
You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.