My Recordset cant DELETE for rd.delete

[edisonl via AccessMonster.com]

Help, help..

I had a form that open up like this:

rd.Open table, conn, adOpenDynamic, adLockBatchOptimistic
rd.filter = "Name='" & name_text & "'"

Do Until rd.EOF

if(rd!Name = Name_Text)then
rd.delete
rd.Delete adAffectCurrent
rd.close
conn.close
set rd = nothing
set conn = nothing
Exit Sub
endif
rd.movenext
loop

rd.close
conn.close
set rd = nothing
set conn = nothing

Cant Delete the Record it seems... Can Anyone Helps ?

Edison

 

[Stefan Hoffmann]

hi Edison,

Do Until rd.EOF
if(rd!Name = Name_Text)then
rd.delete
rd.Delete adAffectCurrent
rd.close
conn.close
set rd = nothing
set conn = nothing
Exit Sub
endif
rd.movenext
loop

Never delete in a forward loop. Consider this:

Do While Not rd.EOF
rd.Delete
rd.MoveNext
Loop

Before first delete:
1 <- current record
2
3

After the delete:
2 <- current record
3

Then the move next occurs:
2
3 <- current record

Cant Delete the Record it seems... Can Anyone Helps ?

What do you like to achive? Delete all or only the first matching record?

rd.Open table, conn, adOpenDynamic, adLockBatchOptimistic
rd.filter = "Name='" & name_text & "'"

This will delete all matching records:

Dim SQL As String

SQL = "DELETE FROM " & table & " " & _
"WHERE [Name] = '" & Replace(Name_Text, "'", "''") & "'"
CurrentDb.Execute SQL, dbFailOnError

Delete the first matching:

Dim rs As DAO.RecordSet

Set rs = CurrentDb.OpenRecordset(table, adOpenDynamic)
rs.FindFirst "[Name] = '" & Replace(Name_Text, "'", "''") & "'"
If Not rs.NoMatch Then
rs.Delete
End If
rs.Close
Set rs = Nothing


btw, [Name] is a reserved word and should not be used, especially as a
field name.


mfG
--> stefan <--

 

[edisonl via AccessMonster.com]

'Right Stefan, Will try it out..
'What I want to achieve is to Search & Destroy Individual records(if Match)
Only

hi Edison,

Do Until rd.EOF
if(rd!Name = Name_Text)then

[quoted text clipped - 8 lines]

rd.movenext
loop

Never delete in a forward loop. Consider this:

Do While Not rd.EOF
rd.Delete
rd.MoveNext
Loop

Before first delete:
1 <- current record
2
3

After the delete:
2 <- current record
3

Then the move next occurs:
2
3 <- current record

Cant Delete the Record it seems... Can Anyone Helps ?

What do you like to achive? Delete all or only the first matching record?

rd.Open table, conn, adOpenDynamic, adLockBatchOptimistic
rd.filter = "Name='" & name_text & "'"

This will delete all matching records:

Dim SQL As String

SQL = "DELETE FROM " & table & " " & _
"WHERE [Name] = '" & Replace(Name_Text, "'", "''") & "'"
CurrentDb.Execute SQL, dbFailOnError

Delete the first matching:

Dim rs As DAO.RecordSet

Set rs = CurrentDb.OpenRecordset(table, adOpenDynamic)
rs.FindFirst "[Name] = '" & Replace(Name_Text, "'", "''") & "'"
If Not rs.NoMatch Then
rs.Delete
End If
rs.Close
Set rs = Nothing

btw, [Name] is a reserved word and should not be used, especially as a
field name.

mfG
--> stefan <--

 

[edisonl via AccessMonster.com]

Hi Stefan,
Almost overlooked, just wonder why do I need to use replace in this case ?
And How is the syntax works ?

Edison



* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
* * * * * *

hi Edison,

Do Until rd.EOF
if(rd!Name = Name_Text)then

[quoted text clipped - 8 lines]

rd.movenext
loop

Never delete in a forward loop. Consider this:

Do While Not rd.EOF
rd.Delete
rd.MoveNext
Loop

Before first delete:
1 <- current record
2
3

After the delete:
2 <- current record
3

Then the move next occurs:
2
3 <- current record

Cant Delete the Record it seems... Can Anyone Helps ?

What do you like to achive? Delete all or only the first matching record?

rd.Open table, conn, adOpenDynamic, adLockBatchOptimistic
rd.filter = "Name='" & name_text & "'"

This will delete all matching records:

Dim SQL As String

SQL = "DELETE FROM " & table & " " & _
"WHERE [Name] = '" & Replace(Name_Text, "'", "''") & "'"
CurrentDb.Execute SQL, dbFailOnError

Delete the first matching:

Dim rs As DAO.RecordSet

Set rs = CurrentDb.OpenRecordset(table, adOpenDynamic)
rs.FindFirst "[Name] = '" & Replace(Name_Text, "'", "''") & "'"
If Not rs.NoMatch Then
rs.Delete
End If
rs.Close
Set rs = Nothing

btw, [Name] is a reserved word and should not be used, especially as a
field name.

mfG
--> stefan <--

 

[Stefan Hoffmann]

hi Edison,

Almost overlooked, just wonder why do I need to use replace in this case ?
And How is the syntax works ?

rs.FindFirst "[Name] = '" & Replace(Name_Text, "'", "''") & "'"

As an example, Name_Text is "Lady d'Arbanville". Without the replace you
will create this

"[Name] = '" & Name_Text & "'"

which will be evaluated to

"[Name] = 'Lady d'Arbanville'"

You always need to escape the use quotation mark by doubling it,
otherwise the compiler/interpreter is not able to process the string.
Valid strings are:

"[Name] = 'Lady d''Arbanville'"

or using double quotes

"[Name] = ""Lady d'Arbanville"""


The Replace() function is needed to double the single quotes for the
first case.


mfG
--> stefan <--