Solved

MS Access 2003 VBA Combobox order by

Posted on 2013-01-11
12
843 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
  • 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 65

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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 

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 65

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 65

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

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Question about DB Schema 27 56
Access VBA, adding Progress Bar in code to allow execution. 7 31
Help with DoEvents 8 28
Cascading Combo boxes between 2 sub navigation forms 1 15
As with any other System Center product, the installation for the Authoring Tool can be quite a pain sometimes. This article serves to help you avoid making these mistakes and hopefully save you a ton of time on troubleshooting :)  Step 1: Make sur…
The System Center Operations Manager 2012, known as SCOM, is a part of the Microsoft system center product that provides the user with infrastructure monitoring and application performance monitoring. SCOM monitors:   Windows or UNIX/LinuxNetwo…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

821 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