Reorder a listbox in a form

[electrix]

I am trying to reorder a four-column listbox in a form. The four columns in the listbox are User_First_Name, User_Last_Name, Phone_No, Product_ID. I'm trying to create four command buttons--each assigned to a column--that when clicked will reorder the entries in the listbox by that column.

Currently, before using any of the buttons, when an entry is highlighted in the listbox, it populates corresponding text fields on the other side of the form.

The all four buttons reorder the listbox by their respective column. However, they create two problems when clicked. First, clicking any of the four buttons makes the User_First_Name and the Product_ID columns completely dissappear from the listbox. However, the entries in the listbox can still be sorted by these columns. But they dissappear, leaving only the User_Last_Name and Phone_No columns.

Second, after clicking one of the command buttons to reorder the listbox, highlighting an entry in the listbox results in the following error:

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

Thus, they no longer populate the text fields on the other side of the form. Can anyone help me fix these two problems? Here's my code for the form:


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

[Trevor]

can you post your DB (with any sensitive info removed)? I think it would help to see what is going on.
thank you.

[ben.oates]

Product_ID is disappearing because you haven't included it in your mcRowSourceBasis SQL. The type mismatch will possibly be a consequence of this as the data you are trying to assign to the text box doesn't exist. I can't explain why User_First_Name is disappearing at the moment. Please put a break point in one of your button clicks and check the SQL being passed very carefully. Post it back here if you still need help.