I was wondering if there is a way to do a recordcount of all records in a specific table, and then save that recordcount (along with other information) as the default field value of a record in the same table.
I know that you can run a query asking for a count of records in a specific table, but whenever I try to run the count function in my 'default value' area of my field (in the table design view) I get an error saying the field for that table can't be found. For instance, the following query works fine for me:
SELECT Count(Table1.id) AS recordcount
However, when I insert =Count(Table1.id) into my default value area in the design view for my table, I get the following error:
assuming you've got an important "why" and you have zero concerns about two users trying to make a new record at the same time (A handles this with a "pure" autonumber, but your code is going to get complicated (a transaction??) if you attempt to duplicate this functionality) ...then write yourself a global function that returns the value you are looking for and try that as default value.
WARNING: i didn't try it (and i don't agree with the idea of doing it).
can't you get the same result safer & sweeter using a regular autonumber plus your funny-field: COUNT the records with autonumber less than your new record autonumber and stuff that value in the funny-field?