1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Reordering a listbox in Access

Discussion in 'Business Applications' started by electrix, Apr 8, 2008.

Thread Status:
Not open for further replies.
Advertisement
  1. electrix

    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!
     
  2. OBP

    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.
     
  3. electrix

    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
    
     
  4. electrix

    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!
     
  5. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    Brilliant (y).
    I will keep this Thread in mind for future questions.
     
  6. Sponsor

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 733,556 other people just like you!

Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/701692

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice