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?
1 4635
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?
Hello there, Kingkev83!
You can Order By more than one field, that should fields you need. Try it!
Dököll
Sign in to post your reply or Sign up for a free account.
Similar topics
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."
...
|
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...
|
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...
|
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...
|
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"...
|
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...
|
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...
|
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...
|
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...
|
by: ryjfgjl |
last post by:
ExcelToDatabase: batch import excel into database automatically...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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)...
|
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...
|
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...
| |