Solved

MS Access 2003 VBA Combobox order by

Posted on 2013-01-11
12
808 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
 

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
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 
LVL 119

Accepted Solution

by:
Rey Obrero 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

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
We were having a lot of "Heartbeat Alerts" in our SCOM environment, now "Heartbeat" in a SCOM environment for those of you who might not be familiar with SCOM is a packet of data sent from the agent to the management server on a regular basis, basic…
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…

747 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

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now