ADP bound form refresh after subform record delete?

[dmcheng1]

Hi. I have an Access 2002 ADP project connecting to a SQL 2000
database. I have a form "Main" using a stored proc as a record source.
I have a continuous subform "Sub" using a Select statement as a record
source.

In Sub's AfterUpdate event, I run a stored proc to update data in a
table and then I Refresh the Main form to display the updated data.

This event works fine if I am adding or editing records in Sub.
However, if I delete records in Sub, then the Main form does not
display the updated data - I have to exit the form and re-enter it in
order to see it.

Anyone know why this occurs? I've tried putting the refresh code in
Sub's Delete event, but that doesn't work either.

Thanks
David

 

[dmcheng1]

A few more details:

The stored procedure I use for form Main is a single table SELECT - no
join is involved. Same goes for the SELECT statement for the Sub form.
Thus, I believe I don't need to use UniqueTable or Resync.

David

 

[Sylvain Lafontaine]

I'm not sure but if I remember correctly, I think that with the Delete
operation, the delete events take place before the data are deleted on the
SQL-Server or something like that. So it's possible that you are making
your refresh before the data are actually deleted.

You should make the verification using the SQL Profiler to see what happens
on the server.

 

[dmcheng1]

Yes you're right, it appears that the SQL delete action occurs after
the Access recordset refresh. Arrggh. I guess I'll have to a manual
Refresh button on the form.

Thanks for your reply, Sylvain.

David

 

[Bonno Hylkema]

I did have the same problem with delete, but I also have a solution:

- on the subform with the following events: after update and after del
confirm, update an arbitrary field on the mainform, like Me.parent!field =
Me.parent!field.oldvalue. This will also work for insert.
- on the mainform with the following event: before update run a procedure
that updates the data on the mainform itself. Notice that you don't update
the data directly in the table, since that will cause an error: data updated
by another user.

Maybe this will help you.

Bonno Hylkema

 

[dmcheng1]

Thanks, I'll try that.

David

I did have the same problem with delete, but I also have a solution:

- on the subform with the following events: after update and after del
confirm, update an arbitrary field on the mainform, like Me.parent!field =
Me.parent!field.oldvalue. This will also work for insert.
- on the mainform with the following event: before update run a procedure
that updates the data on the mainform itself. Notice that you don't update
the data directly in the table, since that will cause an error: data updated
by another user.

Maybe this will help you.

Bonno Hylkema