Append query not appending on first attempt.

[kjspear]

Hello everyone,

I have a question about using Append and Deletion queries. Okay, I have a form where the user presses a delete button and a subform pops up. This form is still using the same underlying table. The user types in the reason for deletion for the record then checks off a confirmation check box. For some reason, when the user presses the deletion button on this subform which the invokes first the Append query. I have the this record appended to another table, then the record in the current table is removed. The problem I seem to be having is that a message states that the query is about to append 0 records. It will not work. However, if I either close the form, move one record ahead or back then go to that record again then do the process again, the confirmation box is still checked and this time a message reads 'You are about to append 1 record.' After that everything works correctly. All the fields on the form reads Deleted. Does anyone know why the user has to close the form or go back on record then return in order for it to work?
How can I get this to work then it will work right away?

If you need more information please let me know.

Thanks
KJ

 

[RoyVidar]

Access probably doesn't know the record is changed yet, try one of the save commands at the start of the "delete" code, for instance:

[tt]if me.dirty then
docmd.runcommand accmdsaverecord
end if[/tt]

Roy-Vidar

 

[OhioSteve]

Its a little difficult for me to follow your explanation, but I will give you some general advice.

First, you can avoid alot of problems by never deleting anything. I often put a boolean field called "deleted" into each table. Then make your forms' data sources queries. Those queries have the parameter deleted=false. When the user presses a "delete" button he just validates the field and refreshes the table. This makes it VERY easy to restore a deleted record when the users come to you crying. Plus, it avoids some of the technical problems that the verb "delete" causes. However, I understand that you have already written this app, so it may be too late to implement this idea.

Second, you can make objects on forms invisible. They have a property called "visible". So you could avoid all of this subform stuff. Create those controls as part of the normal form, NOT a popup. Set their visible property to "false". Then when the user clicks delete, change the objects' visible property to "true". That may eliminate some of your problems, because you aren't changing which object has the focus.

 

[kjspear]

Hello,

Roy-Vidar's suggesting worked. I had to save the record first so that Access can identify it. Yes, think your suggestion to flag the records deleted would be a better solution. But my manager wanted it moved to another table for whatever reason.

Thanks everyone,.

KJ