Autonumber not working

[Deon]

I created a table in a budget database without a autonumber field. I then inserted a autonumber field after creating the form by inserting a row in the table which works fine in the table, now i want to use a text box on the form with BudgetID from the autonumber field to give me the total amount of records in the database but the autonumber field is not in the record source dropdown. Is there another way of doing this please?

 

[pr2-eugin]

If you need to find the total Number of records in the Table use the DMax() function.. As it might come to a shock, Autonumbers in Access gives you a guaranteed Unique number but ARE NOT continuous, sometime NOT even Positive.. So do not trust them to give you the total number of records or show what next number would be.. If it is not available in the drop down, delete the Textbox you current have and use the Add Existing Fields tool, in Form design and drag and drop in your form, that will auto fill the Control source for you..

 

[bob fitz]

If you need to find the total Number of records in the Table use the DMax() function.. As it might come to a shock, Autonumbers in Access gives you a guaranteed Unique number but ARE NOT continuous, sometime NOT even Positive.. So do not trust them to give you the total number of records or show what next number would be.. If it is not available in the drop down, delete the Textbox you current have and use the Add Existing Fields tool, in Form design and drag and drop in your form, that will auto fill the Control source for you..

I suspect that you meant to say use DCount () to get the total as DMax() will return the largest value.

 

[pr2-eugin]

I suspect that you meant to say use DCount () to get the total as DMax() will return the largest value.

Ha ha.. That is what I did mean.. Thanks for noting that Bob.. Sorry if I had mislead..

 

[Deon]

Thanks for advice. That has solved the problem, cheers

 

[Galaxiom]

Where the information is required on a form that already has the recordset you want to count loaded there is no need to hit the database again with a DCount.

Best practice is to use use the Count function against the form's recordset. This is more efficient than a domain function.

This ControlSource for a textbox in the footer of the form will show the number of records on the form:

= Count(*)

BTW You can count the number of records with data in a particular field by replacing the asterisk with a field name. Nulls in that field will not be included in the count.