Calculated fields and Data Entry (1 Viewer)

[Russp42]

I have a form based on a query which has 5 fields from a table and 4 calculated fields. I have included the calculated fields in the query rather than on the form itself as I want to add additional calculated fields on the form to total the calculated fields from the query.(In the Form Footer) The table has an ID Field, 3 fields with static values, 1 field where I enter data [Score] and has 18 rows. The form has 8 columns but I am only entering data into 1 column [Score] so rather than enter through all the fields I have set the property on the other columns to "Enabled No" and "Locked Yes" This works fine until I get to the last row where the data does not update as the cursor does not go past the [Score] field. It will update by going back to the previous record or by doing a refresh. The problem with the refresh is that it closes the form. The form is opened by a message box which appears because I have used a field in one of the calculated fields that does not exist but calls for a value. I cannot add another row to the table as the calculated fields are looking for a value and creates an error on that line which creates errors in the "Total" fields. Also I would like the form to open with zero or blank values instead of having to overwrite values previously entered.

 

[isladogs]

I got lost reading all of that...
Why do you need to set Enabled = No? Locking the fields other than Score will be enough.
Will that fix your issue?
Why does Refresh close the form? Instead try using Me.Recalc after the Score field is updated

I'm sure the message box problem could be eliminated too but, by now, its beginning to sound like you are trying to patch up a bad design.

 

[Russp42]

I got lost reading all of that...
Why do you need to set Enabled = No? Locking the fields other than Score will be enough.
Will that fix your issue?
Why does Refresh close the form? Instead try using Me.Recalc after the Score field is updated

I'm sure the message box problem could be eliminated too but, by now, its beginning to sound like you are trying to patch up a bad design.

I am in my mid 70s and have no Programming training so don't know whether it is a bad design or not. What I do know is that all the calculations work and give me the correct answer apart from the bottom row. Changing Enabled to Yes does does not fix. Having enabled set to No saves entering through 7 fields to get to my 1 data entry field.

 

[June7]

Refresh or Requery should not cause form to close.

Perhaps code in Score textbox AfterUpdate event to commit record. Simply:

Sub Score_AfterUpdate()
Me.Dirty = False
End Sub

Instead of Enabled property set TabStop to no.

 

[isladogs]

Changing Enabled to Yes does does not fix. Having enabled set to No saves entering through 7 fields to get to my 1 data entry field.

You could also just change the tab order so the Score control is first in the list.
You can then re-enable the other controls and shouldn't have a problem with the last record

 

[June7]

But if still don't want to tab through each control to get to next row, set TabStop to no and won't matter where Score is positioned.

 

[Pat Hartman]

Rather than setting the enabled property, it would be more appropriate to set the tab stop property. You would set the enabled property to No when you did not want anyone to modify the data. What you are saying is that you just don't want to tab through the fields so the best solution is the one that is least restrictive. If someone clicks into a control, they can modify it. If they use the tab key, the tab won't stop in any control where the tab stop is set to no.

Access saves a dirty record under a number of conditions. The one you are relying on is that focus moves to a new record causing the previous record to be saved. Because you are restricting movement, the focus is not moving to a new record.

Rather than using .Refresh, .Requery, or .Recalc, it is best to use the command INTENDED to do one and ONLY one thing and that is to save the current record.

If Me.Dirty = True Then
    DoCmd.RunCommand acCmdSaveRecord
End If

You can add the above code to a button and that will force the record to be saved. Keep in mind that saving the form will also save the record. Access takes very good care of saving dirty records for you. The only reason to force a record to save this way is because you want the record to save but still stay visible.

 

[Russp42]

Refresh or Requery should not cause form to close.

Perhaps code in Score textbox AfterUpdate event to commit record. Simply:

Sub Score_AfterUpdate()
Me.Dirty = False
End Sub

Instead of Enabled property set TabStop to no.

Works a treat. Thanks