lostinkansas

Field Calculator using input from a form in VBA

Discussion created by lostinkansas on Jun 28, 2010
Latest reply on Jun 29, 2010 by lostinkansas
I have created a form for a user to input an adjustment factor table.  It allows the user to input a year for each month as well as the given adjustment factor, which is dependent on the day of the week in in that specific month.

so it looks something like this

January    [year] [mon] [tues] [wed] [ect...]
February   [year] [mon] [tues] [wed] [ect...]
ect..

Then at the bottom is a command button to store the entered values in the Adjustment table.(Which is a table that has two fields Date, which already stores all the individual days in the year, and Adjustment)  I want the command button at the bottom to run a field calculation checking the year and the day of week from the date field then using the correctly selected value entered in the form as the value for the Adjustment field. So really it is just an elaborate if then statement.  I just dont know how to make the field calculator look at the form for a value. 

Here is what i have already.  I know the syntax for the .Expression is wrong but i am not sure how to make it work.

Private Sub cmdSave_Click()

  On Error GoTo EH
  
  Dim pDoc As IMxDocument
  Set pDoc = ThisDocument

  'set table to ADT_Adjustment  **must be the top table in the Table of Contents
  
  Dim pTc As ITableCollection
  Set pTc = pDoc.FocusMap
  Dim pTable As ITable
  Set pTable = pTc.Table(0)
  
  ' This calculation is to be done outside of an edit session.
  Dim pEditor As IEditor
  Dim pID As New UID
  pID = "esriCore.Editor"
  Set pEditor = Application.FindExtensionByCLSID(pID)
  If pEditor.EditState = esriStateEditing Then
    MsgBox "This requires that ArcMap is not in edit mode"
    Exit Sub
  End If
  
  ' Find the field named AF
  Dim pCalc As ICalculator
  Dim pField As IField
  Dim intFldIndex As Integer
  intFldIndex = pTable.FindField("AF")
  If intFldIndex = -1 Then
    MsgBox "There must be a field named AF in the layer"
    Exit Sub
  End If
  
  ' Perform the calculation.
  Dim pCursor As ICursor
  Set pCalc = New Calculator
  Set pCursor = pTable.Update(Nothing, True)
      
   With pCalc
      Set .Cursor = pCursor
      .Expression =
        If DatePart("yyyy", pTable.FindField("Date_Cnt")) = frmADTAdjustment.txtYearJAN.Text And DatePart("m", pTable.FindField("Date_Cnt")) = 1 And DatePart("w", pTable.FindField("Date_Cnt")) = 1 Then preExp = frmADTAdjustment.txtMon1.Text
            ElseIf DatePart("yyyy", pTable.FindField("Date_Cnt")) = frmADTAdjustment.txtYearJAN.Text And DatePart("m", pTable.FindField("Date_Cnt")) = 1 And DatePart("w", pTable.FindField("Date_Cnt")) = 2 Then preExp = frmADTAdjustment.txtTues1.Text
            ElseIf DatePart("yyyy", pTable.FindField("Date_Cnt")) = frmADTAdjustment.txtYearJAN.Text And DatePart("m", pTable.FindField("Date_Cnt")) = 1 And DatePart("w", pTable.FindField("Date_Cnt")) = 3 Then preExp = frmADTAdjustment.txtWed1.Text
            ElseIf DatePart("yyyy", pTable.FindField("Date_Cnt")) = frmADTAdjustment.txtYearJAN.Text And DatePart("m", pTable.FindField("Date_Cnt")) = 1 And DatePart("w", pTable.FindField("Date_Cnt")) = 4 Then preExp = frmADTAdjustment.txtThur1.Text
            EsleIf DatePart("yyyy", pTable.FindField("Date_Cnt")) = frmADTAdjustment.txtYearJAN.Text And DatePart("m", pTable.FindField("Date_Cnt")) = 1 And DatePart("w", pTable.FindField("Date_Cnt")) = 5 Then preExp = frmADTAdjustment.txtFri1.Text
       Else
       End If
      .Field = "AF"
   End With
  pCalc.Calculate

  Exit Sub
EH:
   
  MsgBox Err.Number & "  " & Err.Description

End Sub


Any help would be greatly appreciated.  Thanks in advance!

-LostinKansas

Outcomes