Deleting Specific Records How?

[nazario]

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.

[shakti5385]

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

Use *

for delete

[nazario]

Hi shak

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

[shakti5385]

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

[randem]

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.

[nazario]

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

[randem]

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

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

Then post what it shows...

[ganeshmoorthy]

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

[nazario]

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

[randem]

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.

[ganeshmoorthy]

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

[shakti5385]

Hi ganesh
I send him a query

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

bu why this query is not working?

[nazario]

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.

[ganeshmoorthy]

most welcome nazario