Form not saving changes to records

[RichO]

There is probably a simple solution to this but I don't know exactly what keywords to search the forum for.

I have a form with continuous records that gets its data directly from a table. Anytime you move around the form making changes to the records on the screen, you have to move the cursor to a different record or the changes do not stick if you exit the form. Obviously it does not save the changes until you exit the current record.

In this same form there is a time field and a text box with a total time calculation of all the time fields in the form. Any time you change the time field of any record, the total time calculation does not update correctly until you move to another record. Obviously this is related to the above problem that changes are not saved until you move to another record.

Is there a way around this? A couple of users at work are continually confused by this because they forget to exit the current record and their changes are not saved.


Thanks

 

[Pat Hartman]

Unless you have written code to prevent it, every record is being saved as long as there are no errors. Check your code. It may be masking an error message. If you are using a subform, make sure that the master/child links are properly set. It is possible that if the master/child links are improperly set, the foreign key in the many-side table may not be properly set so that although the rows are being saved, they are lost because they are not properly linked to their parents.

 

[RichO]

OK, it does save the changes, but only after exiting the form. While you are still in the form, the changes are not applied to the table until you move to another record.

The reason this is a problem is that this form actually appends its current data to another table, and if the append is executed after a change is made to a field, but without moving to another record, the appended data does not reflect the change.

I have attached a scaled down model of this form (Access 2000).

If you change a field and tab to the next field in the same record, then minimize the form and view the table, you'll see that the change has not yet taken effect. You'll also see that the calculation for total minutes is done in the form after update event because if you do it in the length after update event, it does not calculate correctly because the underlying table has not been updated until you move to another record.

Is there any way to fix this so the fields update in the table immediately?

The guy who originally developed this database was pretty fluent in Access programming and he chose to do it this way, so I'm curious.

Thanks for your help.

 

[neileg]

I'm curious as to why someone who is 'fluent in Access' should allow the user to enter data in a way that causes the code to simply crash. Try entering a time value where the decimal part is over .59 to see. I don't understand why they didn't use a date/time field which would give inbuilt validation?

The code that calculates the total time is in the After Update event, so that means that the calculation is not done until the record is saved, either with an explicit save or an automatic save by moving to another record.

If the length field had been a date/time then the total time value would be a simple =sum([Length]) with no problems unless the total time exceeds 24 hours. This would have the same characteristics.

You could use Me.Refresh in the On Lost Focus event of the Length field to improve matters, but the user is still going to have to move off the field for it to retotal the times.

 

[Pat Hartman]

The record is not saved after every field is entered and certainly not while the cursor is still in a field you just changed. To force a record to be saved, you will need to add a button to your form that the user can press to force the save without moving off the current record. The button code is:

DoCmd.RunCommand acCmdSaveRecord

You can use the button wizard to build the code but I suggest you change the generated save instruction (leave the error trapping code) to the one I suggested. Despite the fact that Microsoft has recommended against using the code generated by its wizards since A2K, it still generates the bad code even in AXP.

 

[RichO]

That did the trick. Thanks Pat.

 

[wazz]

You can use the button wizard to build the code but I suggest you change the generated save instruction (leave the error trapping code) to the one I suggested. Despite the fact that Microsoft has recommended against using the code generated by its wizards since A2K, it still generates the bad code even in AXP.

Against!? ( !!!) time to make some changes...
tnx.
w

P.S.
is there anything wrong with doing this?


Public Function SaveRecord()
On Error GoTo Err_SaveRecord

DoCmd.RunCommand acCmdSaveRecord

Exit_SaveRecord:
Exit Function

Err_SaveRecord:
MsgBox Err.Description
Resume Exit_SaveRecord

End Function

and calling SaveRecord as needed?

tnx.

 

[Pat Hartman]

There isn't anything wrong with it per se but what does it save you? It is only a single instruction and the sub you call it from should have error trapping code anyway.