Reordering a listbox in Access

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

electrix

Thread Starter
Joined
Jun 27, 2007
Messages
40
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.

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 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
I'd like to get rid of that error and also stop causing the Phone_No and Product_ID fields to disappear. Thanks for the help!
 

OBP

Joined
Mar 8, 2005
Messages
19,895
You are using very advanced VBA in your code and I doubt if there is anyone on this Forum with better knowledge than you are showing. (y)
There may be someone on the VBAX forum.
The Run time error that you are getting is usually associated with Using the "find" VB function in the afterupdate event of the list to locate the correct data, this quite often occurs when you switch from using an Autonumber ID field to a text field.
Are you using a "List Box" or a "Combo Box"?
If it is a Combo Box, you could just have 4 Combo boxes instead of a Combo and 4 Buttons, I know it probably does not appear to be as elegant.
Or you could use the VBA to reset the SQL of the Combo box to sort by the other columns.

I would be very interested if you find a Fix for future reference.
 

electrix

Thread Starter
Joined
Jun 27, 2007
Messages
40
Thanks, I'll check out the VBAX forum. Also, I just saw that I must have copied my code incorrectly; the above sample is not complete. Here it is:

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
 

electrix

Thread Starter
Joined
Jun 27, 2007
Messages
40
Okay sir, I figured it out. The listbox was drawing from a query that contained five fields, one of which was hidden from the listbox. I had a couple of problems with my constant declarations at the top of my code, specifically this line:

Code:
Const mcRowSourceBasis = "SELECT DISTINCTROW User_First_Name, User_Last_Name, Phone_No FROM [Main]"
1. I didn't even have Product_ID included in that list. Once I threw it in at the end, I got three fields to show up instead of two: User_Last_Name, Phone_No, and Product_ID. I now had this:

Code:
Const mcRowSourceBasis = "SELECT DISTINCTROW User_First_Name, User_Last_Name, Phone_No, Product_ID FROM [Main]"
2. I was pulling these columns from a table called "Main" while the listbox was pulling from a query named "query2." I changed "Main" to "query2" in this line. I then had this:

Code:
Const mcRowSourceBasis = "SELECT DISTINCTROW User_First_Name, User_Last_Name, Phone_No, Product_ID FROM [query2]"
3. I still couldn't manage to get that User_First_Name column to show up on the listbox after clicking one of the buttons. A little tinkering (switching User_First_Name and User_Last_Name in that line) revealed that for some reason the listbox was hiding the first column specified in that line--putting User_Last_Name in front of User_First_Name in that line of code made User_Last_Name dissappear from the listbox and User_First_Name appear. In query2, there are five fields, the additional one being System_ID, which is hidden in the listbox. So, I added System_ID to the line of code; now I have this:

Code:
Const mcRowSourceBasis = "SELECT DISTINCTROW System_ID, User_First_Name, User_Last_Name, Phone_No, Product_ID FROM [query2]"
That did it. Now, all four columns appear in the listbox and are sortable. The entries also populate the text boxes on the other side of the form when clicked. Problem solved!
 

OBP

Joined
Mar 8, 2005
Messages
19,895
Brilliant (y).
I will keep this Thread in mind for future questions.
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 807,865 other people just like you!

Latest posts

Members online

Top