Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

MS Access 2003 VBA Combobox order by

Posted on 2013-01-11
12
Medium Priority
?
897 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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 

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 2000 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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
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…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
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…

688 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