close form on empty record source


D

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
 
Ad

Advertisements

P

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
 
D

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

Piet Linden said:
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
 
Ad

Advertisements

D

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.

Dick Minter said:
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

Piet Linden said:
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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top