Inserting an Unbound Textbox to Count Records

T

TomP

Hello:

I have a form that is attached to a query. I disabled the navigation status
bar and and disabled the allow edits and deletions to the form. What I would
like to add is a textbox that will actually give the total record count on
the form.

I researched your discussion group questions and tried some coding and still
couldn't make it work. Is there a way to enter a simple formula in the
"Default Value" to get a total record count of records in query that is
attached to the form?

Thank you,
 
M

Marshall Barton

TomP said:
I have a form that is attached to a query. I disabled the navigation status
bar and and disabled the allow edits and deletions to the form. What I would
like to add is a textbox that will actually give the total record count on
the form.

I researched your discussion group questions and tried some coding and still
couldn't make it work. Is there a way to enter a simple formula in the
"Default Value" to get a total record count of records in query that is
attached to the form?
Click to expand...


Use a text box in the form header or footer section. Set
its control source expression to =Count(*)
 
J

Jeff Boyce

You don't mention what you tried ("tried some coding").

Rather than use the Default Value to calculate an initial value for the
record (which, by the way, when new, would have NO values in the other
fields, so would evaluate to zero or null or some such!), consider putting a
formula/expression in the AfterUpdate event of each/every control in which
the user could add/edit a value. The expression tells Access how to
calculate the new value and tells it where to stick it...


Regards

Jeff Boyce
Microsoft Office/Access MVP
 
T

TomP

With the navigation buttons set to "No", I want the form to display a total
record count value which will retrieve and count all records. My record
source data is based on a query.

I did tried the =Count(*) at the form footer and got only a zero.

Thank you,
 
M

Marshall Barton

TomP said:
With the navigation buttons set to "No", I want the form to display a total
record count value which will retrieve and count all records. My record
source data is based on a query.

I tried the =Count(*) at the form footer and got only a zero.
Click to expand...


A zero indicates that the form's record source has no
records.

Can you verify that there are some records?

Are you sure you put the =Count(*) text box in the form
footer section and not some other section?

An alternative to getting the forms record count is to use
some code in the form's Load event procedure:

Me.RecordsetClone.MoveLast
Me.txtTotalRecords = Me.RecordsetClone.RecordCount

Note that a form retrieves individual records as needed.
Without the nav buttons, you may not be able to get to any
record past the forst record. Perhaps I don't understand
what you are trying to accomplish because the "which will
retrieve" part of your question doesn't make sense to me.
 
T

TomP

This form does have 260 records. I created this form so the user can only
"view" the records only. Without have the navigation button visible would
only help enforce the policy.

Your suggestion to add the code in the Load event procedure works! The only
draw back is that the data will only appear when I click the field.

This might be asking too much, but is there a way to have the total record
account appear automatically? If not, I can create a textbox to say "click
here" or something.

Thank you!
 
M

Marshall Barton

That "problem" usually happens when there is a lot of other
stuff going on in the form (calculations, complex
conditional formatting, etc). A usually effective way to
overcome it is to add Me.Repaint and/or DoEvents after
setting the text box's value.

The Nav buttons have nothing to do with your goal. If your
form is displayed in continuous view, then the vertical
scroll bar is a good way to view records. If the form is in
single view, then the nav buttons are the standard approach.

In many situations you can prevent any changes to the form's
data by using the AllowEdits, AllowAdditions and
AllowDeletes properties. However, if you have unbound
controls used for navigation, then you need to set all the
bound control's Lock property.
 
Top