Calculated Field

[exoscoriae]

Hello all.

I have several fields that are calculated from other fields. They are very simple calculations.

My research has repeatedly shown that in an Access form, if I type in Me.Field1 = (Me.Field2 * Me.Field3) in the 'After Update" section, then anytime Field2 or Field3 is updated, this could should run. Other sites say the update will run anytime someone hits ENTER, TAB, or moves to another record.

I can not get this to work at all - as the field never calculates, no matter how many changes I make. The calculation DOES work however if I place the calculation as the control source of an unbound box.

In a bound box however, I can not seem to get this to work.

I know it is often discouraged to store calculated values, although the values need to be written to the table, as another script comes and takes data from the table to populate a GIS shapefile. I'd rather have the data read directly from this database, then have to write scripts on the GIS side to calculate these values a second time.

Thank you for your time =)

 

[exoscoriae]

Update:
I found that if I specifically call the 'after update' sub function in Field1 from Field2 or Field3, then it will update.

The problem I now have is that I have code for my form under 'Before Update' that checks to see if the form is 'dirty', and then prompts the user to save. This was a request from the user, as they did not want auto saving to occur. The save code seems to repeatedly see the form as dirty, even after saving - creating an endless loop of "Do you want to Save?

 

[exoscoriae]

Update2 (no edit function?):

I moved the sub call to right before it checks to see if the form is dirty, which makes the calculated value part of the me.dirty = true statement, rather then an after effect of it.

This solves my problem!

The only way this could be improved now is if those fields were calculated anytime the record is viewed, and not just when changes are made. Anyway to do this?

 

[exoscoriae]

I find that calling the calculation in the "On Current" portion of the form properties does calculate the field every single time the record is viewed.... unfortunately, even if the value i the same as it previously was - it flags the form as dirty and prompts for a save every single time.

I apologize that my original problem has changed so many times since my first post. I'm really working in the dark here, so I keep making a little progress, which then pops another problem up.

I suppose now I need to find a way to autocalc values without flagging the form as dirty each time, or a smarter code to checks if the form is dirty (ie: doesn't take into account calculated values).

 

[missinglinq]

To be honest, I've gotten a headache trying to followyour conversation with yourself. Keeping in mind that in most cases you shouldn't store calculated values, if you absolutely have to, this will allow Field1 to be stored.

Private Sub Field2_AfterUpdate()
If Not IsNull(Me.Field2) And Not IsNull(Me.Field3) Then
 Me.Field1 = Me.Field2 * Me.Field3
Else
 Me.Field1 = Null
End If
End Sub

Private Sub Field3_AfterUpdate()
If Not IsNull(Me.Field2) And Not IsNull(Me.Field3) Then
 Me.Field1 = Me.Field2 * Me.Field3
Else
 Me.Field1 = Null
End If
End Sub

Here's a Yes/No routine that should work for saving/not saving a new record

Private Sub Form_BeforeUpdate(Cancel As Integer)
If Me.NewRecord Then
If MsgBox("Would You Like To Save This Record?", vbQuestion + vbYesNo + vbDefaultButton1, "Save This Record ???") = vbNo Then
  Me.Undo
Else
End If
End If
End Sub

and the same routine to check new records or exiting records that have been edited

Private Sub Form_BeforeUpdate(Cancel As Integer)
 resp = MsgBox("Do you wish to save this record?", vbYesNo)
  If resp = vbNo Then
  Me.Undo
  End If
End Sub

The Missinglinq

Richmond, Virginia

There's ALWAYS more than one way to skin a cat!

 

[missinglinq]

Forgot to add, be sure to remove everything else you've done to date for this, especially whatever you have in the ControlSource of Field1.

The Missinglinq

Richmond, Virginia

There's ALWAYS more than one way to skin a cat!

 

[exoscoriae]

Thank you for your time and bearing with my thread.