close form on empty record source

[Dick Minter]

My form's record source is a query of selected records. As each record is
processed it is de-selected and the form is requeried (me.requery). I want
the form to close when the last record has been processed. I tried code on
the form's Current event:

If isnull(dlookup("[idfield]","[queryname]")) then
btnClose_click (includes the "docmd.close acform, me.name" statement)
end if

But I get an error: Close method cannot be executed while event is still
running.

Where else can I place the code? Or is there a better approach?

DM

 

[Piet Linden]

My form's record source is a query of selected records.  As each recordis
processed it is de-selected and the form is requeried (me.requery).  I want
the form to close when the last record has been processed.  I tried code on
the form's Current event:

If isnull(dlookup("[idfield]","[queryname]")) then
      btnClose_click  (includes the "docmd.close acform, me.name"statement)
end if

But I get an error: Close method cannot be executed while event is still
running.

Where else can I place the code?  Or is there a better approach?

DM

If the form is bound, you should be able to use the RecordsetClone
property of the form, get the count of records it contains and close
if there are no more records to process.
If Me.RecordsetClone.RecordCount = 0 then Me.Close

 

[Dick Minter]

I've tried puting the suggested code on the form's AfterUpdate event, but I
still can't get this to work. Since the form.requery method doesn't fire the
form's AfterUpdate event, the recordsetclone still has a record count of 1.
I inserted a call to Form_AfterUpdate after the form.requery which does
update the count and run the code to close the form, but Access continues to
try to requery the subform after the form's Close() event has fired. The
problem with that is the Close() event checks to see if a form holding a date
range is open and closes it. Since the date range form holds criteria for
the subform's rowsource query, an error occurs once it is closed. Can you
suggest a better placement for the code, or another approach?

DM

My form's record source is a query of selected records. As each record is
processed it is de-selected and the form is requeried (me.requery). I want
the form to close when the last record has been processed. I tried code on
the form's Current event:

If isnull(dlookup("[idfield]","[queryname]")) then
btnClose_click (includes the "docmd.close acform, me.name" statement)
end if

But I get an error: Close method cannot be executed while event is still
running.

Where else can I place the code? Or is there a better approach?

DM

If the form is bound, you should be able to use the RecordsetClone
property of the form, get the count of records it contains and close
if there are no more records to process.
If Me.RecordsetClone.RecordCount = 0 then Me.Close

 

[Dick Minter]

I misstated the first issue: the form.requery does fire AfterUpdate, but the
recordsetclone still shows a count of 1. The value change in textbox control
is what removes, or is intended to remove, the record from the query result.

I've tried puting the suggested code on the form's AfterUpdate event, but I
still can't get this to work. Since the form.requery method doesn't fire the
form's AfterUpdate event, the recordsetclone still has a record count of 1.
I inserted a call to Form_AfterUpdate after the form.requery which does
update the count and run the code to close the form, but Access continues to
try to requery the subform after the form's Close() event has fired. The
problem with that is the Close() event checks to see if a form holding a date
range is open and closes it. Since the date range form holds criteria for
the subform's rowsource query, an error occurs once it is closed. Can you
suggest a better placement for the code, or another approach?

DM

My form's record source is a query of selected records. As each record is
processed it is de-selected and the form is requeried (me.requery). I want
the form to close when the last record has been processed. I tried code on
the form's Current event:

If isnull(dlookup("[idfield]","[queryname]")) then
btnClose_click (includes the "docmd.close acform, me.name" statement)
end if

But I get an error: Close method cannot be executed while event is still
running.

Where else can I place the code? Or is there a better approach?

DM

If the form is bound, you should be able to use the RecordsetClone
property of the form, get the count of records it contains and close
if there are no more records to process.
If Me.RecordsetClone.RecordCount = 0 then Me.Close