Deleting Specific Records How?

Hello ,

I have this simple program and just wondering why it behaves like this,
I just want to delete a single record on a table
but instead of deleting only one, it deleted all the fields on it what do i missing here?

Here is my sample code:

DELETE CODE:

Private Sub Command3_Click()
Dim rs As New ADODB.Recordset
Dim rsView As New ADODB.Recordset

rs.Open "Select * From jobrecord" & _
" Where trim(left(plateno, len('" & Text1.Text & "'))) = trim('" & Text1.Text & "')", connAuto, adOpenDynamic, adLockOptimistic

If MsgBox("Are you sure you want to delete selected record.?", vbExclamation + vbYesNo) = vbYes Then
connAuto.Execute "Delete From jobrecord " & _
"Where plateno = '" & rs.Fields("plateno") & "'"
End If
rsView.Open "Select * From JobReportQuery " & _
" Where trim(left(plateno, len('" & Text1.Text & "'))) = trim('" & Text1.Text & "')", connAuto, adOpenDynamic, adLockOptimistic

Set DataGrid1.DataSource = rsView
End Sub

please see attached file screen 1 and screen 2.

hoping for your help guys..

Thanks naz.

VB Code:

connAuto.Execute "Delete * From jobrecord " & _
"Where plateno = '" & rs.Fields("plateno") & "'"

Quote:

---

Use *

---

for delete

Hi shak

Still have the same output it still delete all records.
when i click the delete button, just want to delete specific records.

VB Code:

rs.Open "select * From jobrecord " & _
"Where plateno = '" & rs.Fields("plateno") & "'"
While Not rs.EOF
    rs.Delete
    rs.MoveNext
Wend

Put your sql statement into a variable then do a

Debug.Print yourvariablename

Then post that. It is amazing how many error are solved by just that technique.

thanks for that, but i dont get it right, what i mean is that why is it the top record are always selected "rs.movenext" should move to the next record right?

for example:

record 1
record 2 ---------- i want to delete this record
record 3

instead deleting this record all records 1,2 and 3 are selected/delete.
Hmm....?

why do i get this error " Items cannot be found the collection corresponding to the requested name or ordinal."

thanks naz

You can't do it that way using movenext. Once you delete a record the next record becomes the current cursor position. Try this:

VB Code:

SQL = "Delete * From jobrecord " & _
"Where plateno = '" & rs.Fields("plateno") & "'"
Debug.Print SQL

Then post what it shows...

have you checked the value of plateno for your records in the table...

Thanks ran, correct me if i am wrong but is this right one to insert it?

[/Highlight]Private Sub Command3_Click()
Private Sub Command3_Click()
Dim rs As New ADODB.Recordset
Dim rsView As New ADODB.Recordset

SQL = "Delete * From jobrecord " & _
"Where plateno = '" & rs.Fields("plateno") & "'"
Debug.Print SQL



' rs.Open "Select * From jobrecord" & _
' " Where trim(left(plateno, len('" & Text1.Text & "'))) = trim('" & Text1.Text & "')", connAuto, adOpenDynamic, adLockOptimistic
' If MsgBox("Are you sure you want to delete selected record.?", vbExclamation + vbYesNo) = vbYes Then
' connAuto.Execute "Delete * From jobrecord " & _
' "Where plateno = '" & rs.Fields("plateno") & "'"
' End If
' rsView.Open "Select * From JobReportQuery " & _
' " Where trim(left(plateno, len('" & Text1.Text & "'))) = trim('" & Text1.Text & "')", connAuto, adOpenDynamic, adLockOptimistic

' Set DataGrid1.DataSource = rsView
End Sub

[Highlight=VB]

i know that this has a lot of correction to be made, thanks

You can leave all the other code in there but you also need

Dim SQL as String

Then put a breakpoint on the next line after the Debug.Print Statement to see the results. Copy and paste the information in the post.

ya, now post the debug window's text....that is the Sql statement...

Hi ganesh
I send him a query

VB Code:

connAuto.Execute "Delete * From jobrecord " & _
"Where plateno = '" & rs.Fields("plateno") & "'"

bu why this query is not working?

Delete * From jobrecord Where plateno = 'XXX-XXX'

here's what i get after putting some breakpoint
now i see so all the data which is = to xxx-xxx is now deleted thats why, there is no
records left is that right?

big help ran. ganes right i have to check the value first.

most welcome nazario