Requery method fails on subform (2002 SP2)

Topic

New Reply

I have a subform which contains the line items for an order. In that subform, if certain line items are deleted, then other optional line items also need to be deleted if they exist. The delete is being done using DAO that runs on the Form/Delete event, and works in a straightforward manner, except it leaves a line of “deleted” fields on the continuous subform. To try to get rid of this, I put in code at the end of the delete event saying Me.Requery and got the runtime error message 2118 – “You must save the current field before you run the Requery action.” Specifying the full form and subform control name causes the same error message. I also tried using the DoCmd.Requery version and got a slightly different runtime error 2046 – “The command or action Requery isn’t available now.”

I’m sure I’ve done this before on subforms and had it work – can anybody shed some light on this? I’ve even wondered if it is a timing issue or race condition.

Reply | Quote

Replies

“Optional line items”? Do they appear on the subform as well? That error message sounds strange, since you can’t really save a field, only a record.

Reply | Quote

Yes, the records are all stored in a single table and are displayed on the subform where the line items are being deleted. A messy situation that I don’t like, but the client insisted. ARGH! Kevin suggested simply reseting the record source for the subform, which I haven’t tried yet, but it seems like you should be able to do a Requery and that would be it.

Reply | Quote

Yes, the records are all stored in a single table and are displayed on the subform where the line items are being deleted. A messy situation that I don’t like, but the client insisted. ARGH! Kevin suggested simply reseting the record source for the subform, which I haven’t tried yet, but it seems like you should be able to do a Requery and that would be it.

Reply | Quote

So are you clearing fields in the same record or other records in the same table?? The error message almost sounds like the AfterUpdate event of a control hasn’t yet occurred.

Reply | Quote

So are you clearing fields in the same record or other records in the same table?? The error message almost sounds like the AfterUpdate event of a control hasn’t yet occurred.

Reply | Quote

No clearing is going on – we actually run a SQL query to delete the records (usually one, but can be four or more in unusual cases) in the Form/Delete event before we actually let the delete of the primary record. I suppose it may have to do with the Delete event itself, and the fact that there is a pending delete which hasn’t been cancelled, but the logic for deciding whether the delete should actually occur is in the delete event. I should think other people have encountered this issue before – and in thinking about it I think I’ve only done it with SQL Server backends, while this is an Access linked table.

Reply | Quote

Oh, I think I see. Your subform is bound to the table/query from which you’re deleting records in code, right? In that case, you may need to either add a DoEvents before the requery or you may need to make sure the subform isn’t dirty before you requery it. Have you tried a refresh? I usually turn the echo off until after the subform is updated in that case to avoid seeing the “deleted” values flash on the screen.

Reply | Quote

I think you may have hit on it – I probably can’t really do a requery until the Delete event has processed – the actual record or records that are left over with the “deleted” status are the related record. So the DoEvents command may well accomplish what I need and then let me do either a refresh or a requery. This whole thing is sort of based on a self-join which is a neat feature, but can also get you into trouble if you try to use referential integrity. I’ll let you know how it turns out. Thanks very much.

Reply | Quote

I think you may have hit on it – I probably can’t really do a requery until the Delete event has processed – the actual record or records that are left over with the “deleted” status are the related record. So the DoEvents command may well accomplish what I need and then let me do either a refresh or a requery. This whole thing is sort of based on a self-join which is a neat feature, but can also get you into trouble if you try to use referential integrity. I’ll let you know how it turns out. Thanks very much.

Reply | Quote

Unfortunately, the DoEvents didn’t seem to make anything happen. As it turns out the .Requery method causes both the BeforeUpdate and Current events to fire, and the error message looks to be coming from the Current event when all the error trapping is turned off – I do however get a different error – 3246 Reserved Error when the DoEvents happens just before the .Requery. Unfortunately, the .Refresh command doesn’t make the “deleted” line go away. I’ll try some other things and see if they fix it.

Reply | Quote

Are you doing deletes before the subform is doing it’s deletes? Maybe there’s a conflict here, with the subform records being in memory and you do your deletes from the table then the subform gets itself in a knot trying to access the tables deleted records.
Just guessing here.
Any chance of posting a cutdown DB for us mere mortals.

Reply | Quote

I think your are correct – at least it looks to be recursive when I try to step through the code, the Before Update and Current events are being triggered in the middle of the Delete event. The code for the delete event by itself is over 4 screens – it messes with backorders and a bunch of things. The code for just the subform is over 20 screens (at 1280×1024), and the main form is far more complex than the subform. Various developers have been working on this beast for over 10 years. The database (just the front-end is 42MB – the backends are about 300MB – there are 6 of them, one of which is 240MB so cutting it down to post it would be a major effort in and of itself.

What we finally decided to do was to move all the code out of the delete event, and do everything in SQL from a command button. Basic testing of that approach indicates it should work. So I have a fair bit of rewriting in my tomorrow!

Reply | Quote

Keep us up to date on that one Wendell, I am interested to know how you solve this one.
I feel for you for the amount of rewriting you have to do.

Reply | Quote

Just for the record, we moved all the code to the command button, wrote queries to delete first the dependent records, then the primary record, and removed all the code associated with the Delete event for the Form, and were able to do a simple Me.Refresh at the end of the command button routine. Everything now seems to work fine – the fundamental problem appeared to be some code related to the BeforeUpdate and Current events that were firing when the Refresh event was initiated. I suspect that it was related to that unique situation, and more normal situations would not exhibit the same behavior.

Reply | Quote

Just for the record, we moved all the code to the command button, wrote queries to delete first the dependent records, then the primary record, and removed all the code associated with the Delete event for the Form, and were able to do a simple Me.Refresh at the end of the command button routine. Everything now seems to work fine – the fundamental problem appeared to be some code related to the BeforeUpdate and Current events that were firing when the Refresh event was initiated. I suspect that it was related to that unique situation, and more normal situations would not exhibit the same behavior.

Reply | Quote

Keep us up to date on that one Wendell, I am interested to know how you solve this one.
I feel for you for the amount of rewriting you have to do.

Reply | Quote

I think your are correct – at least it looks to be recursive when I try to step through the code, the Before Update and Current events are being triggered in the middle of the Delete event. The code for the delete event by itself is over 4 screens – it messes with backorders and a bunch of things. The code for just the subform is over 20 screens (at 1280×1024), and the main form is far more complex than the subform. Various developers have been working on this beast for over 10 years. The database (just the front-end is 42MB – the backends are about 300MB – there are 6 of them, one of which is 240MB so cutting it down to post it would be a major effort in and of itself.

What we finally decided to do was to move all the code out of the delete event, and do everything in SQL from a command button. Basic testing of that approach indicates it should work. So I have a fair bit of rewriting in my tomorrow!

Reply | Quote

Are you doing deletes before the subform is doing it’s deletes? Maybe there’s a conflict here, with the subform records being in memory and you do your deletes from the table then the subform gets itself in a knot trying to access the tables deleted records.
Just guessing here.
Any chance of posting a cutdown DB for us mere mortals.

Reply | Quote

Unfortunately, the DoEvents didn’t seem to make anything happen. As it turns out the .Requery method causes both the BeforeUpdate and Current events to fire, and the error message looks to be coming from the Current event when all the error trapping is turned off – I do however get a different error – 3246 Reserved Error when the DoEvents happens just before the .Requery. Unfortunately, the .Refresh command doesn’t make the “deleted” line go away. I’ll try some other things and see if they fix it.

Reply | Quote

Oh, I think I see. Your subform is bound to the table/query from which you’re deleting records in code, right? In that case, you may need to either add a DoEvents before the requery or you may need to make sure the subform isn’t dirty before you requery it. Have you tried a refresh? I usually turn the echo off until after the subform is updated in that case to avoid seeing the “deleted” values flash on the screen.

Reply | Quote

No clearing is going on – we actually run a SQL query to delete the records (usually one, but can be four or more in unusual cases) in the Form/Delete event before we actually let the delete of the primary record. I suppose it may have to do with the Delete event itself, and the fact that there is a pending delete which hasn’t been cancelled, but the logic for deciding whether the delete should actually occur is in the delete event. I should think other people have encountered this issue before – and in thinking about it I think I’ve only done it with SQL Server backends, while this is an Access linked table.

Reply | Quote

“Optional line items”? Do they appear on the subform as well? That error message sounds strange, since you can’t really save a field, only a record.

Reply | Quote