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

[IvanKautter]

Try this:

    rs.Find(dbfQuery)

    'edit the record
    if Not rs.EOF 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.Delete
   rs.Update
   rs.Close 
  End If
       
    end if

    set rs = nothing

Find method of the recordset object should move the cursor to the desired record.  Delete that record.  Update. Close.  Should work but I have not tested it.

[JayKappy]

That didnt seem to work....As I said I can update and create a new record but having issues with Deleting...

This is what I am doing:  I am finding a specific MUTCD_Code in the dbf adn then deleting that record...Well thats what I am TRYING to do.  I run the code the close ArcPAD.  I then go into the DBF and the record is still there.

If I msgbox the dbfQuery  I get the correct value..just cant figure out why it wont delete

    
'search for the current records MUTCD_Code
Dim dbfQuery
dbfQuery = "MUTCD_Code = """ & varMUTCD_CODE & """"
rs.find(dbfQuery)

'edit the record
if Not rs.EOF 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.Delete
   rs.Update
   rs.Close 
  End If
       
    end if
    set rs = nothing

[RolfBroch]

Have you tried

  If Answer = vbYes Then
   rs.Delete
   rs.Update
   rs.Close
  End If

Rolf

[JayKappy]

still not working....
I added this: varDescription = rs.fields("Descriptio").value to determine if I was even getting a record or not...and verified that I am and it is the correct record...
it simply wont delete the record from the dbf file.  Could the dbf be corrupt or something?

 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\MUTCD_Code.dbf", 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.Close 
 msgbox "Deleteing"
    set rs = nothing

[RolfBroch]

Are you sure the record is not deleted? Remember that deleting a record in dbase file usually only marks the record for deletion and does not actually delete it. To do that you must do an:

aRecordset.Pack

after the update

Rolf

[JayKappy]

Are you sure the record is not deleted? Remember that deleting a record in dbase file usually only marks the record for deletion and does not actually delete it. To do that you must do an:

aRecordset.Pack

after the update

Rolf

All I am doing is in my code example....That is what I am asking..is there something more I have to do to delete the record?
So I can simply put aRecordset.Pack in my ArcPAD code?  Not really sure how to pack this db from ArcPAD...guess thats why the record is not deleting...
Have never done this before...workign with dbf's, updating, deleting, etc
After I run the code...I go into the dbf and the record is still there....not marked deleted or anything....if I see all the field values and the record I am assuming its still there....
Thanks

[JayKappy]

...............................................................

[RolfBroch]

try

msgbox "Value is: " & varDescription

rs.Delete
rs.Update
             rs.Pack
rs.Close
Rolf

[JayKappy]

I do really thank you for yoru help...it is appreciated...but no go...I dont know whats going on here...
this is what i have:
I open ArcPAD and run the this code.  It tells me which record it is going to delete.

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

The messagebox tells me the correct value so It seems that I am on the correct record. 
I then close ArcPAD and go into the dbf and the record is still there...
I have no idea why it wont delete the record...

Could it be soemthing else?  I imagine I would get errors if something else is wrong....hmmmmm

 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\Packets_Outgoing\Support_Signs_Data_Collection_2011\MUTCD_Code.dbf", 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