|
|
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
--
Message posted via http://www.accessmonster.com
|
|
hi Edison,
edisonl via AccessMonster.com wrote:
[Quoted 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
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 <--
|
|
'Right Stefan, Will try it out..
'What I want to achieve is to Search & Destroy Individual records(if Match)
Only :)
Stefan Hoffmann wrote:
[Quoted Text] >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 <--
--
Message posted via http://www.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
* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
* * * * * *
Stefan Hoffmann wrote:
[Quoted Text] >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 <--
--
Message posted via http://www.accessmonster.com
|
|
hi Edison,
edisonl via AccessMonster.com wrote:
[Quoted Text] > 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 <--
|
|
|