Solved

Write conflict access subforms

Posted on 2006-11-12
12
603 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
  • 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
 

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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

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 500 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 Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…

757 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now