We help IT Professionals succeed at work.

How to sort columns on a continuous form populated by resetting the recordet, as when a recordset is returned from a stored procedure?

190 Views
Last Modified: 2017-05-15
I have been programming in Access, always with an Access backend, for many years and have used the same technique to allow the user to sort a particular column on a continuous form.  I use this routine that uses the Order By property of the form to do this.
Private Function setColumnOrder(passedFieldName As String)

gBogusStr = Me.OrderBy
'
If Me.OrderBy = passedFieldName & " DESC" Or _
   Me.OrderBy = "[" & passedFieldName & "] DESC" Then
    Me.OrderBy = passedFieldName
Else
    Me.OrderBy = passedFieldName & " DESC"
End If
'
Me.OrderByOn = True
'
End Function

Open in new window



On my continuous forms the user can sort the contents of any column by clicking on the column header, Clicking once will sort the contents of the column and all records ascending and clicking again will click sort the contents of the column descending, again ascending etc..

Prior to using a SQL backend I always revised the recordsource of the form using something like
Me.recordsource = “Select …. From tbl…  Where….”
Now I am using Access 2013 with a SQL Server backend.  I am populating my continuous form with a recordset returned from a stored procedure.
Now instead of revising the recordsource the logic sets the recordset
using 'Set Me.Recordset = rs '.

This difference
In this case the difference being the statement
‘Set Me.Recordset = rs’
instead of
‘Me.recordsource = “Select …. From tbl…  Where….”

Populating the form works great and is very fast but after executing ‘Set Me.Recordset = rs’ .  my procedure for ordering a column does not work.
When using the above ‘SetColumnOrder’ routine, I get a runtime error ‘31’, ‘Data provider could not be initialized’ when I click on any column heading.  Any idea why that is happening?  Does the ‘Order By’ property of the form not work when it is populated resetting the recordset?
This isn't specific to the fact that it is a recordset returned from a SQL SPROC.  It seems like any time the '‘Set Me.Recordset = rs’  is used to chnage the forms recordset my ordering routine kicks the error.

Any idea how this can be resolved?

Is there a way to get the column re-ordering functionality to work when the continuous form is populated by resetting the recordset?
Comment
Watch Question

CERTIFIED EXPERT
Distinguished Expert 2017
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
Scott McDaniel (EE MVE )Infotrakker Software
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2014
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

Commented:
Thanks you both for your input.  You have both offered advice and answers to me in the past and I have much respect for your responses.

From your response I have the answer to my question 'Why won't the old sort logic work?', Answer: Because the form is no longer bound after re-setting the recrodset. Correct?

Beyond that I agree with your other suggestions.  I will revise the SPROC to take a parameter to override the default sort order, which will be a new question.

Some background on the reason for question:
I have been developing applications for decades but this is the first with a SQL backend.  I have no idea what to expect in terms of response times.  I have been given so much, sometimes conflicting, advice on using MS Access with a SQL backend.  I have created many views over the date and linked them, as well as the SQL tables to the Access front end.  At this point the forms that I use to update data are all bound using the linked SQL tables.

I will not be installing or ‘tuning’ SQL Server.  I will be creating all the components, SPROCS, tables, views stored procedures, indexes etc.. I have also been advised that the performance of SQL is very dependent on the how the administrator configures the DB.  I don’t even know what that entails but I want to be prepared in case the administrator does not have an optimal configuration or for any reason the response is slow.

That’s the reason I am creating this form, using a SPROC, to query and return the data specified by the user.  I want to make sure I can do this, if it becomes necessary.

I already have a version of this form already set up in the standard way, revising the recordsource, not the recordset.  It works perfectly.

This is the routine that calls the SPROC and set the recordset:
Private Sub DoaSPROCRequery(Optional passedPropertyID As Variant = Null, _
                            Optional passedTaxAuthorityID As Variant = Null, _
                            Optional passedMuniCode As Variant = Null, _
                            Optional passedPayee As Variant = Null, _
                            Optional passedLotBlock As Variant = Null, _
                            Optional passedFromPayDate As Variant = Null, _
                            Optional passedThruPayDate As Variant = Null, _
                            Optional passedCheckNum As Variant = Null, _
                            Optional passedVoucherNum As Variant = Null, _
                            Optional passedTAReceipt As Variant = Null, _
                            Optional passedFromDepositDate As Variant = Null, _
                            Optional passedThruDepositDate As Variant = Null)


'
'
gConnection = "ODBC;Description=JTS Data;DRIVER=SQL Server;SERVER=MIKE73-PC\WIN73SQLSERVER;Trusted_Connection=Yes;APP=Microsoft® Windows® Operating System;DATABASE=JTSConversion"
'gConnection = "ODBC;Description=JTS Data;DRIVER=SQLNCLI;SERVER=MIKE73-PC\WIN73SQLSERVER;Trusted_Connection=Yes;APP=Microsoft® Windows® Operating System;DATABASE=JTSConversion"
'
Dim rs As ADODB.Recordset
Dim cmd As ADODB.Command
Set cmd = New ADODB.Command

With cmd
    .CommandText = "sptblPaymentsPreSelect"
    .CommandType = adCmdStoredProc
    setSQLConnection
    .ActiveConnection = gConnection
    '
    ' Input Params
    '
    .Parameters.Append .CreateParameter("passedPropertyID", adBigInt, adParamInput, , passedPropertyID)
    '
    .Parameters.Append .CreateParameter("passedTaxAuthorityID", adBigInt, adParamInput, , passedTaxAuthorityID)
    '
    .Parameters.Append .CreateParameter("passedMuniCode", adBigInt, adParamInput, , passedMuniCode)
    '
    .Parameters.Append .CreateParameter("passedPayee", adVarChar, adParamInput, 30, passedPayee)
    '
    .Parameters.Append .CreateParameter("passedLotBlock", adVarChar, adParamInput, 30, passedLotBlock)
    '
    .Parameters.Append .CreateParameter("passedFromPayDate", adDBTimeStamp, adParamInput, , passedFromPayDate) 'aDBTime, aDBDate, adDBTimeStamp, adDate
    '
    .Parameters.Append .CreateParameter("passedThruPayDate", adDBTimeStamp, adParamInput, , passedThruPayDate)
    '''
    .Parameters.Append .CreateParameter("passedCheckNum", adVarChar, adParamInput, 50, passedCheckNum)
    '
    .Parameters.Append .CreateParameter("passedVoucherNum", adVarChar, adParamInput, 6, passedVoucherNum)
    '
    .Parameters.Append .CreateParameter("passedTAReceipt", adVarChar, adParamInput, 20, passedTAReceipt)
    '
    .Parameters.Append .CreateParameter("passedFromDepositDate", adDBTimeStamp, adParamInput, , passedFromDepositDate) 'aDBTime, aDBDate, adDBTimeStamp, adDate
    '
    .Parameters.Append .CreateParameter("passedThruDepositDate", adDBTimeStamp, adParamInput, , passedThruDepositDate)
    
    Set rs = New ADODB.Recordset
        With rs
            .CursorLocation = adUseClient
            .CursorType = adOpenStatic
            .LockType = adLockReadOnly
            .Open cmd
        End With
        '
    
    Set Me.Recordset = rs    'rsOut
    '
    Set .ActiveConnection = Nothing
    '
End With
'
Set cmd = Nothing
End Sub

Open in new window

CERTIFIED EXPERT
Distinguished Expert 2017
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

Commented:
Thank you both for you input.  It is much appreciated
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.