deleting listview information using delete button

[firoz.raj]

can anyone tell me .why i got error object variable or with block variable not set .any help would be greately appreciated.here is
the code what i have written.Kindly find the attachment also.

Private Sub Command3_Click()
   Set CON = New ADODB.Connection
   If CON.state = adStateOpen Then
'   con.Open ("Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=False;Data Source=\\asfserver\itp$\Product_tabletest.mdb")
    Dim success As Boolean
    success = OpenConnection(CON)
    If success = False Then
       MsgBox ("Cannot open Connection")
     End If
    Set rs = New ADODB.Recordset
    Dim X As Integer
    If Not CON Is Nothing Then 'added check to see if con is set to a valid object
        For X = ListView1.ListItems.Count To 1 Step -1
            If ListView1.ListItems(X).Checked = True Then
'              con.Execute "DELETE from Supplier where Supplier_Name='" & ListView1.ListItems(x).SubItems(1) & "'"
               CON.Execute "DELETE from suppliers where sup_name='" & ListView1.ListItems(X).Text & "'"
               ListView1.ListItems.Remove X
               End If
        Next
        CON.Close
        Set CON = Nothing
    Else
        MsgBox "Connection not set"
    End If
    End If
   End Sub

[vb5prgrmr]

If you are talking about where you have the color tags that show up as text then I would say you have to open the connection before you can test it. From what I see you have only declared the variable con as a New ADODB.Connection but have not acutally opened it.

Basically you have done something like this...

Dim I as Integer
If I = 2 Then 'where I will only equal zero because you have not yet given it a value

Good Luck

[firoz.raj]

Sir connection is already open i have declared connection method
openconnection method in modconnection module .here is the code what i have written.

Public Function OpenConnection(ByRef conex As ADODB.Connection) As Boolean
On Error GoTo errHnd
    If conex Is Nothing Then
        Set conex = New ADODB.Connection
    End If
    If conex.state = adStateOpen Then OpenConnection = True: Exit Function
    Debug.Print globalData.Datapath
    conex.Open ("Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=False;Data Source=" & globalData.Datapath)
    ' this is very necessary to have a correct connection also in the FlexGridEditor
    If conex.state = adStateOpen Then
        OpenConnection = True
    Else
        OpenConnection = False
    End If
Exit Function
errHnd:
Err.Clear
    If MsgBox("Cannot open Database.Do you want to open Setup?", vbApplicationModal + vbInformation + vbYesNo, "Steelworks Production") = vbYes Then
        frmBasicSettings.Show (vbModal)
    End If
End Function

[Jupiter 2]

success is boolean so
success = OpenConnection(CON)
will OpenConnection(CON) actually give a True or False result

Perhaps try this
testResult = OpenConnection(CON)
msgbox testResult
exit sub
See what testResult actually gives in the msgbox.

[firoz.raj]

success is boolean so
I Got true value when i tried MsgBox (SUCCESS).here is the
complete code of addtolistview() method:

Public Sub Addtolistview()
'Dim success As Boolean
'DataPath = GetSetting(Application.Name, "BasicSetup", "Database", "")
'StrQuotationStartID = GetSetting(Application.Name, "BasicSetup", "QuotationStartID", "1")
'If DataPath = "" Then
'   frmBasicSettings.Show (vbModal)
'End If

Set con = New ADODB.Connection
Dim SUCCESS As Boolean
SUCCESS = OpenConnection(con)
MsgBox (SUCCESS)
Call OpenConnection(con)
'con.Open ("Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=False;Data Source=\\asfserver\itp$\Product_tabletest.mdb")
'Call OpenConnection(con)
Set rs = New ADODB.Recordset
rs.CursorLocation = adUseClient
'strSql = "Select"
'strSql = strSql & "[sup_name],[fax_no],[contact_person],[contact_no],[Office_address] from [Suppliers]"
strSql = "SELECT Suppliers.sup_id, Suppliers.sup_name, ItemTypes.item_Type, Suppliers.Fax_No, Suppliers.Contact_Person, Suppliers.Contact_No, Suppliers.Office_Address, Suppliers.Emails, Suppliers.Website FROM ItemTypes INNER JOIN Suppliers ON ItemTypes.Type_ID = Suppliers.Type_ID;"
rs.Open strSql, con, adOpenDynamic, adLockOptimistic
ListView1.View = lvwReport
'ListView1.BorderStyle = ccFixedSingle
ListView1.FullRowSelect = True
ListView1.BorderStyle = ccNone
'checking the width of listview control and divided by 5...so that all columns become of same width...here we used 5 because the control will have 5 columns
colWidth = ListView1.Width / 5
ListView1.ListItems.Clear

'here we are adding the columns
'you need similar no. of columns as much no. of listitems you want to add to the listview. now if you want to hide any column from displaying, just set its width to 0.
With ListView1
   .ColumnHeaders.Clear
'   Set colx = .ColumnHeaders.Add(, , ("Supplier No"), colWidth)
    Set colx = .ColumnHeaders.Add(, , ("Supplier Name"), ListView1.Width / 5)
    Set colx = .ColumnHeaders.Add(, , ("Fax No"), ListView1.Width / 5)
    Set colx = .ColumnHeaders.Add(, , ("Contact Person"), ListView1.Width / 5)
    Set colx = .ColumnHeaders.Add(, , ("Contact_no"), ListView1.Width / 5)
    Set colx = .ColumnHeaders.Add(, , ("Office_address"), ListView1.Width / 5)
End With
'adding rows to listview
 With ListView1
'   .ListItems.Clear
    If rs.RecordCount > 0 Then
        rs.MoveFirst
        While Not rs.EOF()
            Dim faxno, contactNo, officeAddress As String
            faxno = IIf(IsNull(rs!fax_no), "", rs!fax_no)
            contact = IIf(IsNull(rs!contact_person), "", rs!contact_person)
            suppliername = IIf(IsNull(rs!sup_name), "", rs!sup_name)
            contactNo = IIf(IsNull(rs!Contact_no), "", rs!Contact_no)
            officeAddress = IIf(IsNull(rs!office_address), "", rs!office_address)
            Set listitem = ListView1.ListItems.Add(, , supplier_name)
            listitem.Text = IIf(IsNull(rs!sup_name), "", rs!sup_name)
            listitem.SubItems(1) = rs.Fields("fax_no").Value & vbNullString
            listitem.SubItems(2) = rs.Fields("contact_person").Value & vbNullString
            listitem.SubItems(3) = rs.Fields("contact_no").Value & vbNullString
            listitem.SubItems(4) = rs.Fields("Office_address").Value & vbNullString
            rs.MoveNext
        Wend
    End If
End With
Set rs = Nothing
End Sub

[Jupiter 2]

So what result did Success give? What did it say? Did the message box say "TRUE" or "FALSE"? if it says anything else then the result is not boolean, it is a string.

MsgBox (SUCCESS)
Call OpenConnection(con)

you cannot call "TRUE" if success result is true. TRUE the word not a connection!

[vb5prgrmr]

But this is what I am getting at right here from your first post...

Private Sub Command3_Click()
>>>Set CON = New ADODB.Connection
>>>If CON.state = adStateOpen Then

At this point you have not opened the connection object. You have only declared it and in your next line you test before it is open. That is what I was getting at.

Good Luck

[firoz.raj]

Sir when i delete first time.it delete properly.when i again delete
it says index out of bounds.Kindly let me know any help would be
Greately appreciated.Kindly find the attachment also.

Private Sub cmddelete_Click()
   Me.Refresh
   Dim X As Integer
   If con.state = adStateOpen Then
'    Set con = New ADODB.Connection
'    con.Open ("Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=False;Data Source=\\asfserver\itp$\Product_tabletest.mdb")
    Dim success As Boolean
    Set con = New ADODB.Connection
    success = OpenConnection(con)
    If success = False Then
       MsgBox ("Cannot open Connection")
              Exit Sub
     End If
     If Not con Is Nothing Then 'added check to see if con is set to a valid object
        For X = ListView1.ListItems.Count To 1 Step -1
            If ListView1.ListItems(X).Checked = True Then
'              con.Execute "DELETE from Supplier where Supplier_Name='" & ListView1.ListItems(x).SubItems(1) & "'"
               con.Execute "DELETE from suppliers where sup_name='" & ListView1.ListItems(X).Text & "'"
               ListView1.ListItems.Remove X
               End If
        Next
        con.Close
        Set con = Nothing
    Else
        MsgBox "Connection not set"
    End If
    End If
   End Sub

[Jupiter 2]

ListView1.ListItems.Remove X

ListView1.ListItems.Remove(ListView1.ListItems(X))