Delete record from DBF table.

[JayKappy]

I cant figure out how to delete a record from a DBF table....I have a few DBF lookup tables with my application that I have code that allows the user to update and create new rows in the DBF table.  But now I want to give the user the ability to delete recrods as well...

I am tryign this with NO success

Any thoughts?

   Dim objRS3, objSelLayer3, objEFPageOneControls3, objEditForm3
 Set objSelLayer3 = Map.SelectionLayer
 Set objRS3 = objSelLayer3.Records
 objRS3.Bookmark = Map.SelectionBookmark
 Set objEditForm3 = application.map.layers("Supports").forms("EDITFORM")
 Set objEFPageOneControls3 = objEditForm3.Pages("page3").Controls

 ' Populate variables to the form controls.
 Dim varMUTCD_CODE
 varMUTCD_CODE = objEFPageOneControls3("MUTCD_CODE").Value

 Dim objForm, objPageControls, rs
 Set objForm = EDITFORM
 Set objPageControls = objForm.Pages("PAGE3").Controls
 Set rs = CreateAppObject("recordset")
 rs.Open "C:\GPS_data_collections\StreetSign_Collection_ArcPAD\DBF_Lookup_Files\MUTCD_Code.dbf", 2

    'search for the current records MUTCD_Code
    dim dbfQuery, editRecord
    dbfQuery = "[MUTCD_Code] = """ & varMUTCD_CODE & """"
    editRecord = rs.find(dbfQuery)

    'edit the record
    if (editRecord > 0) then

  Dim Answer, MyNote
  MyNote = "Are you sure you want to delete this MUTCD Code?"
  Answer = MsgBox(MyNote, vbQuestion + vbYesNo, "???")
  If Answer = vbNo Then
   Exit Sub
  Else
   rs.movefirst
   rs.move(editRecord - 1)
   rs.Delete
   rs.Update
   rs.Close 
  End If
       
    end if

    set rs = nothing

[NeilChristie]

I've had problems with this in the past.  Try removing the double quotes from the rs.Open line.  The stucture that works for me is to store the filepath and name in a variable and reference that in the rs.Open line wrapped in brackets.
Eg.   rs.Open (FileToUseVariable), 2
Hope this helps.

Cheers,
Neil.

[JayKappy]

This is what I did to no avail....cant figure this one out...driving me nuts...
I know that I am getting into the RS because I can return the correct record with this

Dim varDescription
varDescription = rs.fields("Descriptio").value
msgbox "Value is: " & varDescription

..snip

I thank you all for your help with this...any other thoughts woudl be great but understand this is hard to pin point...
HAVE A GREAT HOLIDAY.

Dim FilePath
FilePath = "C:\GPS_data_collections\Packets_Outgoing\Support_Signs_Data_Collection_2011\MUTCD_Code.dbf"

rs.Open(FilePath),2 
'search for the current records MUTCD_Code
Dim dbfQuery
dbfQuery = "MUTCD_Code = """ & varMUTCD_CODE & """"
rs.find(dbfQuery)
   
Dim varDescription
varDescription = rs.fields("Descriptio").value
msgbox "Value is: " & varDescription

rs.Delete
rs.Update
rs.Pack
rs.Close 
 
msgbox "Deleteing"
set rs = nothing

..snip

[RolfBroch]

This is strange, try to use the code below to check if the record is deleted.

Dim varDescription
varDescription = rs.fields("Descriptio").value
msgbox "Value is: " & varDescription

rs.Delete
rs.Update
MessageBox "Is the record deleted? " & CStr(rs.IsDeleted)
rs.Pack
rs.Close

msgbox "Deleteing"
set rs = nothing

[NeilChristie]

Try wrapping your field name in square brackets in the query variable. 

dbfQuery = "[MUTCD_Code] = """ & varMUTCD_CODE & """"

Without them, the search finds the correct record in the recordset but the delete doesn't happen.
With them, the search finds the record and the recordset is updated after the delete.

Cheers,
Neil.

[JayKappy]

This is strange, try to use the code below to check if the record is deleted.

Dim varDescription
varDescription = rs.fields("Descriptio").value
msgbox "Value is: " & varDescription

rs.Delete
rs.Update
MessageBox "Is the record deleted? " & CStr(rs.IsDeleted)
rs.Pack
rs.Close

msgbox "Deleteing"
set rs = nothing

It tells me FALSE

[JayKappy]

Try wrapping your field name in square brackets in the query variable. 

dbfQuery = "[MUTCD_Code] = """ & varMUTCD_CODE & """"

Without them, the search finds the correct record in the recordset but the delete doesn't happen.
With them, the search finds the record and the recordset is updated after the delete.

Cheers,
Neil.

I think the [] worked.....Yea it did....THANK YOU VERY MUCH