-
October 2nd, 2013, 08:13 PM
#1
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
-
October 3rd, 2013, 02:09 AM
#2
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.
-
October 8th, 2013, 04:07 PM
#3
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
-
October 8th, 2013, 05:45 PM
#4
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|