Forms AfterUpdate Event

[Allen Davidson]

Hi - I'm trying to debug some code I inherited.

Access front end SQL back end. Sort of document management.

Basically I have a form that is opened with DoCmd.OpenForm ,the form is used
for additions and edits of a document but nether acFormAdd or acFormEdit are
specified.

On a save button there is code to DoCmdGoToRecord , , acNext which raises an
error "You can't go to the specified record" the code is commented with 'To
force a commit on current record go to the next wether update or add'

The record does however get written to the DB.

There is then a Form_AfterUpdate with code to run a stored proc to allocate
the document to another entity using its id (int identity) however the
problem is the Form_AfterUpdate is not firing (probably for very good reason).

Any thoughts, I'm trying to avoid re-writing 500 lines of code.

Allen

 

[Klatuu]

--
Dave Hargis, Microsoft Access MVP

Hi - I'm trying to debug some code I inherited.

Access front end SQL back end. Sort of document management.

Basically I have a form that is opened with DoCmd.OpenForm ,the form is used
for additions and edits of a document but nether acFormAdd or acFormEdit are
specified.

Not required. These are defined with defaults in the form's properties.

On a save button there is code to DoCmdGoToRecord , , acNext which raises an
error "You can't go to the specified record" the code is commented with 'To
force a commit on current record go to the next wether update or add'

Better way to force a commit is
Me.Dirty = False

The record does however get written to the DB.

There is then a Form_AfterUpdate with code to run a stored proc to allocate
the document to another entity using its id (int identity) however the
problem is the Form_AfterUpdate is not firing (probably for very good reason).

Do you have error handling set so that if an error occurs on the server
side, Access will know about it?

 

[Allen Davidson]

Hi Dave,

Thanks for the reply.

1. acFormAdd, acFormEdit - just read that these default to the form

2. yes thats what I normally do

3. no - not sure how to do this - however I don't think a server side error
is occuring, there are no server actions between writing the document to the
DB and the point where the stored proc to run the allocation should run.

Allen

 

[Klatuu]

I don't see any other obvious problems. SQL Server is not my strong suit, so
I may not be able to help in detail. I do remember reading somewhere that
you can write the stored procs so they will return a user defined error if
they have a problem, but I can't help with the detals.
Maybe if you repost your quesion in the odbcclientsvr group, you will get a
more intelligent response.

 

[Allen Davidson]

Thanks Dave, just to clarify the post my problem is not with the stored proc,
the problem is the AfterUpdate which runs the stored proc is not firing.

Allen

 

[Klatuu]

Sorry for the obvious question, buy how do you know it is not firing?
Is it a bound form?

 

[Allen Davidson]

Hi Dave,

I don't mind obvious questions at all, they are usually the ones you forget
to ask of yourself.

I put a brekpont and msgbbox in the AfterUpdate and nothing.

The form is bound.

I changed the DoCmdGoToRecord to Me.Dirty = False, I now get an (error?)
message " The data was added to the database but the data won't be displayed
in the form because it doesn't dsatisfy the criteria in the underlying record
source", I'm sure this is a hint to what is wrong!

The RecordSource of the form is "Select * FROM tblABF" , this is also set in
the Form_Load event to the same string "Select * FROM tblABF"

Allen

 

[Klatuu]

So, you have a bound form with a bound control and the After Update event of
the bound control does not fire when you change the value of the control?

The only thing I can think of if this is the case, is corruption.

 

[Marshall Barton]

Thanks Dave, just to clarify the post my problem is not with the stored proc,
the problem is the AfterUpdate which runs the stored proc is not firing.

Stating the obvious, but double check that the AfterUpdate
**property** contains:
[Event Procedure]

 

[Allen Davidson]

Sorry - me not being clear , it's the after update event of the Form that's
not firing.

 

[Klatuu]

Marsh may be on to something here. There has to be some obvious little thing
that will make you say Doh! when you find it.

 

[Allen Davidson]

Hi Marsh,

Yep it says [Event Procedure] (copy & pasted)

The other strange thing i noticed when trying to debug was that if I put a
watch on my bound columns, Me.ABFDesc, Me.Date ... then they are populated
with the user entered data before I run Me.Dirty = False but are null after
Me.Dirty = false.

Also Me.ID (the sql identity column) is null which is a pity because I need
the Me.ID as a param to the stored proc that allocates the new document,
thats the stored proc thats runs (or rather should run) in the forms After
Update event

Allen

Thanks Dave, just to clarify the post my problem is not with the stored proc,
the problem is the AfterUpdate which runs the stored proc is not firing.

Stating the obvious, but double check that the AfterUpdate
**property** contains:
[Event Procedure]

 

[Marshall Barton]

Yep it says [Event Procedure] (copy & pasted)

The other strange thing i noticed when trying to debug was that if I put a
watch on my bound columns, Me.ABFDesc, Me.Date ... then they are populated
with the user entered data before I run Me.Dirty = False but are null after
Me.Dirty = false.

Also Me.ID (the sql identity column) is null which is a pity because I need
the Me.ID as a param to the stored proc that allocates the new document,
thats the stored proc thats runs (or rather should run) in the forms After
Update event

I think maybe you should go back to the "can't display
record" error and investigate how the record is being
filtered out of the form's recordset (and figure out what
record does become current, maybe a new record). Given the
record source you posted, there must be something else
that's getting involved. If it's a server side thing, then
try posting to a more appropriate newsgroup.

It might(?) help if you use a break point in the form's
Error, BeforeUpdate and Current events to investigate what's
happening.

With all the guessing that Dave and I are doing here, it
should be clear that what you are seeing is extremely
unusual and, with an mdb back end, would normally be chalked
up to corruption.

 

[Allen Davidson]

Thnaks Marsh, will try that, on leave till monday, I'm thinking the fact that
aftter the me.dirty = false leaves my (recordset) empty ( null values on the
bound coulmns) is part of the problem.

Thanks for the help,

Yep it says [Event Procedure] (copy & pasted)

The other strange thing i noticed when trying to debug was that if I put a
watch on my bound columns, Me.ABFDesc, Me.Date ... then they are populated
with the user entered data before I run Me.Dirty = False but are null after
Me.Dirty = false.

Also Me.ID (the sql identity column) is null which is a pity because I need
the Me.ID as a param to the stored proc that allocates the new document,
thats the stored proc thats runs (or rather should run) in the forms After
Update event

I think maybe you should go back to the "can't display
record" error and investigate how the record is being
filtered out of the form's recordset (and figure out what
record does become current, maybe a new record). Given the
record source you posted, there must be something else
that's getting involved. If it's a server side thing, then
try posting to a more appropriate newsgroup.

It might(?) help if you use a break point in the form's
Error, BeforeUpdate and Current events to investigate what's
happening.

With all the guessing that Dave and I are doing here, it
should be clear that what you are seeing is extremely
unusual and, with an mdb back end, would normally be chalked
up to corruption.

 

[Allen Davidson]

Found it but v perculiar

The SQL backend table that was being written to has an insert trigger.

Trigger inserts a value from the psudo inserted table into another table
tblLog.

If tblLog has IDENTIY Colums it seems that the after update does not fire.

If I change tblLog and remove the IDENTITY clause all is well, the After
Update fires.

Something to do with Jet ?

Thanks for all the help and suggestions on Friday, probably saved my sanity.

Regards
Allen

Thnaks Marsh, will try that, on leave till monday, I'm thinking the fact that
aftter the me.dirty = false leaves my (recordset) empty ( null values on the
bound coulmns) is part of the problem.

Thanks for the help,

Yep it says [Event Procedure] (copy & pasted)

The other strange thing i noticed when trying to debug was that if I put a
watch on my bound columns, Me.ABFDesc, Me.Date ... then they are populated
with the user entered data before I run Me.Dirty = False but are null after
Me.Dirty = false.

Also Me.ID (the sql identity column) is null which is a pity because I need
the Me.ID as a param to the stored proc that allocates the new document,
thats the stored proc thats runs (or rather should run) in the forms After
Update event

I think maybe you should go back to the "can't display
record" error and investigate how the record is being
filtered out of the form's recordset (and figure out what
record does become current, maybe a new record). Given the
record source you posted, there must be something else
that's getting involved. If it's a server side thing, then
try posting to a more appropriate newsgroup.

It might(?) help if you use a break point in the form's
Error, BeforeUpdate and Current events to investigate what's
happening.

With all the guessing that Dave and I are doing here, it
should be clear that what you are seeing is extremely
unusual and, with an mdb back end, would normally be chalked
up to corruption.

 

[Allen Davidson]

I dont even believe this myself.

Found it but v perculiar

The SQL backend table that was being written to has an insert trigger.

Trigger inserts a value from the psudo inserted table into another table
tblLog.

If tblLog has IDENTIY Colums it seems that the after update does not fire.

If I change tblLog and remove the IDENTITY clause all is well, the After
Update fires.

Something to do with Jet ?

Thanks for all the help and suggestions on Friday, probably saved my sanity.

Regards
Allen

Thnaks Marsh, will try that, on leave till monday, I'm thinking the fact that
aftter the me.dirty = false leaves my (recordset) empty ( null values on the
bound coulmns) is part of the problem.

Thanks for the help,

Allen Davidson wrote:
Yep it says [Event Procedure] (copy & pasted)

The other strange thing i noticed when trying to debug was that if I put a
watch on my bound columns, Me.ABFDesc, Me.Date ... then they are populated
with the user entered data before I run Me.Dirty = False but are null after
Me.Dirty = false.

Also Me.ID (the sql identity column) is null which is a pity because I need
the Me.ID as a param to the stored proc that allocates the new document,
thats the stored proc thats runs (or rather should run) in the forms After
Update event


I think maybe you should go back to the "can't display
record" error and investigate how the record is being
filtered out of the form's recordset (and figure out what
record does become current, maybe a new record). Given the
record source you posted, there must be something else
that's getting involved. If it's a server side thing, then
try posting to a more appropriate newsgroup.

It might(?) help if you use a break point in the form's
Error, BeforeUpdate and Current events to investigate what's
happening.

With all the guessing that Dave and I are doing here, it
should be clear that what you are seeing is extremely
unusual and, with an mdb back end, would normally be chalked
up to corruption.

 

[Marshall Barton]

That's way beyond my SQL Server (lack of) knowledge, but it
sounds "v perculiar" to me too.

If you want to pursue this, I suggest that you sum up the
question of AfterUpdate and IDENTITY and post it as a new
thread in the access queries and in an appropriate SQL
Server forum so folks with more experience in this stuff
might enter the discussion.
--
Marsh
MVP [MS Access]

Found it but v perculiar

The SQL backend table that was being written to has an insert trigger.

Trigger inserts a value from the psudo inserted table into another table
tblLog.

If tblLog has IDENTIY Colums it seems that the after update does not fire.

If I change tblLog and remove the IDENTITY clause all is well, the After
Update fires.

Something to do with Jet ?

Thanks for all the help and suggestions on Friday, probably saved my sanity.

Thnaks Marsh, will try that, on leave till monday, I'm thinking the fact that
aftter the me.dirty = false leaves my (recordset) empty ( null values on the
bound coulmns) is part of the problem.

Allen Davidson wrote:
Yep it says [Event Procedure] (copy & pasted)

The other strange thing i noticed when trying to debug was that if I put a
watch on my bound columns, Me.ABFDesc, Me.Date ... then they are populated
with the user entered data before I run Me.Dirty = False but are null after
Me.Dirty = false.

Also Me.ID (the sql identity column) is null which is a pity because I need
the Me.ID as a param to the stored proc that allocates the new document,
thats the stored proc thats runs (or rather should run) in the forms After
Update event


I think maybe you should go back to the "can't display
record" error and investigate how the record is being
filtered out of the form's recordset (and figure out what
record does become current, maybe a new record). Given the
record source you posted, there must be something else
that's getting involved. If it's a server side thing, then
try posting to a more appropriate newsgroup.

It might(?) help if you use a break point in the form's
Error, BeforeUpdate and Current events to investigate what's
happening.

With all the guessing that Dave and I are doing here, it
should be clear that what you are seeing is extremely
unusual and, with an mdb back end, would normally be chalked
up to corruption.