Update Related Table

JayKappy · ‎04-14-2010

I have an ArcPAD application that has a related table. I used ArcPAD manager to create the axf file

I am trying to update that related table from a button in ArcPAD.

User clicks button
THe code grabs a bunch of values from input boxes on the form
Write them to varaibles (which I can push to a mesage box)
But I think I need serious help on the update/Insert into SQL Statement...

I dont even know if this can be done this way....Has anyone ever done this?

I need to update the related table from this button....

PLEASE HELP

THanks

Sub UpdateValues

Dim objRS, objSelLayer, objEFPageOneControls3, objEditForm3
Set objSelLayer = Map.SelectionLayer
Set objRS = objSelLayer.Records


objRS.Bookmark = Map.SelectionBookmark

 Set objEditForm3 = application.map.layers("Retaining_Walls").forms("EDITFORM")
 Set objEFPageOneControls3 = objEditForm3.Pages("page1").Controls

 Dim varcbo_User, vartxtUniqueID, varLow, varHigh, varAvg, varLength, varArea, varMaterial, varCondition, varDate  
  
  varcbo_User = objEFPageOneControls3("cbo_User").Value
  vartxtUniqueID = objEFPageOneControls3("txtUniqueID").Value
  varLow = objEFPageOneControls3("Edit3").Value
  varHigh = objEFPageOneControls3("Edit4").Value
  varAvg = objEFPageOneControls3("Edit1").Value
  varLength = objEFPageOneControls3("Edit2").Value
  varArea = objEFPageOneControls3("Edit5").Value
  varMaterial = objEFPageOneControls3("Field2").Value
  varCondition = objEFPageOneControls3("Field1").Value
  varDate = objEFPageOneControls3("Date1").Value

msgbox varcbo_User & " " & vartxtUniqueID & " " & varlow & " " & varHigh & " " & varAvg & " " & varLength & " " & varArea & " " & varMaterial & " " & varCondition & " " &  varDate
  

 Dim strFileName2
 strFileName2 = varAXFFileName_2

       '++ open the selected AXF file
       Dim pDS6
       Set pDS6 = OpenAXF(strFileName2)
       If (pDS6 Is Nothing) Then
            Console.Print "Open DataSource failed"
            Exit Sub
       End If

 Dim  pRS6
 Set pRS6 = pDS6.Execute("INSERT INTO RETAININGWALL_HISTORICAL (UNIQUEID, DIM_LOW) VALUES (vartxtUniqueID, varLow)")

Endn Sub

JayKappy · ‎04-14-2010

Here I can retrieve a value from the Related table....the max UNIQUE ID value

I Just need to figure out how to INSERT INTO that table...

Any thoughts?

  
Dim strFileName2
strFileName2 = varAXFFileName_2

 '++ open the selected AXF file
 Dim pDS
 Set pDS = OpenAXF(strFileName2)
      If (pDS Is Nothing) Then
          Console.Print "Open DataSource failed"
      Exit Sub
      End If

Dim  MaxID, MaxID2, pRS, pRS2, pRS3
Dim MaxRecordCount
Set pRS = pDS.Execute("SELECT MAX(UNIQUEID) AS MaximumID FROM RETAININGWALL_HISTORICAL")

pRS.MoveFirst
Do While Not pRS.EOF
 MaxRecordCount = cInt(pRS("MaximumID").value)
pRS.MoveNext 
Loop

Msgbox MaxRecordCount

DaytonPoff · ‎04-15-2010

Check out the forum I started called "Customizing Related Table Sample .vbs Script".
I'm trying to do basically the same thing. Inspections.vbs may help point you in the right directions in regards to SQL scripting.

Check out the following two directories on your computer.

C:\Program Files\ArcPad 8.0\Samples\Riverside (includes the Riverside_mdb.axf sample)
C:\Program Files\ArcPad 8.0\Developer\Samples\RelatedTables\RelatedTablesForm (includes the Inspections.vbs sample)

Hope that helps!

JayKappy · ‎04-16-2010

FIRST OFF THANKS FOR YOUR RESPONCE....like a ghost town in ArcPAD forums sometimes...

I DONT KNOW WHY there is an Angry face on my entry...I am not angry??????

I couldnt find the second example above refering to related tables...
I can get the update to happen but soemthing wierd is going on....

THIS IS WHAT I AM DOING:
Using ArcPAD on my desktop I am able to click the button
It grabs the two values from the form
Builds the SQL string
Updates the related table via the SQL string

I can then go to the related table tab and see the newly added record.
I stop editing
Save

Import back into ArcMap and the record does not import.
I looked back in ArcPAD and its there.

If I manually create a new record in the related table and import the new record comes in.

I dont know why this is happening....MAYBE because I am not giving it an OBJECTID??????

WHY can I see the record in ArcPAD but it wont import back into ArcMap via the check-in process? Again maybe there is no OBJECTID being assigned?

THanks

Sub SaveButton

Dim objRS, objSelLayer, objEFPageOneControls3, objEditForm3
Set objSelLayer = Map.SelectionLayer
Set objRS = objSelLayer.Records

objRS.Bookmark = Map.SelectionBookmark

 Set objEditForm3 = application.map.layers("Retaining_Walls").forms("EDITFORM")
 Set objEFPageOneControls3 = objEditForm3.Pages("page1").Controls

 Dim varcbo_User, vartxtUniqueID 
  
 varcbo_User = objEFPageOneControls3("cbo_User").Value
 vartxtUniqueID = objEFPageOneControls3("txtUniqueID").Value

 Dim sSQL

 sSQL = "INSERT INTO RETAININGWALL_HISTORICAL (UNIQUEID, INSPECT_BY) VALUES ('" & vartxtUniqueID & "', '" & varcbo_User & "')"

 Dim strFileName2
      strFileName2 = varAXFFileName_2

       '++ open the selected AXF file
       Dim pDS
       Set pDS = OpenAXF(strFileName2)
       If (pDS Is Nothing) Then
            Console.Print "Open DataSource failed"
            Exit Sub
       End If


 Dim iCount
 iCount = pDS.Execute(sSQL)
 pDS.Close()

End Sub

JayKappy · ‎04-16-2010

All right....my code was correct above.....I saw the record being added to the related table but it would not come across when I check-in the data in ArcMap...

I first thought it was the OBJECT ID that needed to be added but that was not the case.
I then came across some older forum entries and saw that they added the AXF_STATUS, and the AXF_TIMESTAMP

These two fields are what ArcMap check-in relys on to determine whats new in the axf file. If they are blank then they are skipped from the import process.....

You can also check out this older entry
http://forums.esri.com/Thread.asp?c=34&f=2465&t=298570&mc=2#msgid932792

This is my final code:
Notice:
AXF_STATUS is set to 1...this is for a new file (there are other numbers that you can set to delete the file etc)
AXF_TIMESTAMP is set to grab the system clock time ( getDate() )

HOPE THIS HELPS.....ANY QUESTIONS FEEL FREE TO ASK.......

Sub SaveButton

Dim objRS, objSelLayer, objEFPageOneControls3, objEditForm3
Set objSelLayer = Map.SelectionLayer
Set objRS = objSelLayer.Records

objRS.Bookmark = Map.SelectionBookmark

 Set objEditForm3 = application.map.layers("Retaining_Walls").forms("EDITFORM")
 Set objEFPageOneControls3 = objEditForm3.Pages("page1").Controls

 Dim varcbo_User, vartxtUniqueID, varLow, varHigh, varAvg, varLength, varArea, varMaterial, varCondition, varDate  
  
  varcbo_User = objEFPageOneControls3("cbo_User").Value
  vartxtUniqueID = objEFPageOneControls3("txtUniqueID").Value
  varLow = objEFPageOneControls3("Edit3").Value
  varHigh = objEFPageOneControls3("Edit4").Value
  varAvg = objEFPageOneControls3("Edit1").Value
  varLength = objEFPageOneControls3("Edit2").Value
  varArea = objEFPageOneControls3("Edit5").Value
  varMaterial = objEFPageOneControls3("Field2").Value
  varCondition = objEFPageOneControls3("Field1").Value
  varDate = objEFPageOneControls3("Date1").Value
  

 Dim sSQL
 sSQL = "INSERT INTO RETAININGWALL_HISTORICAL (UNIQUEID, MATERIAL, DIM_LOW, DIM_HIGH, DIM_AVE, LENGTH, AREA, CONDITION, INSPECT_DATE, AXF_STATUS, AXF_TIMESTAMP, INSPECT_BY) VALUES ('" & vartxtUniqueID & "', '" & varMaterial & "', '" & varLow & "', '" & varHigh & "', '" & varAvg & "', '" & varLength & "', '" & varArea & "', '" & varCondition & "', '" & varDate & "', 1, getDate(), '" & varcbo_User & "')"



 Dim strFileName2
      strFileName2 = varAXFFileName_2

       '++ open the selected AXF file
       Dim pDS
       Set pDS = OpenAXF(strFileName2)
       If (pDS Is Nothing) Then
            Console.Print "Open DataSource failed"
            Exit Sub
       End If


  Dim iCount
  iCount = pDS.Execute(sSQL)
  pDS.Close()


Msgbox "Done"

End Sub

MattCooper · ‎04-16-2010

I think you need to also insert values for the AXF_TIMESTAMP and AXF_STATUS fields.

AXF_STATUS = 1 sets it as a new record, I believe.

Matt

JayKappy · ‎04-16-2010

Thanks mcoop05......just saw that in another thread....put those two in there and it works great....

You must have posted that seconds after my last post explaining that.....THANKS

DaytonPoff · ‎04-26-2010

I confirmed with another user that 8.0.3 has the Related Tables samples. She applied the service pack and now has the files I've been talking about.

Congratulation on getting your code to work. I finally worked out all the bugs in mine and it is working too. Thanks for the advice. Looks like we went about it two separate ways, but were both successful in the end.

MatthewWilson1 · ‎08-19-2011

Hi Jay,

This thread has helped me quite a bit, as I am trying to do something very similar. However, I'm having some trouble getting your code to work in ArcPad 10 (namely the INSERT SQL statement).

Once an existing facility is selected from the map, I would like to auto populate the related table (DA_FORM) with values from the GIS feature attribute table.

I can extract the appropriate values from the GIS feature, but cannot seem to execute the SQL statement. I get the following error: "the column cannot contain null values. [,,,Column Name, Table Name,]"

Any guidance you may be able to provide would be much appreciated! Here's what I have so far...

(onformload event)

Sub AutoPop

dim objSelLyr, objSelRS, StrFacilNo, StrStructName

set objSelLyr = Application.Map.SelectionLayer
set objSelRS = objSelLyr.Records

objSelRS.Bookmark = Map.SelectionBookmark

'Get values from structure_existing_area layer
StrFacilNo = objSelRS.Fields("facil_no").Value
StrStructName = objSelRS.Fields("structname").Value

msgbox ("Facility # is " & StrFacilNo & ": " & StrStructName)

Dim DS
Dim SQL
Dim Result

'Set data source
Set DS = objSelLyr.DataSource

'Define SQL statement to insert values into table DA_FORM
SQL = "INSERT INTO DA_FORM (facil_no, facil_type, AXF_STATUS, AXF_TIMESTAMP) VALUES ('StrFacilNo', 'StrStructName', 1, GetDate())"

'Execute SQL command
Result = DS.Execute(SQL)

End Sub

ChrisCzerwinski1 · ‎03-11-2014

Hi Matt,
Did you ever figure out your code for ArcPad 10. I'm trying to achieve a similar procedure but can't get it going.
I know this thread is pretty old, but I figured I would try.

Thanks,
Chris