Using 1-to-1 to eliminate "This record has been changed"?

[bassman197]

Hello all,
I have inherited as part of my job a small database that writes contracts, addendums and invoices. ONE contract will normally have only ONE addemdum, so the fields for these two documents are all in the same table - makes sense. Except, our people often like to have both the contract and addendum open at the same time (the contract open for reference as they are composing the addendum). If they edit the contract, compose the addendum and then see one more thing that needs editing in the contract - bam! The dreaded "This record has been changed by another user" message. The other user is simply the second form causing the user to conflict with themselves. I would like to eliminate the possiblility of this message cropping us, since selecting "save record" once caused a major lock up (since both forms were open?).

With my fledgling Access skills, the ways I can think of to do this are:
1) save record command on the "on click" for the buttons that open and close the forms somehow? I tried this once and Access got hung up - maybe I put the code in the wrong place?
2) use VBA to open and close the forms as they pass the record off to one another, but I don't want to do this - I really want the users to be able to have both forms open with impunity!
3) dirty=false? I know of this, but not sure how to implement it, or if it would work with both forms open.
4) ISOLATE all of the fields unique to the addendum in a separate table and create a one to one relationship with the contract table, LOCKING all of the fields on the addendum that are simply showing related contract info so users can write only in the fields unique to the new addendum table. I know that one to one is used rarely, but in this case, maybe it's the way to go? Any thoughts would be appreciated!

 

[HiTechCoach]

Are both form's editing the same data?

If they are, this is the issue.

You will need to rethink/redesign the UI to avoid this.

Why are you using two forms? I would probably use one form with a tab control to show all the fields. This would avoid the issue.

 

[bassman197]

I have to admit, I never though of that! I've been a user of the program for years and only recently have inherited it's maintenance, so I'm seeing the issue through a paradigm!

Most all of the pages could be put on tabs: Contract, Addendum, Invoice - even pages with a one to many relationship (ONE client contact may have MANY vendor contracts). I am confused about how to integrate this into some of the existing design. The switchboard contains many search functions, which simply open single dialog forms with a text box and a button. They look for matching data and were created with the "open a form and find information to display there" option in the command button wizard. If all of the forms that these searches now open were moved to tabs in a parent form, would the search commands still work, or would I have to change the searches to reference the parent form, then which tab? Maybe I'm over thinking this?

PS: Thanks for the idea and sorry for the delayed reply - I didn't get an email alert like a sometime do!

Are both form's editing the same data?

If they are, this is the issue.

You will need to rethink/redesign the UI to avoid this.

Why are you using two forms? I would probably use one form with a tab control to show all the fields. This would avoid the issue.

 

[evanscamman]

or...

if Me.Dirty then me.dirty = false

This triggers the save event and works like a charm

 

[bassman197]

Thanks for the code ideas - I tried something like these and it didn't work, but I'm wondering if I was just asking Access to do the impossible. When the code is associated with the button to open another form, it works great but... now both forms are still sitting open. The temptation is for users to graze back and forth between them (no more buttons involved, so no new code to implement) and then the error message comes. What I tried, as I recall, was putting similar code on each form under "on lost focus". The idea was that as the user switched from one form to the other, Access would just keep updating, but I got a different error message - wish I had saved it or a screen shot of it. In essence, though, it was probably saying "I can't save right now because more than one form based on this record is open at the same time", which is exactly what I was trying to solve! I'm starting to think the tab approach (force users not to have two forms open at once) is the only way...?