Form Objects Disappear When Filter Returns No Records

[JamesJ]

AC 2007 sp2.
I have a form with a list box combo box and a text box.

The cbo is used to filter records in the form and is
also synced with the list box. I have dvd titles in the list box and when I
select an item
in the cbo (category) it filters the records in the list box and the form.

I have an item in the lookup table 'Other' which is the default value for a
new record. (making sure that all records have a value for the category)
So there usually isn't any records containing 'Other' so when I select it
the cbo and no records are returned the list box and text box totally
disappear.

I do have the Allow Additions set to no because the other day I was going
crazy trying to figure out
why the Navigation Bar was showing 1 more record than actually existed. I
found that that access counts a
new record as a row.
If I set Allow Edits to Yes the controls don't disappear but the record
count is wrong.

Any Ideas?

James

 

[Dirk Goldgar]

AC 2007 sp2.
I have a form with a list box combo box and a text box.

The cbo is used to filter records in the form and is
also synced with the list box. I have dvd titles in the list box and when
I select an item
in the cbo (category) it filters the records in the list box and the form.

I have an item in the lookup table 'Other' which is the default value for
a new record. (making sure that all records have a value for the category)
So there usually isn't any records containing 'Other' so when I select it
the cbo and no records are returned the list box and text box totally
disappear.

I do have the Allow Additions set to no because the other day I was going
crazy trying to figure out
why the Navigation Bar was showing 1 more record than actually existed. I
found that that access counts a
new record as a row.
If I set Allow Edits to Yes the controls don't disappear but the record
count is wrong.

Any Ideas?

If you put the list box and combo box into the form header section, instead
of the detail section, they won't disappear when there are no records. You
didn't say much about the text box, so I don't know if that could also go in
the header section or not.

 

[JamesJ]

Ok.
There's no way to get the record count in the nav bar not to consider a new
record as a row?

James

 

[Dirk Goldgar]

Ok.
There's no way to get the record count in the nav bar not to consider a
new record as a row?

I'm not toally sure about Access 2007, since I don't have it handy, but
normally the record count in the built-in navigation buttons only counts the
actual records unless you're actually positioned on the new record. If
you're on the new record, though, it counts that record -- 1 more than the
actual count -- and only drops down to the actual count when you move off
the new record without adding a record there.

Is that what you're talking about? If it is, you can't get that behavior to
change. You can, however, turn off the built-in navigation controls and add
your own controls for navigation and status display. The form's record
count can be displayed by a calculated text box with this controlsource:

=[Recordset].[RecordCount]

 

[JamesJ]

I abandoned the RecordCount code I had in the on current of the form
to lighten up my app.

Seems to work ok no that I'm using...

If Me.NewRecord Then
Me.txtCount = "0 Item(s)"
Else
Me.RecordsetClone.MoveLast
Me.txtCount = Me.RecordsetClone.RecordCount & " Item(s)"
End If

and setting the Allow Additions to No.

I'm not sure where I got this code from but it seems to work ok.

Thanks,
James

Ok.
There's no way to get the record count in the nav bar not to consider a
new record as a row?

I'm not toally sure about Access 2007, since I don't have it handy, but
normally the record count in the built-in navigation buttons only counts
the actual records unless you're actually positioned on the new record.
If you're on the new record, though, it counts that record -- 1 more than
the actual count -- and only drops down to the actual count when you move
off the new record without adding a record there.

Is that what you're talking about? If it is, you can't get that behavior
to change. You can, however, turn off the built-in navigation controls
and add your own controls for navigation and status display. The form's
record count can be displayed by a calculated text box with this
controlsource:

=[Recordset].[RecordCount]

--
Dirk Goldgar, MS Access MVP
Access tips: www.datagnostics.com/tips.html

(please reply to the newsgroup)