Can't Set row source at runtime

Juan Velasquez
Juan Velasquez used Ask the Experts™
on
Hello,

I have a form that is populated via a stored procedure.  This stored procedure returns a recordset which populates the form.  However, when I attempt to clear the filter via a command button which set the forms recordsource to "" via me.recordsource = "", I get a data initialization error.  I've never run into this before
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
ramromconsultant

Commented:
What is the error number and description?
Mike EghtebasDatabase and Application Developer

Commented:
to remove the filter:

DoCmd.FilterOn=False
Mike EghtebasDatabase and Application Developer

Commented:
depending where you use it:

Me.FilterOn=False

Author

Commented:
I'll try it tomorrow when I go to work
Scott McDaniel (EE MVE )Infotrakker Software
Most Valuable Expert 2012
Top Expert 2014

Commented:
As ramron said: What's the error? Anytime you encounter an error and need help with it, you should post the exact error number and message.

Are you actually setting the Filter at some point? If not, then clearing it won't really help. When you clear the Recordsource, you remove all records from the form, not the Filter.

Can you show how you're populating the Form? That is, how you build and set set the Recordset?

Author

Commented:
Hello LMConsulting,

I'll let you know as soon as I get to work this morning.

Author

Commented:
The error is
Error Number: 31
Error Description:  Data Provider Could Not Be initialized.  
The above error appears when I attempt to reset the form record source to a select statement via a command button

-------THIS IS WHERE THE ERROR OCCURS

    Me.RecordSource = "SELECT dbo_vwPivotedReviewsRevised.USI, dbo_vwPivotedReviewsRevised.WorkStream, dbo_vwPivotedReviewsRevised.GFP, " & _
                   "dbo_vwPivotedReviewsRevised.Review1 , dbo_vwPivotedReviewsRevised.Status1, " & _
                   "dbo_vwPivotedReviewsRevised.Review2 , dbo_vwPivotedReviewsRevised.Status2, " & _
                   "dbo_vwPivotedReviewsRevised.Review3 , dbo_vwPivotedReviewsRevised.Status3, " & _
                   "dbo_vwPivotedReviewsRevised.Review4 , dbo_vwPivotedReviewsRevised.Status4, " & _
                   "dbo_vwPivotedReviewsRevised.Review5 , dbo_vwPivotedReviewsRevised.Status5, " & _
                   "dbo_vwPivotedReviewsRevised.Review6 , dbo_vwPivotedReviewsRevised.Status6, " & _
                   "dbo_vwPivotedReviewsRevised.Review7 , dbo_vwPivotedReviewsRevised.Status7, " & _
                   "dbo_vwPivotedReviewsRevised.Review8 , dbo_vwPivotedReviewsRevised.Status8 " & _
                   "FROM dbo_vwPivotedReviewsRevised "
    Me.Requery
    ClearComboBoxes
    Call ClearList(Me.lstSelectReview)
    Call ClearList(Me.lstSelectStatus)

Author

Commented:
Below is the code I use to populate form via a stored procedure executed via a command button.  It uses multiple selections from two list boxes to create two different comma delimited strings which are passed to the stored procedure which then returns the resulting dataset

Private Sub ApplyReviewStatusFilter(strSelectedReviewData As String, strSelectedStatusData As String)
Dim objCmd As ADODB.Command
Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset
Dim prm As ADODB.Parameter



Set cnn = New ADODB.Connection

cnn.ConnectionString = cStrOLEDBConnectionString
cnn.ConnectionTimeout = 0
cnn.Open
Dim x As Integer

Set objCmd = New ADODB.Command
cnn.CursorLocation = adUseClient
 Set rst = New ADODB.Recordset
        rst.CursorLocation = adUseClient
        rst.CursorType = adOpenDynamic
    With objCmd
        .ActiveConnection = cnn
        .CommandText = "[dbo].[spRetrieveByReviewOrStatus]"
        .CommandType = adCmdStoredProc
        Set prm = .CreateParameter("pReview", adVarChar, adParamInput, 500, strSelectedReviewData)
       .Parameters.Append prm
        Set prm = .CreateParameter("pStatus", adVarChar, adParamInput, 500, strSelectedStatusData) '
        .Parameters.Append prm
        Set rst = .Execute
        .Parameters.Delete ("pReview")
        .Parameters.Delete ("pStatus")
    End With
       
        'MsgBox rst.RecordCount
   Set Me.Recordset = rst
 
   
 
    rst.Close
    cnn.Close
    Set rst = Nothing
    Set cnn = Nothing
    Set objCmd = Nothing
Scott McDaniel (EE MVE )Infotrakker Software
Most Valuable Expert 2012
Top Expert 2014

Commented:
In many cases your error indicates a poorly formed connection string, or a problem with the Provider installed on the machine (i.e. a corrupt driver or connection libarary, perhaps).

If you move this to a different machine, do you get the same error? If you do, then your connection string would be suspect. If not, and if you can connect properly from the other machine, then I'd suspect a problem with the driver/provider.

What's the value in cStrOLEDBConnectionString

Author

Commented:
Hello LM,
You beat me to it.  I did some more research and was just going to provide the connection string.  Here it is

Public Const cStrOLEDBConnectionString As String = "Provider=SQLOLEDB;Data Source=red2;Initial Catalog=Unicorn;Uid=rtsiob;Pwd=tbontbth>"
Scott McDaniel (EE MVE )Infotrakker Software
Most Valuable Expert 2012
Top Expert 2014

Commented:
The connection string looks okay, but that doesn't mean it will work.

Can you try building a new procedure that does nothing more than opens a connection using that string? That would let us know if the string works, and we can eliminate that.

Author

Commented:
I went ahead and built the following procedure and called it via a command button on the form.  It returned Test 1 where 1 is the state of the connection which means that the connection was open.  This makes sense as the form does get populated.  It is only when I attempt to set the record source afterwards that I get the error

Public Sub TestConnection()



Dim cnn As ADODB.Connection

Set cnn = New ADODB.Connection

cnn.ConnectionString = cStrOLEDBConnectionString

cnn.ConnectionTimeout = 0
cnn.Open
MsgBox "Test " & cnn.State
End Sub
Scott McDaniel (EE MVE )Infotrakker Software
Most Valuable Expert 2012
Top Expert 2014

Commented:
Let's step back for a moment - how are you "setting the recordsource afterwards"?

Author

Commented:
I have a button that is called clear filter results.  The purpose of the button is to set the form row source so that it displays all records via the following code - this is where the error occurrs
 Me.RecordSource = "SELECT dbo_vwPivotedReviewsRevised.USI, dbo_vwPivotedReviewsRevised.WorkStream, dbo_vwPivotedReviewsRevised.GFP, " & _
                   "dbo_vwPivotedReviewsRevised.Review1 , dbo_vwPivotedReviewsRevised.Status1, " & _
                   "dbo_vwPivotedReviewsRevised.Review2 , dbo_vwPivotedReviewsRevised.Status2, " & _
                   "dbo_vwPivotedReviewsRevised.Review3 , dbo_vwPivotedReviewsRevised.Status3, " & _
                   "dbo_vwPivotedReviewsRevised.Review4 , dbo_vwPivotedReviewsRevised.Status4, " & _
                   "dbo_vwPivotedReviewsRevised.Review5 , dbo_vwPivotedReviewsRevised.Status5, " & _
                   "dbo_vwPivotedReviewsRevised.Review6 , dbo_vwPivotedReviewsRevised.Status6, " & _
                   "dbo_vwPivotedReviewsRevised.Review7 , dbo_vwPivotedReviewsRevised.Status7, " & _
                   "dbo_vwPivotedReviewsRevised.Review8 , dbo_vwPivotedReviewsRevised.Status8 " & _
                   "FROM dbo_vwPivotedReviewsRevised "
Infotrakker Software
Most Valuable Expert 2012
Top Expert 2014
Commented:
You can't do that - you've set the Form's Recordset previously, which means you'd need to rebuild a NEW recordset (using your SQL above) and then set the Form's Recordset to THAT new Recordset.

Author

Commented:
That's exactly the idea that came to me yesterday while I was drinking a cup of coffee.  Basically I have a filter that populates the form.  I will call the code for that filter after clearing all the combo boxes and list boxes.  The stored procedure will then return all records and the form will then be repopulated with all the records.  I don't know why I didn't think of that earlier.  Sometimes I get tunnel vision.
Scott McDaniel (EE MVE )Infotrakker Software
Most Valuable Expert 2012
Top Expert 2014

Commented:
Sometimes I get tunnel vision.
I know that feeling - same experience for me today, trying to visualize a B-Tree formation where I could traverse the nodes in reverse. Went to my grandson's t-ball game last night and came away with the perfect solution (without even thinking about it). Goes to prove that sometimes the best thing you can do is step away from the machine.

Author

Commented:
Thank you for you insight

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial