Write conflict access subforms

Hello All,

I have a form that has 5 subforms.  The main form is just a housing to do things like search via a combo box and it takes you to that record.  The subforms are different parts of each record.  3 of the subforms all refer to the same table, just different parts of the data in each subform.  The main form links with the subforms so no matter what subform you are workng on it stays on th same record, until you switch to a different record.  Recently, subform 2 has popped up the write conflict error when user trys to go to another record after change was made to a field.  If i select copy to clipboard, the data is from subform 1.  It's almost like the first subform is holdng the data open and not allowing the 2nd subform to enter anything.  If I open any of the subforms without the parent, they work flawlessly.  I have searched for an answer to this for about 8 hours now and am a little frustrated to say the least.  I'm self taught on VBA so please be gentle.

Many thanks!
jklaeneAsked:
Who is Participating?
 
harfangConnect With a Mentor Commented:
I think this works:

Age: DateDiff('yyyy', BirthDate, Now()) + (DateSerial(Year(Now()), Month(BirthDate), Day[BirthDate)) > Now())

or: "difference in years, minus one if this year's birthday is in the future" [because True is represented as minus one, False as zero].

Cheers!
(°v°)
0
 
harfangCommented:
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°)
0
 
jklaeneAuthor Commented:
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!
0
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
harfangCommented:
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°)
0
 
jklaeneAuthor Commented:
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!
0
 
jklaeneAuthor Commented:
Private Sub Form_Current()
    'If [Age] < 18 Then
    '    [Minor] = -1
    'Else
    '    [Minor] = 0
    'End If
End Sub
0
 
harfangCommented:
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°)
0
 
jklaeneAuthor Commented:
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()))
0
 
harfangCommented:
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°)
0
 
jklaeneAuthor Commented:
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.
0
 
jklaeneAuthor Commented:
many thanks harfang!
0
 
harfangCommented:
Welcome, and good luck with your project!
(°v°)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.