[RESOLVED] delete data

[elixir_5000]

Private Sub Command1_Click()

Dim objConn As ADODB.Connection
Set objConn = New ADODB.Connection
objConn.Open ("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\database\DB.mdb;Persist Security Info=False")
Dim x As String
dim rs1 as adodb.recordset
Set rs1 = New ADODB.Recordset
 
x = " delete from data where inv ='" & Form29.Text1 & "'"
rs1.Open x, objConn
If rs1.EOF Then
MsgBox ("INVOICE NOT FOUND")
Else
MsgBox ("INVOICE DELETED")
Form30.Visible = False
End If
End Sub

when i run the above coe it gives error at line rs1.eof.. that operation can not be done when object is closed
tell me what is the problem

[shakti5385]

Public con As New ADODB.Connection
Function Open_Connection()
If Con.State = 1 Then
    Con.Close
End If
With Con
    .ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\database\DB.mdb;Persist Security Info=False"
    .ConnectionTimeout = 30
    .CursorLocation = adUseClient
    .Open
End With

call this function on the form load

[elixir_5000]

still not working same problem

[shakti5385]

Public Con As New ADODB.Connection
 
Private Sub Command1_Click()
Dim Rs1 As New ADODB.Recordset
X = "delete from data where inv ='" & Form29.Text1.Text & "'"
Rs1.Open X, Con, adOpenDynamic, adLockOptimistic
If Rs1.EOF = True Then
MsgBox ("INVOICE NOT FOUND")
Else
MsgBox ("INVOICE DELETED")
Form30.Visible = False
End If
End Sub
 
Private Sub Form_Load()
'Function Open_Connection()
If Con.State = 1 Then
    Con.Close
End If
With Con
    .ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\database\DB.mdb;Persist Security Info=False"
    .ConnectionTimeout = 30
    .CursorLocation = adUseClient
    .Open
End With
End Sub

As i understand your problem post in this which line error is occuring

[lintz]

What about....

Dim strCn As String
strCn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\database\DB.mdb;Persist Security Info=False"
 
Dim x As String
Dim rs1 = New ADODB.Recordset
 
x = "delete from data where inv ='" & Form29.Text1 & "'"
 
rs1.Open x, strCn, adOpenStatic, adLockOptimistic
 
If rs1.EOF Then
MsgBox ("INVOICE NOT FOUND")
Else
MsgBox ("INVOICE DELETED")
Form30.Visible = False
End If
End Sub

[elixir_5000]

If Rs1.EOF = True Then

this line is highlighted as the error comes saying the operation can not be performed when the object is closed

[shakti5385]

Public Con As New ADODB.Connection
Dim TSQL As String
Public RTMP As New ADODB.Recordset
 
Private Sub Command1_Click()
TSQL = "delete from data where inv ='" & Form29.Text1.Text & "'"
Set RTMP = Get_Special_Record_Set(TSQL)
If RTMP.EOF = True Then
MsgBox ("INVOICE NOT FOUND")
Else
MsgBox ("INVOICE DELETED")
Form30.Visible = False
End If
End Sub
 
Private Sub Form_Load()
Function Open_Connection()
If Con.State = 1 Then
    Con.Close
End If
With Con
    .ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\database\DB.mdb;Persist Security Info=False"
    .ConnectionTimeout = 30
    .CursorLocation = adUseClient
    .Open
End With
End Function
 
Function Get_Special_Record_Set(ByRef Shakti As String) As ADODB.Recordset
Dim Shakti As String
If RTMP.State = 1 Then
RTMP.Close
End If
RTMP.Open Shakti, Con, adOpenDynamic, adLockOptimistic
Set Get_Special_Record_Set = RTMP
End Function

Please check this quesry that it is correct or not

TSQL = "delete from data where inv ='" & Form29.Text1.Text & "'"

[elixir_5000]

it is actually deleting the record from the database even after giving the error that means query is ok

[shakti5385]

it is actually deleting the record from the database even after giving the error that means query is ok

r u getting error still

[jethrobg]

objConn.Execute "delete [B]what?? *[/B] from data where inv ='" & Form29.Text1 & "'

try this

[elixir_5000]

yes still there is the same error

[shakti5385]

Private Sub Command1_Click()
TSQL = "select * from data where inv ='" & Form29.Text1.Text & "'"
Set RTMP = Get_Special_Record_Set(TSQL)
If RTMP.EOF = True And RTMP.BOF = True Then MsgBox ("INVOICE NOT FOUND"): Exit Sub
While Not RTMP.EOF
    RTMP.Delete
    RTMP.MoveNext
Wend
MsgBox ("INVOICE DELETED")
Form30.Visible = False
End Sub

Try this final code

[jethrobg]

Dim objConn As ADODB.Connection
Set objConn = New ADODB.Connection
objConn.Open ("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\database\DB.mdb;Persist Security Info=False")
Dim x As String
dim rs1 as adodb.recordset
Set rs1 = New ADODB.Recordset
 
x = " select * from data where inv ='" & trim(Form29.Text1) & "'"
rs1.open x,objConn
if rs1.recordcount>0 then
 rs1.close
 objConn.execute("delete * from data where inv ='" & Form29.Text1 & "')
 msgbox "record deleted
else
 rs.close
 msgbox "item not found"
endif