filter = no records make unbound fields disappear

[Allen Browne]

The Detail section of the form goes completely blank if both:
- there are no record to display, and
- no new records are added.

When this occurs, there are bugs in Access that mess up the display of the
boxes in the Form Header/Footer section also. Details in:
Incorrect display of data
at:
http://allenbrowne.com/bug-06.html

In your case, are the boxes in the form header bound to an expression, such
as:
=[Forms].[MyOtherForm].[StartDate]
Bound expressions will probably disappear when there is no current record,
so it might be better to leave the control unbound and programmatically copy
the value into it. However, this is still likely to suffer from the
"disappearing data" syndrome described in the article above.

The other alternative that might work would be to set the form's
AllowAdditions property to Yes. Assuming the source query is not read-only,
this allows Access to display the new record row, which works around the
problems. You can cancel the form's BeforeInsert event so that no new record
can actually be added.

 

[Ken Snell [MVP]]

Another "workaround" is to set the form's RecordSource to an empty string
when there are no records. Of course, one must then have your code test for
an empty RecordSource when doing the next "filter" and set it to the correct
RecordSource string.

--

Ken Snell
<MS ACCESS MVP>

The Detail section of the form goes completely blank if both:
- there are no record to display, and
- no new records are added.

When this occurs, there are bugs in Access that mess up the display of the
boxes in the Form Header/Footer section also. Details in:
Incorrect display of data
at:
http://allenbrowne.com/bug-06.html

In your case, are the boxes in the form header bound to an expression,
such as:
=[Forms].[MyOtherForm].[StartDate]
Bound expressions will probably disappear when there is no current record,
so it might be better to leave the control unbound and programmatically
copy the value into it. However, this is still likely to suffer from the
"disappearing data" syndrome described in the article above.

The other alternative that might work would be to set the form's
AllowAdditions property to Yes. Assuming the source query is not
read-only, this allows Access to display the new record row, which works
around the problems. You can cancel the form's BeforeInsert event so that
no new record can actually be added.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I have a form based on a query. It has unbound Start Date and End Date
textboxes at the top to set the criteria for the recrodsource query. When
a particular textbox is clicked, it opens another form based on a
different query. This new form has the Start Date and End Date textboxes
at the top also, and their control source is the same fields from the
previous form, which remains open. On the second form, I use a combobox
to apply filters to the records in the detail section. The problem is,
when a value is selected for the filter that returns no records, the two
date fields at the top disappear too, even though their source is not the
form's recordsource, but the values in a different form's unbound
textboxes. How can I keep a "no records returned" from erasing the values
out of completely unrelated textboxes that are in the form header?

Thanks for any help on this.

 

[rgrantz]

I have a form based on a query. It has unbound Start Date and End Date
textboxes at the top to set the criteria for the recrodsource query. When a
particular textbox is clicked, it opens another form based on a different
query. This new form has the Start Date and End Date textboxes at the top
also, and their control source is the same fields from the previous form,
which remains open. On the second form, I use a combobox to apply filters
to the records in the detail section. The problem is, when a value is
selected for the filter that returns no records, the two date fields at the
top disappear too, even though their source is not the form's recordsource,
but the values in a different form's unbound textboxes. How can I keep a
"no records returned" from erasing the values out of completely unrelated
textboxes that are in the form header?

Thanks for any help on this.

 

[rgrantz]

Thanks to you both for the explanations and suggestions. I'm disappointed
to hear that this behavior exists in this day and age, it doesn't seem like
there's any legitimate reason to have this happen to controls that are
unbound to the recordsource. It's disappointing that this bug is still here
in 2005, I mean sheesh.

Thanks again, it is appreciated.

Another "workaround" is to set the form's RecordSource to an empty string
when there are no records. Of course, one must then have your code test
for an empty RecordSource when doing the next "filter" and set it to the
correct RecordSource string.

--

Ken Snell
<MS ACCESS MVP>

The Detail section of the form goes completely blank if both:
- there are no record to display, and
- no new records are added.

When this occurs, there are bugs in Access that mess up the display of
the boxes in the Form Header/Footer section also. Details in:
Incorrect display of data
at:
http://allenbrowne.com/bug-06.html

In your case, are the boxes in the form header bound to an expression,
such as:
=[Forms].[MyOtherForm].[StartDate]
Bound expressions will probably disappear when there is no current
record, so it might be better to leave the control unbound and
programmatically copy the value into it. However, this is still likely to
suffer from the "disappearing data" syndrome described in the article
above.

The other alternative that might work would be to set the form's
AllowAdditions property to Yes. Assuming the source query is not
read-only, this allows Access to display the new record row, which works
around the problems. You can cancel the form's BeforeInsert event so that
no new record can actually be added.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I have a form based on a query. It has unbound Start Date and End Date
textboxes at the top to set the criteria for the recrodsource query.
When a particular textbox is clicked, it opens another form based on a
different query. This new form has the Start Date and End Date textboxes
at the top also, and their control source is the same fields from the
previous form, which remains open. On the second form, I use a combobox
to apply filters to the records in the detail section. The problem is,
when a value is selected for the filter that returns no records, the two
date fields at the top disappear too, even though their source is not the
form's recordsource, but the values in a different form's unbound
textboxes. How can I keep a "no records returned" from erasing the
values out of completely unrelated textboxes that are in the form header?

Thanks for any help on this.