Updating issues - HELP!

I have two subforms which are both based upon my Contact Table. I have the second form linked to the first form on the ContactID Field. Simple enough. I have written some code that runs an Update Query on Form 1 changing some information on the linked form (Form 2) on the AfterUpdate event.

The update query works but when I try to go to a new record, Access messages me that another user has updated my data and I have to Save Changes, Copy to Clipboard, etc. If I Save Changes, it seems to corrupt the underlying record.

Anyway, I'm not sure why what I'm doing causes this problem.

Can someone help me out?

You can't use the same table on both forms. It seems like you may have some
data fields split; one to go to the 1st subform and the others attached to
the other subform. This will never work.

What are your trying to do, perhaps we can recommend a better way?

"Bill Mitchell" wrote in message
...
I have two subforms which are both based upon my Contact Table. I have
the second form linked to the first form on the ContactID Field. Simple
enough. I have written some code that runs an Update Query on Form 1
changing some information on the linked form (Form 2) on the AfterUpdate
event.

The update query works but when I try to go to a new record, Access
messages me that another user has updated my data and I have to Save
Changes, Copy to Clipboard, etc. If I Save Changes, it seems to corrupt the
underlying record.

Anyway, I'm not sure why what I'm doing causes this problem.

Can someone help me out?

On Mon, 14 Jun 2004 17:20:01 -0700, "Bill Mitchell"
wrote:

I have two subforms which are both based upon my Contact Table. I have the second form linked to the first form on the ContactID Field. Simple enough. I have written some code that runs an Update Query on Form 1 changing some information on the linked form (Form 2) on the AfterUpdate event.

The update query works but when I try to go to a new record, Access messages me that another user has updated my data and I have to Save Changes, Copy to Clipboard, etc. If I Save Changes, it seems to corrupt the underlying record.

Anyway, I'm not sure why what I'm doing causes this problem.

Can someone help me out?

The other user *is you*.

If you have a record open on a Form, and then use code or a query to
update the same record, you have a conflict. Your update query isn't
changing data *on the form* of course - it's changing data in the
table, and the form is just trying to reflect those changes.

Could you explain what you're trying to accomplish? What are the
Recordsources of the two forms, and the code?

John W. Vinson[MVP]
Come for live chats every Tuesday and Thursday
http://go.compuserve.com/msdevapps?loc=us&access=public

Yes, I thought this might be the problem.

I have a field in my Contact Table that I call ContactScratchpad. It's just a memo field that acts as an archive for notes I want to enter about a contact. In addition, I have several Notes fields throughout each Contact's record. Every time I add new informattion to one of these Notes Fields I am also running some Update SQL to append the new Notes to the end of the Scratchpad field.

I am guessing that I could just make the Scratchpad Field part of another table. When I run the update query I could test for the existence of a record linked to my current contact. If it exists, it will update the record. If not, It will append a new record.

Oh well, I was just looking for an easier way. Thanks for your input.

"John Vinson" wrote:

On Mon, 14 Jun 2004 17:20:01 -0700, "Bill Mitchell"
wrote:

I have two subforms which are both based upon my Contact Table. I have the second form linked to the first form on the ContactID Field. Simple enough. I have written some code that runs an Update Query on Form 1 changing some information on the linked form (Form 2) on the AfterUpdate event.

The update query works but when I try to go to a new record, Access messages me that another user has updated my data and I have to Save Changes, Copy to Clipboard, etc. If I Save Changes, it seems to corrupt the underlying record.

Anyway, I'm not sure why what I'm doing causes this problem.

Can someone help me out?

The other user *is you*.

If you have a record open on a Form, and then use code or a query to
update the same record, you have a conflict. Your update query isn't
changing data *on the form* of course - it's changing data in the
table, and the form is just trying to reflect those changes.

Could you explain what you're trying to accomplish? What are the
Recordsources of the two forms, and the code?

John W. Vinson[MVP]
Come for live chats every Tuesday and Thursday
http://go.compuserve.com/msdevapps?loc=us&access=public

On Mon, 14 Jun 2004 18:50:01 -0700, "Bill Mitchell"
wrote:

I have a field in my Contact Table that I call ContactScratchpad. It's just a memo field that acts as an archive for notes I want to enter about a contact. In addition, I have several Notes fields throughout each Contact's record. Every time I add new informattion to one of these Notes Fields I am also running some Update SQL to append the new Notes to the end of the Scratchpad field.

So the problem is arising because you're storing data redundantly?
Well, then DON'T.

You can always create a Query to concatenate the various note fields.
Storing an extra memo field is not necessary, and it at least slightly
elevates the risk of corrupting your database.

John W. Vinson[MVP]
Come for live chats every Tuesday and Thursday
http://go.compuserve.com/msdevapps?loc=us&access=public