MS Access 2003 VBA Combobox order by

I'm using the following VBA to populate my combobox.

The problem is that for whatever reason the stored procedure simply won't return the data sorted the way I want it.

Any way to do that in the VBA below?  It would be by ID desc

Private Sub cmbVisits_GotFocus()
    cmbVisits.RowSource = ""
    Dim cmd1 As New ADODB.Command
    Dim rst1 As ADODB.Recordset
    'Connect to db and run stored proc based on Client ID
    cmd1.ActiveConnection = CurrentProject.Connection
    cmd1.CommandType = adCmdStoredProc
    cmd1.CommandText = "usp_MarketingClientVisitsGet"
    cmd1.Parameters("@ClientID") = Me.txtClientID.Value

    'Set the recordset
    Set rst1 = cmd1.Execute()

    If rst1.RecordCount > 0 Then
            With Me.cmbVisits
            .RowSourceType = "Value List"
            .BoundColumn = 1
            .ColumnCount = 2
            .ColumnWidths = "0;1.5in"
            .ColumnHeads = False
        End With

        With rst1
            .MoveFirst
            Do Until .EOF
                cmbVisits.AddItem !ID & ";" & !AE
                .MoveNext
            Loop
        End With
    End If
End Sub

Open in new window

Larry Bristersr. DeveloperAsked:
Who is Participating?
 
Rey Obrero (Capricorn1)Connect With a Mentor Commented:
give this a try, in access  this format works
SELECT A.*
FROM
(
SELECT  [CVT].id ,
        [CVT].[Client ID] ,
        [CVT].[Contact ID] ,
        [CVT].requester ,
        [CVT].createdBy ,
        [CVT].createdDate ,
        [CVT].modifiedBy ,
        [CVT].modifiedDate ,
        [CVT].result ,
        [CVT].appointmentObtained ,
        [CVT].clientDeclined ,
        [CVT].departDate ,
        [CVT].returnDate ,
        [CVT].scheduleComment,
        [CVT].visitStatus,
        'P - ' + ISNULL(C.[Account Executive], 'NoAE') + '-' + [CVT].visitStatus + ' ' + CONVERT(varchar(10),ISNULL(departDate,createdDate),101) AE,
        CASE [CVT].visitStatus
        WHEN 'Cancelled' THEN 99
        ELSE 1 END sort,
        'Primary' vType       	        
FROM	dbo.ClientVisitTracking [CVT]
LEFT JOIN 
		Client C
	ON	CVT.[Client ID] = C.[Client ID]
LEFT JOIN 
		dbo.vw_MarkingContactList [MCL]
	ON	CVT.[Client ID] = MCL.[Client ID]
	AND	CVT.[Contact ID]= [MCL].[Contact ID]
WHERE	[CVT].[Client ID] = ISNULL(@ClientID,[CVT].[Client ID])
	AND	[CVT].id = ISNULL(@VisitID, [CVT].id)

	
UNION ALL
SELECT  [CVTN].VisitId id ,
        [CVTN].nearbyClientID [Client ID] ,
        [CVTN].nearbyContactID [Contact ID] ,
        [CVT].requester ,
        [CVTN].createdBy ,
        [CVTN].createdDate ,
        [CVT].modifiedBy ,
        [CVT].modifiedDate ,
        [CVT].result ,
        [CVT].appointmentObtained ,
        [CVT].clientDeclined ,
        [CVT].departDate ,
        [CVT].returnDate ,
        [CVT].scheduleComment,
        [CVTN].visitStatus,
        'N - ' + ISNULL(C.[Account Executive], 'NoAE') + '-' + [CVTN].visitStatus + ' ' + CONVERT(varchar(10),ISNULL([CVT].departDate,[CVT].createdDate),101) AE,
        CASE [CVTN].visitStatus
        WHEN 'Cancelled' THEN 99
        ELSE 1 END sort,
        'Nearby' vType       
FROM	dbo.ClientVisitTrackingNearBy [CVTN]
LEFT JOIN
		dbo.ClientVisitTracking	[CVT]
	ON	[CVTN].visitClientID = CVT.[Client ID]
	AND [CVTN].VisitId = CVT.id
LEFT JOIN 
		Client C
	ON	[CVTN].nearbyClientID = C.[Client ID]
LEFT JOIN 
		dbo.vw_MarkingContactList [MCL]
	ON	[CVTN].nearbyClientID = MCL.[Client ID]
	AND	[CVTN].nearbyContactID= [MCL].[Contact ID]
WHERE	[CVTN].nearbyClientID = ISNULL(@ClientID,[CVTN].nearbyClientID)
	AND	[CVTN].VisitId = ISNULL(@VisitID, [CVTN].VisitId)
) As A	
ORDER BY A.departDate desc
                                            

Open in new window

0
 
mbizupCommented:
I believe value lists are sorted as text.  Try formatting the ID field:

cmbVisits.AddItem Format(!ID,"000000")  & ";" & !AE
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Define 'sorted the way I want to', preferably with an example.

If you want the SP to return sorted data, then you have to include an ORDER BY statement in the SP's final SELECT statement that returns the data.
0
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

 
mbizupCommented:
Hmmm -  Ignore my comment,  Formatting the data is not going to help unless you are ordering it as Jim is describing.
0
 
Larry Bristersr. DeveloperAuthor Commented:
JimHorn,
I'm doing that...but for some reason it simply isn't sorting it in the stored procedure.

This is what gets returned on the SQL below (showing departColumn only from results)

departDate
2012-11-27 00:00:00.000
2012-11-27 00:00:00.000
2013-01-07 00:00:00.000

SQL follows
SELECT  [CVT].id ,
        [CVT].[Client ID] ,
        [CVT].[Contact ID] ,
        [CVT].requester ,
        [CVT].createdBy ,
        [CVT].createdDate ,
        [CVT].modifiedBy ,
        [CVT].modifiedDate ,
        [CVT].result ,
        [CVT].appointmentObtained ,
        [CVT].clientDeclined ,
        [CVT].departDate ,
        [CVT].returnDate ,
        [CVT].scheduleComment,
        [CVT].visitStatus,
        'P - ' + ISNULL(C.[Account Executive], 'NoAE') + '-' + [CVT].visitStatus + ' ' + CONVERT(varchar(10),ISNULL(departDate,createdDate),101) AE,
        CASE [CVT].visitStatus
        WHEN 'Cancelled' THEN 99
        ELSE 1 END sort,
        'Primary' vType       	        
FROM	dbo.ClientVisitTracking [CVT]
LEFT JOIN 
		Client C
	ON	CVT.[Client ID] = C.[Client ID]
LEFT JOIN 
		dbo.vw_MarkingContactList [MCL]
	ON	CVT.[Client ID] = MCL.[Client ID]
	AND	CVT.[Contact ID]= [MCL].[Contact ID]
WHERE	[CVT].[Client ID] = ISNULL(@ClientID,[CVT].[Client ID])
	AND	[CVT].id = ISNULL(@VisitID, [CVT].id)

	
UNION ALL
SELECT  [CVTN].VisitId id ,
        [CVTN].nearbyClientID [Client ID] ,
        [CVTN].nearbyContactID [Contact ID] ,
        [CVT].requester ,
        [CVTN].createdBy ,
        [CVTN].createdDate ,
        [CVT].modifiedBy ,
        [CVT].modifiedDate ,
        [CVT].result ,
        [CVT].appointmentObtained ,
        [CVT].clientDeclined ,
        [CVT].departDate ,
        [CVT].returnDate ,
        [CVT].scheduleComment,
        [CVTN].visitStatus,
        'N - ' + ISNULL(C.[Account Executive], 'NoAE') + '-' + [CVTN].visitStatus + ' ' + CONVERT(varchar(10),ISNULL([CVT].departDate,[CVT].createdDate),101) AE,
        CASE [CVTN].visitStatus
        WHEN 'Cancelled' THEN 99
        ELSE 1 END sort,
        'Nearby' vType       
FROM	dbo.ClientVisitTrackingNearBy [CVTN]
LEFT JOIN
		dbo.ClientVisitTracking	[CVT]
	ON	[CVTN].visitClientID = CVT.[Client ID]
	AND [CVTN].VisitId = CVT.id
LEFT JOIN 
		Client C
	ON	[CVTN].nearbyClientID = C.[Client ID]
LEFT JOIN 
		dbo.vw_MarkingContactList [MCL]
	ON	[CVTN].nearbyClientID = MCL.[Client ID]
	AND	[CVTN].nearbyContactID= [MCL].[Contact ID]
WHERE	[CVTN].nearbyClientID = ISNULL(@ClientID,[CVTN].nearbyClientID)
	AND	[CVTN].VisitId = ISNULL(@VisitID, [CVTN].VisitId)
	
ORDER BY CVT.departDate desc

Open in new window

0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
UNION queries don't sort the entire set.  Give this a whirl (obviously air code)

SELECT goo, foo, boo, DepartDate, 1 as sort_order
FROM SomeTable
UNION ALL
SELECT goo, foo, boo, DepartDate, 2 as sort_order
FROM SomeOtherTable
ORDER BY sort_order, depart_date
0
 
Larry Bristersr. DeveloperAuthor Commented:
JimHorn,
  Yeah...that didn't work.  

SHould I maybe place all that data into a table variable (once for each select instead of a union) and then do my select and sort?
0
 
Larry Bristersr. DeveloperAuthor Commented:
That did it.
Thanks guys...should have figured that myself.
0
 
mbizupCommented:
I might have missed something, but isn't that what Jim suggested?
0
 
Larry Bristersr. DeveloperAuthor Commented:
mbizup
Wasn;t wrapped with the Select * from (


) as A
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
lbrister threw the whole SQL into a subquery, then did an ORDER BY off of the subquery results, which also works as long as you don't need to sort by parts of the UNION first.  That'll work in this case.
0
 
Larry Bristersr. DeveloperAuthor Commented:
Hey guys...
I'm going to post a question into the SQL area addressing pros and cons of unions, with cte, table variables etc...sometime this weekend.

I can create pretty complex selects in functions and stored procedures, but since I'm not a school trained guy I don't necessarily know the best way out when I run into something like getting the sort to work in a union statement like in this question.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.