topic Re: Delete record from DBF table. in ArcPad Questions

Delete record from DBF table.

JayKappy — Thu, 16 Dec 2010 19:58:59 GMT

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

Re: Delete record from DBF table.

IvanKautter — Sat, 11 Dec 2021 12:19:55 GMT

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.

Re: Delete record from DBF table.

JayKappy — Sat, 11 Dec 2021 12:19:58 GMT

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

Re: Delete record from DBF table.

RolfBroch — Mon, 20 Dec 2010 11:11:23 GMT

Have you tried

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

Rolf

Re: Delete record from DBF table.

JayKappy — Sat, 11 Dec 2021 12:20:01 GMT

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

Re: Delete record from DBF table.

RolfBroch — Tue, 21 Dec 2010 07:41:53 GMT

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

Re: Delete record from DBF table.

JayKappy — Tue, 21 Dec 2010 15:40:31 GMT

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

Re: Delete record from DBF table.

JayKappy — Tue, 21 Dec 2010 15:42:35 GMT

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

Re: Delete record from DBF table.

RolfBroch — Wed, 22 Dec 2010 05:04:14 GMT

try

msgbox "Value is: " & varDescription

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

Re: Delete record from DBF table.

JayKappy — Sat, 11 Dec 2021 12:20:03 GMT

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

Re: Delete record from DBF table.

NeilChristie — Thu, 23 Dec 2010 01:11:02 GMT

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.

Re: Delete record from DBF table.

JayKappy — Sat, 11 Dec 2021 12:20:06 GMT

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

Re: Delete record from DBF table.

RolfBroch — Thu, 30 Dec 2010 05:09:26 GMT

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

Re: Delete record from DBF table.

NeilChristie — Mon, 03 Jan 2011 18:55:13 GMT

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.

Re: Delete record from DBF table.

JayKappy — Thu, 06 Jan 2011 20:06:06 GMT

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

Re: Delete record from DBF table.

JayKappy — Thu, 06 Jan 2011 20:07:07 GMT

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