I have an axxess student database with 2 large forms{IAPp1Form&IAPp1Form}w/calcs figured in the queries. The problem is there are so many fields, including the calc functions in the query that I'm pushing the 255 max field cap. The forms function and track the vital values but when I try to pull a report (an exact replica of the form) from the same query it errors out w/ 'too many fields defined ...'
I'm trying to use VBA for the form/report calculations but NOT very successful. For instance...
To calculate the total number of honors/AP courses by query.......
{code}
TTLHAP10th: (IIf([THAP1],1,0)+IIf([THAP2],1,0)+IIf([THAP3],1,0)+IIf([THAP4],1,0)+IIf([THAP5],1,0)+IIf([THAP6],1,0)+IIf([THAP7],1,0)+IIf([THAP8],1,0)+IIf([THAP9],1,0)+IIf([THAP10],1,0)+IIf([THAP11],1,0)+IIf([THAP12],1,0)+IIf([THAP13],1,0)+IIf([THAP14],1,0)+IIf([THAP15],1,0)+IIf([THAP16],1,0)+IIf([THAP17],1,0)+IIf([THAP18],1,0)+IIf([THAP19],1,0)+IIf([THAP20],1,0)+IIf([THAP21],1,0))
{/code}
So I put together VBA.......??????
{code}
Public Function TTLHAPTen()
' Returns the Total honors/AP courses 10thgrade
TTLHAP10th=(IIf(Me.[THAP1],1,0)+IIf(Me.[THAP2],1,0)+IIf(Me.[THAP3],1,0)+IIf(Me.[THAP4],1,0)+IIf(Me.[THAP5],1,0)+IIf(Me.[THAP6],1,0)+IIf(Me.[THAP7],1,0)+IIf(Me.[THAP8],1,0)+IIf(Me.[THAP9],1,0)+IIf(Me.[THAP10],1,0)+IIf(Me.[THAP11],1,0)+IIf(Me.[THAP12],1,0)+IIf(Me.[THAP13],1,0)+IIf(Me.[THAP14],1,0)+IIf(Me.[THAP15],1,0)+IIf(Me.[THAP16],1,0)+IIf(Me.[THAP17],1,0)+IIf(Me.[THAP18],1,0)+IIf(Me.[THAP19],1,0)+IIf(Me.[THAP20],1,0)+IIf(Me.[THAP21],1,0))
End Function
{/code}
This returns a 'Me' error when I try to compile
I take the Me out, and I get 'external name not defined'
Plus once I get the function to operate I still need direction on what event to place it in the form?
If anyone has suggestion on how to formlate the query anymore effectively or solution on the VBA method,
thank you in advance.