Field Calculator using input from a form in VBA

[JessicaKilpatrick]

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

[JessicaKilpatrick]

I have come across a fix for my issue, I am not sure that it is the most efficient or effective way of solving the problem but it is working.  I had to move the form from the map document template to the Normal template because of how the field calculator was conducting the calculation.  I also changed my code for .Expression equal to a variable and then set .PreExpression to my criteria.  Hope this will help for anyone also having this issue.  However if there is a better way to go about this please don't hesitate to let me know.  Thanks. 

Here is what i changed the field calculator expression to:

With pCalc
      Set .Cursor = pCursor
      'Pre Expression for the Sundays in the month of July ONLY
      .PreExpression = "Dim adtfactor as double" & vbNewLine & _
                       "If DatePart(""yyyy"", [Date_Cnt]) = frmADTAdjustment2.txtYearJUL.Text And DatePart(""m"", [Date_Cnt]) = 7 And DatePart(""w"", [Date_Cnt]) = 1 Then adtfactor = frmADTAdjustment2.txtSun7.Text"
      .Expression = "adtfactor"
      .Field = "AF"
   End With
  pCalc.Calculate