[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Write conflict access subforms

Posted on 2006-11-12
12
Medium Priority
?
631 Views
Last Modified: 2008-01-09
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!
0
Comment
Question by:jklaene
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 6
12 Comments
 
LVL 58

Expert Comment

by:harfang
ID: 17927493
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
 

Author Comment

by:jklaene
ID: 17927582
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
 
LVL 58

Expert Comment

by:harfang
ID: 17927674
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:jklaene
ID: 17927831
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
 

Author Comment

by:jklaene
ID: 17927833
Private Sub Form_Current()
    'If [Age] < 18 Then
    '    [Minor] = -1
    'Else
    '    [Minor] = 0
    'End If
End Sub
0
 
LVL 58

Expert Comment

by:harfang
ID: 17927874
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
 

Author Comment

by:jklaene
ID: 17927954
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
 
LVL 58

Expert Comment

by:harfang
ID: 17928016
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
 

Author Comment

by:jklaene
ID: 17928043
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
 
LVL 58

Accepted Solution

by:
harfang earned 2000 total points
ID: 17930696
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
 

Author Comment

by:jklaene
ID: 17934272
many thanks harfang!
0
 
LVL 58

Expert Comment

by:harfang
ID: 17934323
Welcome, and good luck with your project!
(°v°)
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

649 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question