Adding If Statement within code

[connie]

Hi!

I have the following VBA code that records an audit trail (it looks long but a quick scroll through will give you the gist of it:

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim db As DAO.Database
Dim rs As DAO.Recordset
    Set db = CurrentDb
    Set rs = db.OpenRecordset("tblDatabaseChanges")
    
With rs
      .AddNew
      .Fields("RecordID") = Me.RecordID
      .Fields("Project_Name_Old") = Me.ctlProjectName.OldValue
      .Fields("Major_Dept_Old") = Me.ctlMajorDept.OldValue
      .Fields("Dept_Name_Old") = Me.ctlDeptName.OldValue
      .Fields("Room_Name_Old") = Me.ctlRoomName.OldValue
      .Fields("Room_No_Old") = Me.ctlRoomNo.OldValue
      .Fields("Item_Qty_Old") = Me.ctlItemQty.OldValue
      .Fields("Unit_Cost_Old") = Me.ctlUnitCost.OldValue
      .Fields("Ext_Cost_Old") = Me.ctlExtCost.OldValue
      .Fields("Manufacturer_Old") = Me.ctlManufacturer.OldValue
      .Fields("Model_Old") = Me.ctlModel.OldValue
      .Fields("Phase_Old") = Me.ctlPhase.OldValue
      .Fields("ECR_No_Old") = Me.ctlECRNo.OldValue
      .Fields("CAD_ID_Old") = Me.ctlCADID.OldValue
      .Fields("Description_Old") = Me.ctlDescription.OldValue
      .Fields("Type_Funding_Old") = Me.ctlTypeFunding.OldValue
      .Fields("FI_Old") = Me.ctlFI.OldValue
      .Fields("AC_Old") = Me.ctlAC.OldValue
      .Fields("Last_Edited_by_Old") = Me.ctlLastEditedBy.OldValue
      .Fields("Reason_for_Change_Old") = Me.ctlReasonForChange.OldValue
      .Fields("Project_Name") = Me.ctlProjectName
      .Fields("Major_Dept") = Me.ctlMajorDept
      .Fields("Dept_Name") = Me.ctlDeptName
      .Fields("Room_Name") = Me.ctlRoomName
      .Fields("Room_No") = Me.ctlRoomNo
      .Fields("Item_Qty") = Me.ctlItemQty
      .Fields("Unit_Cost") = Me.ctlUnitCost
      .Fields("Ext_Cost") = Me.ctlExtCost
      .Fields("Manufacturer") = Me.ctlManufacturer
      .Fields("Model") = Me.ctlModel
      .Fields("Phase") = Me.ctlPhase
      .Fields("ECR_No") = Me.ctlECRNo
      .Fields("CAD_ID") = Me.ctlCADID
      .Fields("Description") = Me.ctlDescription
      .Fields("Type_Funding") = Me.ctlTypeFunding
      .Fields("FI") = Me.ctlFI
      .Fields("AC") = Me.ctlAC
      .Fields("Last_Edited_by") = Me.ctlLastEditedBy
      .Fields("Reason_for_Change") = Me.ctlReasonForChange
      .Fields("DateofChange") = Date
      .Fields("Budget_Variance") = Me.ctlExtCost.OldValue - Me.ctlExtCost
      .Update
      .Close
End With
 
  Set db = Nothing
  Set rs = Nothing
End Sub

What I want to do though is have an If statement within that for Budget_Variance, instead of .Fields("Budget_Variance") = Me.ctlExtCost.OldValue - Me.ctlExtCost I need something like:

If IsNull(Me.ctlExtCost.OldValue Then
.Fields("Budget_Variance") = 0 - Me.ctlExtCost
Else .Fields("Budget_Variance") = Me.ctlExtCost.OldValue - Me.ctlExtCost
End If

Because currently if it is a brand new record and there was no OldValue, nothing shows up under Budget_Variance. This way it would subtract the new cost from zero and come up with a negative Budget_Variance. However I am trying to put this in and it is giving me errors; I don't think I'm inserting it correctly within the code or something.

Anyone know how to do this? Thanks!!

 

[namliam]

why not use the nz function?
.Fields("Budget_Variance") = nz(Me.ctlExtCost.OldValue,0) - Me.ctlExtCost

as for your if thing error...

If IsNull(Me.ctlExtCost.OldValue) Then
    .Fields("Budget_Variance") = 0 - Me.ctlExtCost
Else 
    .Fields("Budget_Variance") = Me.ctlExtCost.OldValue - Me.ctlExtCost
End If

You was missing one )
and if then else endif requires seperate lines...

 

[pbaldy]

Well, for starters you haven't closed off the IsNull() function, so that would cause a compile error. What error do you get?

 

[connie]

why not use the nz function?
.Fields("Budget_Variance") = nz(Me.ctlExtCost.OldValue,0) - Me.ctlExtCost

as for your if thing error...

If IsNull(Me.ctlExtCost.OldValue) Then
    .Fields("Budget_Variance") = 0 - Me.ctlExtCost
Else 
    .Fields("Budget_Variance") = Me.ctlExtCost.OldValue - Me.ctlExtCost
End If

You was missing one )
and if then else endif requires seperate lines...

Will that do the same thing? If so, that would work perfectly! I've never used nz before.

As for the missing If parenthesis, I actually just typed that in, I didn't copy it from the code as I had it put in since I have since wiped it out since I couldn't get it to work yesterday. I'm positive the parenthesis was closed at that point...I believe the error I was getting was "Object under cursor not recognized."

 

[namliam]

NZ(field,0) will replace a null value in the field by 0, thus doing what you basicaly want without the IF mess...

 

[connie]

NZ(field,0) will replace a null value in the field by 0, thus doing what you basicaly want without the IF mess...

Perfect! Thank you so much!!