473,320 Members | 1,829 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Combo box sort problems

I have a problem, i use a combo box to display members details in the following format
display member: (concatinated field) Surname &' '& Forename &' '& MembershipNo & - & DateofBirth as `Memberdetails`
Valuemember : MembershipNo

I would like to sort the combo box by surname/MemberDetails but it currently only sorts by membershipNo. I have tried the sorted = true value which does sort by surname but does not sort the Valuemember: membershipNo also so i get a member with an incorrect membershipNo.

i use the following code to populate the combo box:

Common.vb
Public Function PopulateCombo(ByVal cboBox As ComboBox, ByVal psSQL As String, ByVal IsTrue As Boolean, ByVal ErrorText As String, ByVal Value As String, ByVal Display As String, Optional ByVal Othertext As String = "") As DataSet
Dim SelectCmd As Odbc.OdbcCommand
SelectCmd = New System.Data.Odbc.OdbcCommand
SelectCmd.Connection = gThisConn
If IsTrue Then
If Othertext = "" Then
SelectCmd.CommandText = "(Select -1 as `" & Value & "`, ' ' as `" & Display & "` from member) Union (" & psSQL & ")"
Else
SelectCmd.CommandText = "(Select -1 as `" & Value & "`, '--All-- ' as `" & Display & "`) Union All (" & psSQL & ")"
End If

Else
SelectCmd.CommandText = psSQL
End If


Dim thisDA As Odbc.OdbcDataAdapter
thisDA = New System.Data.Odbc.OdbcDataAdapter
thisDA.SelectCommand = SelectCmd

Try
Dim thisDs As DataSet
thisDs = New DataSet

thisDA.Fill(thisDs)
cboBox.DataSource = thisDs.Tables(0)
cboBox.ValueMember = thisDs.Tables(0).Columns(0).ToString
cboBox.DisplayMember = thisDs.Tables(0).Columns(1).ToString
Catch Ex As Exception
MessageBox.Show("Error Populating " & ErrorText & " - " & Ex.Message)
Finally
SelectCmd.Dispose()
thisDA.Dispose()
End Try
Return Nothing
End Function

I use a global string for PsSQL:
'Public Const gpopcboMemberSearch As String = "Select MembershipNo, ([Surname] &' '&[forename] &' - '& [MembershipNo] & ' - ' & [DateOfBirth]) as `MemberDetails` from [Member] Where [Active] = True Order by [Surname], [Forename] "

this code calls the populate combo code above to populate the combobox

PopulateCombo(cboMemberSearch, gpopcboMemberSearch & " Order By Surname", True, "MemberSelect", "MembershipNo", "Surname")


i need to sort ASC by surname then forename and the relevent Valuemember: memberhsipNo needs to correspond with the member selected.

can anyone help solve my Problem?
May 25 '07 #1
1 4635
Dököll
2,364 Expert 2GB
I have a problem, i use a combo box to display members details in the following format
display member: (concatinated field) Surname &' '& Forename &' '& MembershipNo & - & DateofBirth as `Memberdetails`
Valuemember : MembershipNo

I would like to sort the combo box by surname/MemberDetails but it currently only sorts by membershipNo. I have tried the sorted = true value which does sort by surname but does not sort the Valuemember: membershipNo also so i get a member with an incorrect membershipNo.

i use the following code to populate the combo box:

Common.vb
Expand|Select|Wrap|Line Numbers
  1.  Public Function PopulateCombo(ByVal cboBox As ComboBox, ByVal psSQL As String, ByVal IsTrue As Boolean, ByVal ErrorText As String, ByVal Value As String, ByVal Display As String, Optional ByVal Othertext As String = "") As DataSet
  2.         Dim SelectCmd As Odbc.OdbcCommand
  3.         SelectCmd = New System.Data.Odbc.OdbcCommand
  4.         SelectCmd.Connection = gThisConn
  5.         If IsTrue Then
  6.             If Othertext = "" Then
  7.                 SelectCmd.CommandText = "(Select -1 as `" & Value & "`, '                                                  ' as `" & Display & "` from member) Union (" & psSQL & ")"
  8.             Else
  9.                 SelectCmd.CommandText = "(Select -1 as `" & Value & "`, '--All--                                           ' as `" & Display & "`) Union All (" & psSQL & ")"
  10.             End If
  11.  
  12.         Else
  13.             SelectCmd.CommandText = psSQL
  14.         End If
  15.  
  16.  
  17.         Dim thisDA As Odbc.OdbcDataAdapter
  18.         thisDA = New System.Data.Odbc.OdbcDataAdapter
  19.         thisDA.SelectCommand = SelectCmd
  20.  
  21.         Try
  22.             Dim thisDs As DataSet
  23.             thisDs = New DataSet
  24.  
  25.             thisDA.Fill(thisDs)
  26.             cboBox.DataSource = thisDs.Tables(0)
  27.             cboBox.ValueMember = thisDs.Tables(0).Columns(0).ToString
  28.             cboBox.DisplayMember = thisDs.Tables(0).Columns(1).ToString
  29.         Catch Ex As Exception
  30.             MessageBox.Show("Error Populating " & ErrorText & " - " & Ex.Message)
  31.         Finally
  32.             SelectCmd.Dispose()
  33.             thisDA.Dispose()
  34.         End Try
  35.         Return Nothing
  36.     End Function
  37.  
I use a global string for PsSQL:

Expand|Select|Wrap|Line Numbers
  1. 'Public Const gpopcboMemberSearch As String = "Select MembershipNo, ([Surname] &' '&[forename] &' - '& [MembershipNo] & ' - ' & [DateOfBirth]) as `MemberDetails` from [Member] Where [Active] = True Order by [Surname], [Forename] "
  2.  
  3. this code calls the populate combo code above to populate the combobox
  4.  
  5. PopulateCombo(cboMemberSearch, gpopcboMemberSearch & " Order By Surname", True, "MemberSelect", "MembershipNo", "Surname")
  6.  
  7.  
i need to sort ASC by surname then forename and the relevent Valuemember: memberhsipNo needs to correspond with the member selected.

can anyone help solve my Problem?
Hello there, Kingkev83!

You can Order By more than one field, that should fields you need. Try it!

Dököll
May 31 '07 #2

Sign in to post your reply or Sign up for a free account.

Similar topics

6
by: raj | last post by:
When I try to sort my combo box I get the following error. "Cannot sort a ComboBox that has a DataSource set. Sort the data using the underlying data model." ...
5
by: Marnie Parker | last post by:
I am adding a drop down comb box to an existing database I wrote about a year ago. This combo box list names where the user can select which record to go to. Sigh. Last, First Name and a...
2
by: Aravind | last post by:
Hi folks. I'm doing a library system which contains a table caled "Member", which in turn contains an ID key and the member's name. I have a combo box which allows the user to pick a name from...
2
by: Todd | last post by:
Hi. I want to sort the records on my form (using either a continuous form or a datasheet) by the unbound "description" column in a combo box on the form (or in the datasheet.) Here's a rough...
3
by: hmiller | last post by:
Hey everyone, I am having a hell of a time trying to set this menu system up. Here's what I'm trying to do. Combo Box One; is populated by names under properties "row source" "Phase 1"...
14
by: Kevin | last post by:
A couple of easy questions here hopefully. I've been working on two different database projects which make use of multiple forms. 1. Where's the best/recommended placement for command buttons...
7
by: Arnold | last post by:
Greetings Gurus, In a mainform's header, I have a combobox named comboStudents. The rowsource for this combobox is: SELECT -999 As StudentID, "<Add New Student>" As FullName, "aaa" As...
7
by: Amanda | last post by:
User is going to be transferring items out of combobox to listbox and vice versa keeping ascending and desending order respectively at all time. If user selects the last item in combobox, the...
3
by: RZ15 | last post by:
Hi guys, I have made a form with a combo box listing fields that a user can sort by. I have a code that works for this when i have 4 levels to sort by. It is the following: Private Sub...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.