Update DBF from ArcPAD

JayKappy · ‎12-16-2010

I am able to create a new Record in my DBF table from ArcPAD but trying to figure out how to UPDATE an existing...
just missing on how to add a WHERE clause...I can do this with a SQL update on existing tables related to a feature in my app but havent figured it out on a DBF

This is what I am doing for the new record....ANY OUT there shed soem light on how to apply this to an existing record?

 ' Called from the OnChange event of the combobox
  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, varDESCRIPTIO, varLEGEND_COL, varBACKGROUND, varHEIGHTNUM, varHeightUnit
   Dim varWIDTHNUM, varWidthUnit, varTEXT_, varFIELD9, varFIELD10, varXcoord_Lat, varYcoord_Lon 
   Dim varXcoord_Hen, varYcoord_Hen, varSIGN_SHAPE

   varMUTCD_CODE = objEFPageOneControls3("MUTCD_CODE").Value
   varDESCRIPTIO = objEFPageOneControls3("DESCRIPTIO").Value
   varLEGEND_COL = objEFPageOneControls3("LEGEND_COL").Value
   varBACKGROUND = objEFPageOneControls3("BACKGROUND").Value
   varHEIGHTNUM = objEFPageOneControls3("HEIGHTNUM").Value
   varHeightUnit = objEFPageOneControls3("HeightUnit").Value
   varWIDTHNUM = objEFPageOneControls3("WIDTHNUM").Value
   varWidthUnit = objEFPageOneControls3("WidthUnit").Value
   varTEXT_ = objEFPageOneControls3("TEXT_").Value
   varFIELD9 = objEFPageOneControls3("FIELD9").Value
   varFIELD10 = objEFPageOneControls3("FIELD10").Value
   varXcoord_Lat = objEFPageOneControls3("Xcoord_Lat").Value
   varYcoord_Lon  = objEFPageOneControls3("Ycoord_Lon").Value
   varXcoord_Hen = objEFPageOneControls3("Xcoord_Hen").Value
   varYcoord_Hen = objEFPageOneControls3("Ycoord_Hen").Value
   varSIGN_SHAPE = objEFPageOneControls3("SIGN_SHAPE").Value


 Dim objForm, objPageControls, objDBFFile
 Set objForm = EDITFORM
 Set objPageControls = objForm.Pages("PAGE3").Controls
 Set objDBFFile = CreateAppObject("recordset")
 objDBFFile.Open "C:\GPS_data_collections\StreetSign_Collection_ArcPAD\DBF_Lookup_Files\MUTCD_Code.dbf", 2
 objDBFFile.AddNew
 objDBFFile.Fields("MUTCD_CODE").Value = varMUTCD_CODE
 objDBFFile.Fields("DESCRIPTIO").Value = varDESCRIPTIO
 objDBFFile.Fields("LEGEND_COL").Value = varLEGEND_COL
 objDBFFile.Fields("BACKGROUND").Value = varBACKGROUND
 objDBFFile.Fields("HEIGHTNUM").Value = varHEIGHTNUM
 objDBFFile.Fields("HeightUnit").Value = varHeightUnit
 objDBFFile.Fields("WIDTHNUM").Value = varWIDTHNUM
 objDBFFile.Fields("WidthUnit").Value = varWidthUnit
 objDBFFile.Fields("TEXT_").Value = varTEXT_
 objDBFFile.Fields("FIELD9").Value = varFIELD9
 objDBFFile.Fields("FIELD10").Value = varFIELD10
 objDBFFile.Fields("Xcoord_Lat").Value = varXcoord_Lat
 objDBFFile.Fields("Ycoord_Lon").Value = varYcoord_Lon
 objDBFFile.Fields("Xcoord_Hen").Value = varXcoord_Hen
 objDBFFile.Fields("Ycoord_Hen").Value = varYcoord_Hen
 objDBFFile.Fields("SIGN_SHAPE").Value = varSIGN_SHAPE
 objDBFFile.Update
 objDBFFile.Close

 ' Clear Variables
 Set objDBFFile = Nothing
 Set objPageControls = Nothing
 Set objForm = Nothing

JayKappy · ‎12-16-2010

GOT IT...

Sub UpdateMUTCDCodeDBF

 ' Called from the OnChange event of the combobox
  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, varDESCRIPTIO
   varMUTCD_CODE = objEFPageOneControls3("MUTCD_CODE").Value
   varDESCRIPTIO = objEFPageOneControls3("DESCRIPTIO").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 FacilityID
    dim dbfQuery, editRecord
    dbfQuery = "[MUTCD_Code] = """ & varMUTCD_CODE & """"
    editRecord = rs.find(dbfQuery)

    'edit the record by adding the current date to the JETTED field
    if (editRecord > 0) then
        rs.movefirst
        rs.move(editRecord - 1)
        rs.fields("MUTCD_CODE").value = varMUTCD_CODE
        rs.fields("DESCRIPTIO").value = varDESCRIPTIO
        rs.Update
        rs.Close        
    end if

    set rs = nothing
End Sub

CharlesHuxtable · ‎02-03-2011

Hello Jay, I'm trying to do a similar thing. I have figured out how to add records to a dbf file from ArcPad, but I can't figure out how to scroll through records in the dbf and edit a particular records. Can you give me a bit more background on how you did yours?
Any help from Jay or others out there gratefully received.
Charles

KimSlezak · ‎03-04-2011

Chuxtable1,
This is how I did it if you're still looking. I take the unique id of the record I want to update and find it, then update field values. If unique id is not in the dbf, I add record and set field values. This is for a log file the client wanted for billing the coop/city they were inspecting poles for, and safer than having them try to pull it out of the shapefile after the fact.

In the .apl I have onok="Call UpdateTrackingRecord" for the EDITFORM.
UpdateTrackingRecord starts with assigning all control values to variables, then added a record to the tblpclog. We were ending up with lots of duplicates in the log if they opened the pole's record more than once, so had to add check for pole already being in the log, if it is the log is updated in case they changed the status of the pole.

Sub UpdateTrackingRecord

Dim myRSchk, DBFFilePath
Dim objForm
Dim fOBJID, fTMPBLOCK, fINSPDATE, fSTATUS, fGG, fGW, fRecID, fTransf, fLat, fLong, fPic,fREASON
Set objForm = ThisEvent.Object

fOBJID = CLng(objForm.Pages("INFO").Controls("txtOBJECTID"))
fINSPDATE = CDate(objForm.Pages("INSP").Controls("dtpINSPDate"))
fSTATUS = CStr(objForm.Pages("INFO").Controls("txtSTATUS"))
.....rest of controls


Dim upd
Set myRSchk = Application.CreateAppObject("Recordset")
DBFFilePath = Application.UserProperties("poncapath") & "\tblPCLog.dbf"
myRSchk.Open DBFFilePath,2 
myRSchk.MoveFirst
Do Until myRSchk.EOF
 if myRSchk.Fields("I_OBJECTID").Value= fOBJID then
  myRSchk.Fields("AUTOID").Value = fRecID
  myRSchk.Fields("I_INSPDATE").Value= fINSPDATE
  myRSchk.Fields("I_STATUS").Value = fSTATUS
  IF CLng(objForm.Pages("INSP").Controls("GGNum")) > 0 Then
  myRSchk.Fields("I_GG").Value = fGG
  End If
  IF CLng(objForm.Pages("INSP").Controls("GrndWire")) > 0 Then
  myRSchk.Fields("I_GW").Value = fGw
  End If

  myRSchk.Fields("I_TRANSF").Value = fTransf
  'MsgBox "here"&fSTATUS, vbok,"status"
  IF Len(objForm.Pages("INSP").Controls("cboReason").Text) > 0 Then
  myRSchk.Fields("I_REASON").Value = fREASON
  End If

  myRSchk.Fields("I_PIC").Value = fPic
  if myRSchk.Fields("I_LAT").Value <> "" Then
  myRSchk.Fields("I_LAT").Value = fLat
  End if
  if myRSchk.Fields("I_LONG").Value <> "" Then
  myRSchk.Fields("I_LONG").Value = fLong
  End if
 
 upd = 6 'update occured, else must add record
 Exit Do
 End if
 myRSchk.MoveNext
Loop

if upd <> 6 Then
myRSchk.AddNew
myRSchk.Fields("AUTOID").Value = fRecID
myRSchk.Fields("I_OBJECTID").Value= fOBJID
   .... all the other same lines for setting field values

CharlesHuxtable · ‎03-10-2011

kpaul,
could you send me the complete code please, ie where you say "......rest of controls" and "....all the other same lines for setting field values"

Thanks for your help

CharlesHuxtable · ‎03-13-2011

kpaul,
could you send me the complete code please, ie where you say "......rest of controls" and "....all the other same lines for setting field values"

Thanks for your help.
Further to this, can you give me a bit more information about how your form looks and operates on the handheld device. I'm sure that it is similar to what I am trying to do, it's just that I'm struggling to understand some of the functionality - being fairly new at VB scripting