CodeGuru Home VC++ / MFC / C++ .NET / C# Visual Basic VB Forums Developer.com
Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2013
    Posts
    1

    Unhappy Run-time error '91': Object variable or With block variable not set

    I'm making a sales & inventory system & i have this error... when I tried to add Listview codes(different) even though there are already different codes of adodc DataGrid View in other forms and modules...
    the problem also was I combined the declarations in the Module. Is that possible to declare two modules, one for adodc, and one for listview...??
    I have been reading several treads on this forum abut this error but i cannot solve it.

    I get an error on this line in Sub LoadData() part::: rs.Open "Select * from PurchaseOrder", db, 3, 3

    NewPurchaseOrder Form
    Code:
    Dim y
    Dim list As ListItem
    
    
    Private Sub cmdAdd_Click()
    ConnectDB
        rs.Open "Table1", db, 3, 3
            rs.AddNew
                rs(1) = txtPONumber
                rs(2) = txtDatePurchased
                rs(3) = txtSupplierName
                rs(4) = txtQuantity
                rs(5) = txtItemName
                rs(6) = txtUnitPrice
                rs(7) = txtPayableAmount
                'rs(8) = txtContact
            rs.Update
        Set rs = Nothing
        db.Close: Set db = Nothing
          LoadData
    End Sub
    
    
    Private Sub cmdDelete_Click()
    ConnectDB
                rs.Open "Select * from PurchaseOrder where ID = " & ListView1.SelectedItem, db, 3, 3
                    rs.Delete
                   
                Set rs = Nothing
            db.Close: Set db = Nothing
            LoadData
    End Sub
    
    
    Private Sub Form_Load()
    With ListView1.ColumnHeaders
            
            .Add , , "ID", 1700
            .Add , , "PO Number", 1700
            .Add , , "Date Purchased", 1500
            .Add , , "Supplier Name", 1500
            .Add , , "Quantity", 1500
            .Add , , "Item Name", 700
            .Add , , "Unit Price", 1700
            .Add , , "Payable Amount", 1000
            '.Add , , "Contact No", 1500
            
           
    
        End With
         LoadData
    End Sub
    
    Sub LoadData()
    ListView1.ListItems.Clear
    Dim list As ListItem
    Dim x As Integer
      ConnectDB
            rs.Open "Select * from PurchaseOrder", db, 3, 3
                Do Until rs.EOF
                    Set list = ListView1.ListItems.Add(, , rs(0))
                        For x = 1 To 7
                            list.SubItems(x) = rs(x)
                    Next x
                rs.MoveNext
           Loop
    
                Set rs = Nothing
            db.Close: Set db = Nothing
        
        
      ' .Add , , "Price", 800
      
    End Sub
    
    Private Sub ListView1_Click()
    txtPONumber = ListView1.SelectedItem.SubItems(1)
    txtDatePurchased = ListView1.SelectedItem.SubItems(2)
    txtSupplierName = ListView1.SelectedItem.SubItems(3)
    txtQuantity = ListView1.SelectedItem.SubItems(4)
    txtItemName = ListView1.SelectedItem.SubItems(5)
    txtUnitPrice = ListView1.SelectedItem.SubItems(6)
    txtPayableAmount = ListView1.SelectedItem.SubItems(7)
    'txtContact = ListView1.SelectedItem.SubItems(8)
    
    
    cmdAdd.Enabled = False
    cmdDelete.Enabled = True
    End Sub

    Module1 Form
    Code:
    Global rs As ADODB.Recordset
    Global conn As ADODB.Connection
    Public Sub connect()
    Dim ctr As Integer
    Dim sql As String
    
    
    
    Set rs = New ADODB.Recordset
    Set conn = New ADODB.Connection
    conn.Open "provider=Microsoft.ACE.OLEDB.12.0;Data Source =" & App.path & "\DB.Quadcom.mdb;"
    
    End Sub

    Module2 Form
    Code:
    Public db As New ADODB.Connection
    
    Public path As String
    Public PathName As String
    Public list As String
    
    Public Sub ConnectDB()
    
        path = App.path & "\DB.Quadcom.mdb"
        
            db.Open "provider=Microsoft.ACE.OLEDB.12.0;Data Source =" & App.path & "\DB.Quadcom.mdb;"
    
            
    End Sub
    Last edited by DataMiser; October 3rd, 2013 at 02:04 AM. Reason: added code tags

  2. #2
    Join Date
    Jul 2008
    Location
    WV
    Posts
    5,362

    Re: Run-time error '91': Object variable or With block variable not set

    Your loaddata does not create an object for rs
    your connectdb does not create an object for rs
    your connect rountine does but is not called there

    So your problem is that at the point where you try to open the RS you have not yet Set RS=New ADODB.Recordset
    Always use [code][/code] tags when posting code.

  3. #3
    Join Date
    Dec 2008
    Location
    Step Into(F11)
    Posts
    465

    Re: Run-time error '91': Object variable or With block variable not set

    at first,Your module1 code is also wrong . check the connection first .then try the following way .
    Code:
    Global conn As ADODB.Connection
    Global rs As ADODB.Recordset
    Public Sub connect()
    Dim ctr As Integer
    Dim sql As String
    
    
    
    
    Set conn = New ADODB.Connection
    conn.Open "provider=Microsoft.ACE.OLEDB.12.0;Data Source =" & App.path & "\DB.Quadcom.mdb;"
    if conn.state=adstateopen then
       Set rs = New ADODB.Recordset
       rs.open "write your DML Statement ",con,2
       ' go through manipulation of database handling
       rs.update 
    
    End Sub

  4. #4
    Join Date
    Jul 2008
    Location
    WV
    Posts
    5,362

    Re: Run-time error '91': Object variable or With block variable not set

    I would not say that it is wrong to not test the state especially on the line after opening it. It should be open at that point or generate an error on the open statement.

    The part that is causing the error is that RS has not been sit to instance of a recordset like I stated in post #2

    Actually I would not use a global RS but a local one in the sub if the sub is just intended to update a recordset and of course I would close the recordset afterwards.

    It is really a bit sloppy to use a global connection and global rs and then assign them as new in a sub that will be called more than once and very sloppy if you are not closing them.
    VB will let you get away with this kind of stuff but it is never a good idea and leads to poor programming habits.
    Always use [code][/code] tags when posting code.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  





Click Here to Expand Forum to Full Width

Featured