Limit to Number of Calculated Fields

[john.voisey]

I only seem to be able to add some 30 calculated fields to a form or report. Anything over this returns a #Name? error. I've seen this before but have searched this and other forums but cannot find an answer. I suspect a memory issue but I have 2 gb on Pc. Any Ideas?

 

[Rich]

Why would you want that many calculated fields?

 

[john.voisey]

Golf

I am building a base for a golf society that keeps records of members, golf course, competitions and scores.
We use the Stableford method of scoring points for each hole based on a players handicap therefore we need to calculate the net score, ie the score for the hole minus the stroke recieved for the hole based on the handicap and the points received. This means that for an 18 hole golf course we need a minimum of 2 calculated field for each hole. Thats 36 to start. Add the totals for the outward 9 and the inward 9 and the 18 holes you have around 42 Minimum.

 

[The_Doc_Man]

Treat each hole as a separate entity (if you can), build a sub-form (perhaps in datasheet view) for one round, and then have only the one calculated field per hold. Do summation queries for the inward, outward halves and the total.

 

[john.voisey]

Hi Doc Man
This is the way that I first created the database. Better structure and no problem with calculated fields as there only 2 or 3 per hole. Its a question of presentation. The form looks naff with a subform. If you are familiar with golf its much better to present a golf score card on screen which you cannot do with subforms. At least I can't do with subforms. If you know better ..............

 

[Rich]

I don't know enough about golf to offer any help but I'm sure a db structure for golf has been discussed here way back. Try searching the archives

 

[Jonathan Kok]

Make your subform appear as 'Continuous Forms' instead of 'Datasheet' (the default format). You can then format it however you'd like.

 

[The_Doc_Man]

Good call, Jonathan

 

[john.voisey]

Thanks Jonathan but you cannot place subform fields anywhere on the main form, they can only appear in the subform predefined area. I need to place the field or records in this case in very set areas

 

[Jonathan Kok]

True, true...you could always split it up into a few seperate subforms, though. If you really wanted, you could (in theory) have 18 subforms filtered on the specific hole. I know there's a limit on nested forms, but not sure on the quantity of subforms. You may run into the same problem.
This may even be the way to go, as with continuous subforms, you could then display more than one score for the same hole (should they play the course twice).

 

[john.voisey]

Thanks to everyone for all the suggestions but have found the problem.
Its not the number of calculated fields but the method used to create them that was the problem. Once I had set up the parameters or layout for the first hole I did a copy/paste to hole 2 etc. When you do this Access automatically reassigns a new name to the field and everything works fine. However, this only works a few times and thats when you get an error message.
I eventually found reference to this in the Microsoft Knowledgebase for Access 2002 and as long as I create new calculated fields from scratch all is well.
I assume all these bugs have been cleared in the new version ie Access 2007.