 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Forms / March 2007Me.Dirty - Where to put this logic?
Okay, Here is the setup of this monster database that we're using. We have an Access frontend with SQL server backend. We have about 14 tables that all link to one main table via main table's PK. To present this data, we have forms that have 14 sub-forms on them (long story, but in order to get what we needed, this was how we got it to work). We have the SQL server residing on one server and then six laptops connect to that server via wireless router (again, due to the mobility of the laptops, it has to be wireless). So at one time, there are six users entering data, but never working on the same person at the same time. Lately we've been experiencing, what seems to be a common error, the write conflict error where the only options are Save, Copy to Clipboard or Drop Changes. With me so far? Okay, so it is my understanding that as a user tabs through the form and fills out the appropriate text boxes, check boxes, and option buttons, the record does not save until the form is closed, correct? This is when the record is considered "dirty," it's there but not written to SQL. In doing some research, it looks like I need to utilize the following logic: If Me.Dirty = True Then Me.Dirty = False End If My question is where to do I put this? Do I put in in the OnExit event of the subform? Does it go in each AfterUpdate event for each control on the form (this would be ridiculous (there's thousands))? Does it go in the AfterUpdate event of the main form? With this setup, I can set many places to put it, I just need to make sure it's in the right spot so the error goes away. Please let me know. Thanks in advance! Hi, Basically, you are saying that you need to have force a save... Have you considered simply having a "Save" button, that has the following code in the on click event: docmd.runcommand accmdsaverecord By definition, Access saves the record when the record changes, so putting it in the after update event for the form won't help. If your form is so astoundingly large that the users are taking too long to reach the save point, you could consider having the same code run somewhere else along the way. Hope this helps. Damian. > Okay, > [quoted text clipped - 28 lines] > > Thanks in advance! > Okay, > [quoted text clipped - 28 lines] > setup, I can set many places to put it, I just need to make sure it's > in the right spot so the error goes away. Please let me know. When the focus changes from the main form to a subform, the main form's record will be saved automatically if the form is dirty. When the focus changes from a subform to the main form, the subform's record will be saved automatically, if the subform is dirty. So there's no point in trying to save records as you switch between main form and subform. Is it taking the users a long time to fill out the form or subform, so that the record remains dirty for a long time? You say that the users are never "working on the same person at the same time." Given that you have a SQL Server back-end, I'm not sure why you're getting write conflicts, unless "working on a person" involves updating records that may be in common between two "persons". That is, unless your problem isn't really one of true write conflicts, but rather a configuration or data problem that is causing Access to *think* there's a write conflict when there really isn't. Do you get these conflict messages all the time, or only occasionally? Do they really seem to correspond to situations where users are updating the same records?  SignatureDirk Goldgar, MS Access MVP www.datagnostics.com (please reply to the newsgroup) It wouldn't surprise me if it is taking users a long time to fill out the form. Not because the form is too long or complicated, it is because they are doing health screenings of kids while they're entering the data. When we first started to get the error, it was sporratic. Then when we shut down the six computer and just worked on the application that is on the server we even got the error. Since each kids goes to a different station, the record dealing with that kid is only open at one time. I don't think there would be any commonality between the users and the records they have pulled up. I've tried including the timestamp that has been suggested in a lot of forums and this change doesn't seem to resolve the write conflict error either. I do not have any fields that are bit size. Is there anything else that I am missing? I do have memo fields, but I really do not want to have to break those down to seperate tables or change them to text fields. We get a lot of school nurses who can be wordy and the memo field helps. We've received the error when opening up a form (i've incorporated a search feature that returns results in a list box - user can double click on the record or select the open form button to open it up) and we've seen the error when a user was trying to enter data. I'm stumped! I've even seen forums where they say this is a known bug for Access and to deal with it, however, I would really like to exhaust all outlets before concluding that. Would the wireless connection play a part? Would it help to be wired? I feel like I'm grasping for straws. :-) >> Okay, >> [quoted text clipped - 19 lines] >time, or only occasionally? Do they really seem to correspond to >situations where users are updating the same records? Would splitting the database help? I've never done this before, is the process easy? >It wouldn't surprise me if it is taking users a long time to fill out the >form. Not because the form is too long or complicated, it is because they [quoted text clipped - 27 lines] >>time, or only occasionally? Do they really seem to correspond to >>situations where users are updating the same records? I guess my first question in regards to splitting a database, is does this work if you have a SQL BE? >Would splitting the database help? I've never done this before, is the >process easy? [quoted text clipped - 4 lines] >>>time, or only occasionally? Do they really seem to correspond to >>>situations where users are updating the same records? > Would splitting the database help? I've never done this before, is > the process easy? If you have a SQL Server back-end, you're already split. Now, if every user is logging into the same Access front-end, which resides on a network share, you aren't taking full advantage of the front-end/back-end arrangement. Is that what you're doing? Normally, each user would have her own copy of the front-end, installed on her local PC. It's possible, of course, that you aren't *fully* split. Does the Access front-end contain local tables with shared data? It's not unusual to have user-specific information in the front-end, since each user would have a separate copy of the front-end. But if there's non-user-specific data in the front-end, you would need to migrate that to the back-end before giving each user her own copy of the front-end. It occurs to me that, if you have all your users using the same copy of the front-end, that may have something to do with the write-conflict problem you're experiencing. But it's just conjecture at this point. SignatureDirk Goldgar, MS Access MVP www.datagnostics.com (please reply to the newsgroup) > We've received the error when opening up a form (i've incorporated a > search feature that returns results in a list box - user can double > click on the record or select the open form button to open it up) and > we've seen the error when a user was trying to enter data. I'm > stumped! Is it that you are opening popup forms to enter detail information while editing the basic information on the main form? That could cause the problem, as you'd be in conflict with yourself. > I've even seen forums where they say this is a known bug for Access > and to deal with it I don't know of any such "known bug" except with regard to needing timestamp fields, not trying to save Null bit fields, etc. Those are known issues, with specific cures. > outlets before concluding that. Would the wireless connection play a > part? Would it help to be wired? I don't think so. It could be that your application design is causing the problem, but it's hard to say without pinning down one or more reproducible scenarios for triggering the conflict. SignatureDirk Goldgar, MS Access MVP www.datagnostics.com (please reply to the newsgroup) |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.