Forms AfterUpdate Event

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
--
Dave Hargis, Microsoft Access MVP


Allen Davidson said:
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.
Click to expand...
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'
Click to expand...
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).
Click to expand...
Do you have error handling set so that if an error occurs on the server
side, Access will know about it?
 
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
 
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.
 
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
 
Sorry for the obvious question, buy how do you know it is not firing?
Is it a bound form?
 
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
 
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.
 
Allen said:
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.
Click to expand...


Stating the obvious, but double check that the AfterUpdate
**property** contains:
[Event Procedure]
 
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.
 
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

Marshall Barton said:
Allen said:
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.
Click to expand...


Stating the obvious, but double check that the AfterUpdate
**property** contains:
[Event Procedure]
Click to expand...
 
Allen said:
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
Click to expand...


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.
 
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,

Marshall Barton said:
Allen said:
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
Click to expand...


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.
Click to expand...
 
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




Allen Davidson said:
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,

Marshall Barton said:
Allen said:
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
Click to expand...


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.
Click to expand...
Click to expand...
 
I dont even believe this myself.

Allen Davidson said:
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




Allen Davidson said:
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,

Marshall Barton said:
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.
Click to expand...
Click to expand...
Click to expand...
 
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]


Allen said:
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.


Allen Davidson said:
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.


Marshall Barton said:
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.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top