Solved

MS Access 2003 VBA Combobox order by

Posted on 2013-01-11
12
879 Views
Last Modified: 2013-01-11
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

0
Comment
Question by:lrbrister
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
  • 3
  • +1
12 Comments
 
LVL 61

Expert Comment

by:mbizup
ID: 38767867
I believe value lists are sorted as text.  Try formatting the ID field:

cmbVisits.AddItem Format(!ID,"000000")  & ";" & !AE
0
 
LVL 66

Expert Comment

by:Jim Horn
ID: 38767868
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
 
LVL 61

Expert Comment

by:mbizup
ID: 38767895
Hmmm -  Ignore my comment,  Formatting the data is not going to help unless you are ordering it as Jim is describing.
0
Database Solutions Engineer FAQs

In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller single-server environments.

 

Author Comment

by:lrbrister
ID: 38767961
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
 
LVL 66

Expert Comment

by:Jim Horn
ID: 38767977
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
 

Author Comment

by:lrbrister
ID: 38767996
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
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 500 total points
ID: 38768028
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
 

Author Closing Comment

by:lrbrister
ID: 38768043
That did it.
Thanks guys...should have figured that myself.
0
 
LVL 61

Expert Comment

by:mbizup
ID: 38768054
I might have missed something, but isn't that what Jim suggested?
0
 

Author Comment

by:lrbrister
ID: 38768060
mbizup
Wasn;t wrapped with the Select * from (


) as A
0
 
LVL 66

Expert Comment

by:Jim Horn
ID: 38768101
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
 

Author Comment

by:lrbrister
ID: 38768126
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

Featured Post

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Having trouble getting your hands on Dynamics 365 Field Service or Project Service trial? Worry No More!!!
Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…

627 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question