Cannot delete..please help...

Dear Experts,

Has anyone could help me please to fix this code...it always failed I tried many time?...This is a "Delete" button, when I press this button, it should be delete row/item on the database...but it always failed....here is the code:

VB Code:

Private Sub CommandButton3_Click()
 
 Dim con As ADODB.Connection
    Set con = New ADODB.Connection
    con.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\exercise.mdb"
    
    Set rst = Execute_SQL_ReturnRecordSet("Select * from Item", False, "C:\exercise.mdb")
    
         rst.MoveFirst
Do While rst.EOF = False
    If UserForm1.txtqty.Text = rst.Fields("Item_Code").Value Then
       Exit Do
    End If
    rst.MoveNext
Loop
 
    TextBox3.Text = rst.Fields("Qty")
        
    Application.ScreenRefresh
End Sub

When I select item no 1 (using a combo box), then when I press "Check Current Stock" it show its Qty which is "1"...BUT, when I select other item numbers, let say item code number 3 it still show the Qty for item number 1...???....

Please help with code corrections...

Thank you so much...

Jennifer

If you want to show a particular record, use SQL to get it - there is no need to loop.

eg:

VB Code:

Private Sub CommandButton3_Click()
 
 Dim con As ADODB.Connection
    Set con = New ADODB.Connection
    con.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\exercise.mdb"
 
    Set rst = Execute_SQL_ReturnRecordSet("Select * from Item Where Item_Code = '" & UserForm1.txtqty.Text & "'", False, "C:\exercise.mdb")
    
If Not rst.EOF Then
  TextBox3.Text = rst.Fields("Qty")
Else
  MsgBox "not found!"
End If
        
    Application.ScreenRefresh
End Sub

Notice there is no "rst.MoveFirst", as this is done automatically when you open the recordset.

Also, why do you create a connection object (con), then pass the path of the database to your function?

You can try something like the ff....

VB Code:

Private Sub CommandButton3_Click()
    Dim con As ADODB.Connection
    Set con = New ADODB.Connection
    con.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\exercise.mdb"
    con.Execute "DELETE * FROM Item WHERE Field = 'criteria'"
End Sub

VB Code:

Private Sub CommandButton3_Click()
 
 Dim con As ADODB.Connection
    Set con = New ADODB.Connection
    con.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\exercise.mdb"
    
    Set rst = Execute_SQL_ReturnRecordSet("Select * from Item", False, "C:\exercise.mdb")
    
         rst.MoveFirst
Do While NOT rst.EOF
    If UserForm1.txtqty.Text = rst.Fields("Item_Code").Value Then
       Exit Do
    End If
    rst.MoveNext
Loop
 
    'This one generates an error for when there are no rows being pointed to by rst
    'TextBox3.Text = rst.Fields("Qty")
        
    Application.ScreenRefresh
End Sub

that's as far as code comments :D