Sorting a listbox in a form

E

electrix

New member
Local time
Today, 18:55
Joined
Apr 8, 2008
Messages
9
Hi. I have a listbox with four fields in a form: User_First_Name, User_Last_Name, Phone_No, and Product_ID. I am trying to create command buttons for each field that, when clicked, will reorder the listbox by that field. So, I made four buttons in the form and tried to configure them accordingly.

Before adding the command buttons, when an entry was highlighted in the listbox (named QuickList), it populated several text box fields on the other side of the form. After the button to reorder the form is clicked, however, clicking on one of the entries in the listbox results in the following error:

"Run-time error '13': Type mismatch"

In addition, clicking on the FIRST NAME, LAST NAME, and PHONE NO buttons all make the User_First_Name and Product_ID fields in the listbox disappear. The PHONE NO button will reorder the listbox by phone number, but both the FIRST NAME and LAST NAME buttons reorder the listbox by last name. Clicking the PRODUCT ID button makes all four fields disappear.

Here is the code for the form.

Code: 
Option Compare Database
Option Explicit
'* Define the RowSource for the listbox, along with different sorted variations
Const mcRowSourceBasis = "SELECT DISTINCTROW User_First_Name, User_Last_Name, Phone_No FROM [Main]"
Const mcRowSourceUnsorted = mcRowSourceBasis & ";"
Const mcRowSourceSortCol1 = mcRowSourceBasis & " Order By 1;"
Const mcRowSourceSortCol1Desc = mcRowSourceBasis & " Order By 1 Desc;"
Const mcRowSourceSortCol2 = mcRowSourceBasis & " Order By 2;"
Const mcRowSourceSortCol2Desc = mcRowSourceBasis & " Order By 2 Desc;"
Const mcRowSourceSortCol3 = mcRowSourceBasis & " Order By 3;"
Const mcRowSourceSortCol3Desc = mcRowSourceBasis & " Order By 3 Desc;"
Const mcRowSourceSortCol4 = mcRowSourceBasis & " Order By 4;"
Const mcRowSourceSortCol4Desc = mcRowSourceBasis & " Order By 4 Desc;"

Private Sub Command27_Click()
On Error GoTo Err_Command27_Click

    Dim stDocName As String
    Dim stLinkCriteria As String

    stDocName = "History"
    
    stLinkCriteria = "[System_ID]=" & "'" & Me![System_ID] & "'"
    DoCmd.OpenForm stDocName, , , stLinkCriteria
History.Visible = False
Exit_Command27_Click:
    Exit Sub

Err_Command27_Click:
    MsgBox Err.Description
    Resume Exit_Command27_Click
    
End Sub


  
Private Sub Account_AfterUpdate()
Me.Account_Desc = Me.Account.Column(0)

End Sub

Private Sub cboCarrier_AfterUpdate()
 If MsgBox("Changes have been made to this record." _
        & vbCrLf & vbCrLf & "Do you want to save these changes?" _
        , vbYesNo, "Changes Made...") = vbYes Then
        Me.cboProduct_ID = Null
Me.cboProduct_ID.Requery
  Me.cboProduct_ID = Me.cboProduct_ID.ItemData(0)
 
Me.Account.Requery
  Me.Account = Me.Account.ItemData(0)
        
        
            DoCmd.Save
        Else
            DoCmd.RunCommand acCmdUndo
    End If



  
  
End Sub
  





Private Sub Command28_Click()
On Error GoTo Err_Command28_Click
History.Visible = True
    
Exit_Command28_Click:
    Exit Sub

Err_Command28_Click:
    MsgBox Err.Description
    Resume Exit_Command28_Click
    
End Sub

Private Sub Command34_Click()
History.Visible = False
End Sub


Private Sub Check136_Click()
Cancelled_Date = Now()
End Sub


Private Sub Form_BeforeUpdate(Cancel As Integer)
'TimeStamp = Now()
End Sub
Private Sub List72_AfterUpdate()
    ' Find the record that matches the control.
    Dim rs As Object

    Set rs = Me.Recordset.Clone
    rs.FindFirst "[ID] = " & Str(Nz(Me![List72], 0))
    If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

Private Sub Form_Load()
    Me.Caption = "Postle Mobile Communication Database"

End Sub


Private Sub QuickList_AfterUpdate()
    ' Find the record that matches the control.
    Dim rs As Object

    Set rs = Me.Recordset.Clone
    rs.FindFirst "[System_ID] = " & Str(Nz(Me![QuickList], 0))
    If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub


Private Sub Toggle_SubForm_Click()
History.Visible = True
If History.Visible = True Then
    o


End Sub
Private Sub Billing_Subform_Button_Click()
On Error GoTo Err_Billing_Subform_Button_Click

    Dim stDocName As String
    Dim stLinkCriteria As String

    stDocName = "Billing subform"
    
    stLinkCriteria = "[Parent_ID]=" & Me![System_ID]
    
    
    DoCmd.OpenForm stDocName, , , stLinkCriteria
    

Exit_Billing_Subform_Button_Click:
    Exit Sub

Err_Billing_Subform_Button_Click:
    MsgBox Err.Description
    Resume Exit_Billing_Subform_Button_Click
    
End Sub


Private Sub cmdSortCol1_Click()
'* Set the listbox RowSource to a pre-defined SQL statement stored in a module constant.
'* See the form module declarations section for the module constants' definition.
If QuickList.RowSource = mcRowSourceSortCol1 Then
    '* Sort records in descending order
    QuickList.RowSource = mcRowSourceSortCol1Desc
Else
    '* Sort records in ascending order
    QuickList.RowSource = mcRowSourceSortCol1
End If
End Sub

Private Sub cmdSortCol2_Click()
'* Set the listbox RowSource to a pre-defined SQL statement stored in a module constant.
'* See the form module declarations section for the module constants' definition.
If QuickList.RowSource = mcRowSourceSortCol2 Then
    '* Sort records in descending order
    QuickList.RowSource = mcRowSourceSortCol2Desc
Else
    '* Sort records in ascending order
    QuickList.RowSource = mcRowSourceSortCol2
End If
End Sub

Private Sub cmdSortCol3_Click()
'* Set the listbox RowSource to a pre-defined SQL statement stored in a module constant.
'* See the form module declarations section for the module constants' definition.
If QuickList.RowSource = mcRowSourceSortCol3 Then
    '* Sort records in descending order
    QuickList.RowSource = mcRowSourceSortCol3Desc
Else
    '* Sort records in ascending order
    QuickList.RowSource = mcRowSourceSortCol3
End If
End Sub

Private Sub cmdSortCol4_Click()
'* Set the listbox RowSource to a pre-defined SQL statement stored in a module constant.
'* See the form module declarations section for the module constants' definition.
If QuickList.RowSource = mcRowSourceSortCol4 Then
    '* Sort records in descending order
    QuickList.RowSource = mcRowSourceSortCol4Desc
Else
    '* Sort records in ascending order
    QuickList.RowSource = mcRowSourceSortCol4
End If
End Sub

I'm quite confused. Can anyone help me to get these buttons to work? Thanks!
 
No need to go through all of that trouble. Just use:
Code: 
Private Sub YourCommandButton_Click()
   Me.OrderBy = "YourFieldName"
   Me.OrderByOn = True
End Sub
 
And if you want multiples, just put:

Code: 
   Me.OrderBy = "YourFirstField, YourSecondField"
   Me.OrderByOn = True

Or, if you want sorted descending:

Code: 
   Me.OrderBy = "YourFirstField DESC, YourSecondField"
   Me.OrderByOn = True
 
I tried this, and it did not reorder the listbox; it only populated the text boxes in the other part of the form with the first entry (based on whatever the button was sorting by) in the table that the form pulls from. However, the listbox pulls from a query; do I have to use something else in order to reorder the entries in the actual listbox?
 
Ah, okay. Well, it certainly did reorder the FORM! :) Perhaps you could shed some light on how to go about reordering the listbox?
 
Your initial RowSource is fine but the Order By should list the Field Name not the number.

Order By YourFieldName

and then,

QuickList.RowSource = TheConstantValue
Me.QuickList.Requery
 
And where does the following code go?

QuickList.RowSource = TheConstantValue
Me.QuickList.Requery
 
electrix said:
And where does the following code go?

QuickList.RowSource = TheConstantValue
Me.QuickList.Requery
Click to expand...

I thought you would be able to figure it out since I used your own items. So, here it is a little more explicitly:

Code: 
If QuickList.RowSource = mcRowSourceSortCol1 Then
    '* Sort records in descending order
    QuickList.RowSource = mcRowSourceSortCol1Desc
Else
    '* Sort records in ascending order
    QuickList.RowSource = mcRowSourceSortCol1
End If
[color=red][b]Me.QuickList.Requery[/b][/color]

End Sub
 
Okay, thank you. Now, each of the four buttons reorders the listbox by the appropriate field (one button for first name, one for last name, one for phone number and one for product id). However, the User_First_Name field and the Product_ID field still completely disappear from the listbox when any of the four buttons are clicked. In addition, I'm still getting the message: "Run-time error '13': Type mismatch" when I click on an entry in the listbox. Any ideas on how to solve this?
 
By the way, my code looks like this now:

Code: 
Option Compare Database
Option Explicit
'* Define the RowSource for the listbox, along with different sorted variations
Const mcRowSourceBasis = "SELECT DISTINCTROW User_First_Name, User_Last_Name, Phone_No FROM [Main]"
Const mcRowSourceUnsorted = mcRowSourceBasis & ";"
Const mcRowSourceSortCol1 = mcRowSourceBasis & " Order By User_First_Name;"
Const mcRowSourceSortCol1Desc = mcRowSourceBasis & " Order By User_First_Name Desc;"
Const mcRowSourceSortCol2 = mcRowSourceBasis & " Order By User_Last_Name;"
Const mcRowSourceSortCol2Desc = mcRowSourceBasis & " Order By User_Last_Name Desc;"
Const mcRowSourceSortCol3 = mcRowSourceBasis & " Order By Phone_No;"
Const mcRowSourceSortCol3Desc = mcRowSourceBasis & " Order By Phone_No Desc;"
Const mcRowSourceSortCol4 = mcRowSourceBasis & " Order By Product_ID;"
Const mcRowSourceSortCol4Desc = mcRowSourceBasis & " Order By Product_ID Desc;"

Private Sub Command27_Click()
On Error GoTo Err_Command27_Click

    Dim stDocName As String
    Dim stLinkCriteria As String

    stDocName = "History"
    
    stLinkCriteria = "[System_ID]=" & "'" & Me![System_ID] & "'"
    DoCmd.OpenForm stDocName, , , stLinkCriteria
History.Visible = False
Exit_Command27_Click:
    Exit Sub

Err_Command27_Click:
    MsgBox Err.Description
    Resume Exit_Command27_Click
    
End Sub


  
Private Sub Account_AfterUpdate()
Me.Account_Desc = Me.Account.Column(0)

End Sub

Private Sub cboCarrier_AfterUpdate()
 If MsgBox("Changes have been made to this record." _
        & vbCrLf & vbCrLf & "Do you want to save these changes?" _
        , vbYesNo, "Changes Made...") = vbYes Then
        Me.cboProduct_ID = Null
Me.cboProduct_ID.Requery
  Me.cboProduct_ID = Me.cboProduct_ID.ItemData(0)
 
Me.Account.Requery
  Me.Account = Me.Account.ItemData(0)
        
        
            DoCmd.Save
        Else
            DoCmd.RunCommand acCmdUndo
    End If



  
  
End Sub
  





Private Sub Command28_Click()
On Error GoTo Err_Command28_Click
History.Visible = True
    
Exit_Command28_Click:
    Exit Sub

Err_Command28_Click:
    MsgBox Err.Description
    Resume Exit_Command28_Click
    
End Sub

Private Sub Command34_Click()
History.Visible = False
End Sub


Private Sub Check136_Click()
Cancelled_Date = Now()
End Sub


Private Sub Form_BeforeUpdate(Cancel As Integer)
'TimeStamp = Now()
End Sub
Private Sub List72_AfterUpdate()
    ' Find the record that matches the control.
    Dim rs As Object

    Set rs = Me.Recordset.Clone
    rs.FindFirst "[ID] = " & Str(Nz(Me![List72], 0))
    If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

Private Sub Form_Load()
    Me.Caption = "Postle Mobile Communication Database"

End Sub


Private Sub QuickList_AfterUpdate()
    ' Find the record that matches the control.
    Dim rs As Object

    Set rs = Me.Recordset.Clone
    rs.FindFirst "[System_ID] = " & Str(Nz(Me![QuickList], 0))
    If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub


Private Sub Toggle_SubForm_Click()
History.Visible = True
If History.Visible = True Then
    o


End Sub
Private Sub Billing_Subform_Button_Click()
On Error GoTo Err_Billing_Subform_Button_Click

    Dim stDocName As String
    Dim stLinkCriteria As String

    stDocName = "Billing subform"
    
    stLinkCriteria = "[Parent_ID]=" & Me![System_ID]
    
    
    DoCmd.OpenForm stDocName, , , stLinkCriteria
    

Exit_Billing_Subform_Button_Click:
    Exit Sub

Err_Billing_Subform_Button_Click:
    MsgBox Err.Description
    Resume Exit_Billing_Subform_Button_Click
    
End Sub
Private Sub cmdSortCol1_Click()
'* Set the listbox RowSource to a pre-defined SQL statement stored in a module constant.
'* See the form module declarations section for the module constants' definition.
If QuickList.RowSource = mcRowSourceSortCol1 Then
    '* Sort records in descending order
    QuickList.RowSource = mcRowSourceSortCol1Desc
Else
    '* Sort records in ascending order
    QuickList.RowSource = mcRowSourceSortCol1
End If
Me.QuickList.Requery

End Sub

Private Sub cmdSortCol2_Click()
'* Set the listbox RowSource to a pre-defined SQL statement stored in a module constant.
'* See the form module declarations section for the module constants' definition.
If QuickList.RowSource = mcRowSourceSortCol2 Then
    '* Sort records in descending order
    QuickList.RowSource = mcRowSourceSortCol2Desc
Else
    '* Sort records in ascending order
    QuickList.RowSource = mcRowSourceSortCol2
End If
Me.QuickList.Requery

End Sub

Private Sub cmdSortCol3_Click()
'* Set the listbox RowSource to a pre-defined SQL statement stored in a module constant.
'* See the form module declarations section for the module constants' definition.
If QuickList.RowSource = mcRowSourceSortCol3 Then
    '* Sort records in descending order
    QuickList.RowSource = mcRowSourceSortCol3Desc
Else
    '* Sort records in ascending order
    QuickList.RowSource = mcRowSourceSortCol3
End If
Me.QuickList.Requery

End Sub

Private Sub cmdSortCol4_Click()
'* Set the listbox RowSource to a pre-defined SQL statement stored in a module constant.
'* See the form module declarations section for the module constants' definition.
If QuickList.RowSource = mcRowSourceSortCol4 Then
    '* Sort records in descending order
    QuickList.RowSource = mcRowSourceSortCol4Desc
Else
    '* Sort records in ascending order
    QuickList.RowSource = mcRowSourceSortCol4
End If
Me.QuickList.Requery

End Sub
 
You must log in or register to reply here.

Similar threads

N
Solved How to update a currentstock control in the subform after a requery from the parent form
Replies
16
Views
606
Mark_
M
T
Requering listbox records based on another listbox
Replies
10
Views
652
TimTDP
T
A
Ensure releasing/closing of a handle without losing err-state in case
Replies
5
Views
487
Josef P.
J
A
Solved Filtering (using VBA) a subform based on Option Buttons on main form
Replies
19
Views
628
CJ_London
C
mloucel
Solved Little Help with VBA..
Replies
7
Views
253
theDBguy
theDBguy

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom