none
loading a listbox RRS feed

  • Question

  • Hello.  Below is code that I am testing/experimenting with and am beginning to realize much of this could be in a public function rather than repeating every time a listbox is used.  And there will be numerous listboxes used in the new app.  The Private Sub does work and fill the listbox.  It is the function use that doesn't. The basic info is an Access 2013 frontend, SQL Server 2008 backend, using stored procs in sql server, all is going thru ado.  Below the Private Sub is the Public Sub that I've been experimenting with.  Since I'm posting here, its apparent I'm not getting it right.

    If someone could look this over and see the errors of my way, it would be nice.

    Thanks...John

    Private Sub Command10_Click()
        Dim cmd As ADODB.Command
        Dim rst As ADODB.Recordset
    
        Set cmd = New ADODB.Command
    
        With cmd
            .ActiveConnection = conn
            .CommandText = "Company_test"
            .CommandType = adCmdStoredProc
            Set rst = .Execute
        End With
    
    '=====================================
        'fills a listbox with below
        
        Set rst = New ADODB.Recordset
    
        With rst
            .CursorLocation = adUseClient
            .CursorType = adOpenStatic
            .LockType = adLockReadOnly
            .Open cmd
        End With
    
        Me.List11.ColumnCount = 3
        Me.List11.ColumnWidths = ".5in; 1.5in; 2in"
    
        Set Me.List11.Recordset = rst
    
        Me.Label15.Caption = "Records in listbox = " & rst.RecordCount
        
    '=====================================
    
        Set rst = Nothing
        Set cmd = Nothing
        
    End Sub
    Public Function NoParamStoredProc(strSQL)
        Dim cmd As ADODB.Command
        Dim rst As ADODB.Recordset
    
        Set cmd = New ADODB.Command
    
        With cmd
            .ActiveConnection = conn
            .CommandText = strSQL
            .CommandType = adCmdStoredProc
            Set rst = .Execute
        End With
    
    End Function
    


    Friday, January 8, 2016 2:55 AM

Answers

  • Hi John,
    I see a couple of problems with your code. The main problem is that NoParamStoredProc is a function that doesn't return anything, which is quite unusual. Also, you should be explicit about ByVal/ByRef, and about data types. This is not vbscript :-)

    So I would rewrite as:
    Public Function NoParamStoredProc(ByVal strStoredProc As String) As ADODB.Recordset
        Dim cmd As ADODB.Command
        Dim rst As ADODB.Recordset

        Set cmd = New ADODB.Command

        With cmd
            .ActiveConnection = conn
            .CommandText = strStoredProc
            .CommandType = adCmdStoredProc
            Set rst = .Execute
        End With

        Set NoParamStoredProc = rst
    End Function


    Next problem is that Command10 is not an appropriate name for a button. Be VERY descriptive with your control names, even when you are experimenting. Good practice.

    So in that button_click event you can write:
    Set Me.myListbox.Recordset = NoParamStoredProc("Company_test")

    Also look at the ParamArray keyword for a function declaration. I think you can enhance the AnyParamStoredProc function with any parameters, without too much trouble.


    -Tom. Microsoft Access MVP

    • Marked as answer by johnboy0276 Friday, January 8, 2016 8:12 AM
    Friday, January 8, 2016 5:23 AM

All replies

  • Hi John,
    I see a couple of problems with your code. The main problem is that NoParamStoredProc is a function that doesn't return anything, which is quite unusual. Also, you should be explicit about ByVal/ByRef, and about data types. This is not vbscript :-)

    So I would rewrite as:
    Public Function NoParamStoredProc(ByVal strStoredProc As String) As ADODB.Recordset
        Dim cmd As ADODB.Command
        Dim rst As ADODB.Recordset

        Set cmd = New ADODB.Command

        With cmd
            .ActiveConnection = conn
            .CommandText = strStoredProc
            .CommandType = adCmdStoredProc
            Set rst = .Execute
        End With

        Set NoParamStoredProc = rst
    End Function


    Next problem is that Command10 is not an appropriate name for a button. Be VERY descriptive with your control names, even when you are experimenting. Good practice.

    So in that button_click event you can write:
    Set Me.myListbox.Recordset = NoParamStoredProc("Company_test")

    Also look at the ParamArray keyword for a function declaration. I think you can enhance the AnyParamStoredProc function with any parameters, without too much trouble.


    -Tom. Microsoft Access MVP

    • Marked as answer by johnboy0276 Friday, January 8, 2016 8:12 AM
    Friday, January 8, 2016 5:23 AM
  • Tom, thanks for the reply.  Well, its official.  I suck at this.  Anyway, onward and upward.  Gave it a try and commented out all the cmd code and left the listbox info in.  Replaced the rst per your suggestion and got an error, "The object you entered is not a valid Recordset property."

    So, I commented out the .Open cmd as that produced an error first.  By the time I was fixing one another error showed.  Finally, the error above seemed to be the one that was of any consistency.  From there, I moved the following into the function from the command10.

    Set rst = New ADODB.Recordset
    
        With rst
            .CursorLocation = adUseClient
            .CursorType = adOpenStatic
            .LockType = adLockReadOnly
            .Open cmd
        End With

    After doing that, the listbox filled as it should.  And with no errors.  So now the function is:

    Public Function NoParamStoredProc(ByVal strStoredProc As String) As ADODB.Recordset
        Dim cmd As ADODB.Command
        Dim rst As ADODB.Recordset
    
        Set cmd = New ADODB.Command
    
        With cmd
            .ActiveConnection = conn
            .CommandText = strStoredProc
            .CommandType = adCmdStoredProc
            Set rst = .Execute
        End With
        
            Set rst = New ADODB.Recordset
    
        With rst
            .CursorLocation = adUseClient
            .CursorType = adOpenStatic
            .LockType = adLockReadOnly
            .Open cmd
        End With
    
    
        Set NoParamStoredProc = rst
        
    End Function


    And the Command10 button click is:

    Private Sub Command10_Click()
        
        Me.List11.ColumnCount = 3
        Me.List11.ColumnWidths = ".5in; 1.5in; 2in"
        
        Set Me.List11.Recordset = NoParamStoredProc("Company_test")
    
        Set rst = Nothing
        Set cmd = Nothing
        
    End Sub

    Okay.  That is quite the reduction in lines.  If this is sufficient then back to the proper naming and making the form.  And figuring this out if there are parameters involved. 

    Tom, thanks again for the response.  Feel free to comment on what I did above.  

    ...John


    Friday, January 8, 2016 5:58 AM
  • Congrats on your victory. The only thing I can suggest is that you take another look at this code tomorrow, and see if you can really understand what it does, and why it works. This is how you learn programming.

    Also make sure you have "Option Explicit" at the top of EVERY module (you can set the default for new modules in Tools > Options > Require variable declaration). Then recompile (Debug > Compile) and your Command10_Click event will point out a few errors that are easily corrected.


    -Tom. Microsoft Access MVP

    Friday, January 8, 2016 6:06 AM