Write conflict access subforms

Hello jklaene

This is a tricky setup. Normally, Access will automatically save a subform's record when leaving the subform control. Thus, you can normally edit the same record using different subforms, as you do in your setup.

However, the save can fail. Depending on your validation rules, Before_Update code and your error handling strategy, a record can remain unsaved in a subform, even silently. This can also happen from VB, if you use a recordset to edit data.

To explore the problem, show the record selector on your subforms. This will help you see whether a record is saved, currently edited, or locked by another object. You can also examine the save state in the subform control's exit event: subForm.Form.Dirty

A more general solution would be to avoid this setup alltogether (I always do). Instead, consider:

* Using the same recordset for all subforms meant for the same table. This is a single line of VB on opening the form:

    Set subFormA.Form.RecordSet = subFormB.Form.RecordSet

* Use a single subform, with a tab-control to show different sets of fields. This allows global validation of all fields, for instance, and interactions between them.

Happy debugging!

(°v°)

harfang,

ok, I added code (DoCmd.RunCommand acCmdSaveRecord) to the AFTER UPDATE event to all subforms and the parent form.  I now get the message 50% of the time.  noticed that even after i update a field, the record selector still shows the pencil as if unedited even though it should be savng after update.  should i save the record after every field independently?  I need to have the subforms with the pages.  It worked fine before and all I did was convert some macros to VBA and some other small tweaks.  Some records have tiny squares in the fields, would that have anything to do with it?  thanks again!

jklaene

My guess is that some of your code is updating data in the subforms. If you remove all VB code, the problem would certainly go away. Incidentally, saving the record in the AfterUpdate event of the form should have either no effect or create an infinite loop. If you mean the AfterUpdate of individual controls, then it should save the record, and remove the pencil.

Another indication are the tiny squares. These are amost impossible to insert through the keyboard and often represent broken CR/LF pairs (otherwise, they are extended unicode characters). They are a clear indication that the data has either been imported with them or that some lines of code are actually modifying the data. In view of the other problems, I suspect the latter.

You could perhaps disactivate all your VB code by inserting "Exit Sub" at the top of each, verify that the problem is gone, and then reactivate them one by one.

Good luck!
(°v°)

yea!  why didnt i think of that?  my brain was fried.... i have a field that needs updated every time the user views a record.  it basically determines their age and automatically checks a box if minor or not.  when i took that code out, the write conflict went away but now i moved that code to after update (before it was on current) and the record makes the change but immediately hangs and wont let you do anything.  this was my first question ive ever asked, so how do i give you points...just hit accept?

thanks!

Private Sub Form_Current()
    'If [Age] < 18 Then
    '    [Minor] = -1
    'Else
    '    [Minor] = 0
    'End If
End Sub

Well, you shouldn't make changes to a  record on AfterUpdate, because that will update it again and fire the AfterUpdate, and... freeze.

You can move the code to BeforeUpdate of a record, or, naturally, use an expression to calculate the "minor" flag. There is no real need to use a "minor" field, is there, or even the Age (supposing you have a DOB field and today's date).

As for closing the question, once you are satified with the answer, you can simply click the accept button from the most useful comment. If you want to accept several answers of different experts, you must then use the "split points" link (not a button) and follow instructions on the following screen.

But please feel free to pursue the issue with [Age] and [Minor] a bit further.

Cheers!
(°v°)

ok...if you dont mind...there is a check box [Minor] and a text box [Age].  the text box figures the age based on a DOB field.  the code is to check the box based on that age formula.  the problem is i need it to check that formula everytime they view an employee because at some point they wont be a minor any more and the check box will have to change as a result.  where do you suggest to put the code?  here's the formula i'm using to get the age: =IIf(DatePart("m",Now())<DatePart("m",[Birthdate]),DateDiff("yyyy",[Birthdate],Now())-1,DateDiff("yyyy",[Birthdate],Now()))

I would put this in the query. Change the record source to a query or edit the current query, make sure to add all the fields (use the *) and then add two calculated columns:

    Age: IIf(... your formula ...)
    Minor: Age<18

Then you can display the non-editable calculated age in a textbox and the calculated Minor boolean value as a checkbox. Both calculated fields can further be used for calculations, etc.

Note that your formula calculates age on a monthly base, no a daily base. If you don't need the actual age, you could use:

    Minor: DateAdd('yyyy', 18, BirthDate) > Now()

Finally, this can be used as a controlsource for a checkbox as well, with the equal sign as you did for the age.

Good luck!
(°v°)

I see.  I couldn't figure out how to get the age to calculate on a daily basis to show the true age, that's why it does it by month.  I know it seems silly, but I couldn't figure it out.  LOL  If you could give me age code to figure the true age, that would be awesome and then I will leave ya alone.

many thanks harfang!

Welcome, and good luck with your project!
(°v°)