before update event

[TAMSIN]

Hi,

On a bound form, I'm trapping the before update event, and using ADO to send an update query to the database (just using access as the front end here).
To stop Access trying to do the update itself, I've got

Cancel = True

at the end of the beforeupdate sub. But this means that Access still thinks the record is dirty, so I can't leave the record. Does anyone know how I can kid access into thinking the record isn't dirty when the before update has run?
I tried

Me.undo

which makes the record clean, but resets the values on the form, even though they're updated in the database.
and

Me.Dirty = False

causes an error.

Thanks for any help!

 

[Quehay]

This is not based on experience, but what about having
the following code in the event to cancel the record entry?

[tt] RunCmd acCmdUndo [/tt]

 

[TAMSIN]

Thanks for the idea
I tried:

RunCommand adCmdUndo

but it has the same effect as

Me.Undo

Basically I want to make Access think the record is clean without undoing the changes on the form.
Cheers,
Tamsin

 

[PaulF]

if this error is caused by a zero length string, change the Allow Zero Length property for that field to Yes.

PaulF

 

[jackme]

if you need the data in db for your update quary then you will have to save old values and undo it by code.
if you dont so why going thru update events anyway?

jack.

 

[TAMSIN]

I don't think I explained what I want to do very well!
I'm using Access as the front end to a postgreSQL database. Direct data-binding works fine for getting the data to display in an access form. However, when access runs an update (e.g. when I edit a record and then move to the mext record), the query it generates isn't ideal for Postgres. So I was thinking that I could do this update myself, rather than letting access do it, by using ADO to send an update query straight to PostgreSQL, to update the data in the db to match the changes on my form. However, if I do this in the before update event, access still thinks that the data is dirty and just keeps doing the before update event repeatedly. Unless I do Me.Undo after running the ADO update, which then resets my form data, so that the changes I made ARE in the database but are not in the form.
Does this make more sense?