Some suggestions:
I would fix the naming issues (fields, tables, queries)
I really don't like the compound PKs. See Microsoft Access Tables: Primary Key Tips and Techniques
It looks like you have designed the tables like the spreadsheet; this actually has a name. It is termed "Committing spreadsheet" and is a non-normalized design.
For instance, will you ever have more than 5 entries (OutStart6 or OutStart7)? If yes, then it means you will have to redesign your fields, tables, queries, forms, reports and possible code because you do not have a normalized structure.
Looking at table "Adm48+", you could set the default value of the number fields to 0 (zero). Any NEW record would automatically have a value of 0, so the number field (in a new record) would never be NULL. Therefore you would not have to have very long IIF() functions.
But every field/control would have a 0 displayed. If you don't want to see the zeros, there is another alternative: you could use the NZ() function. This function changes a NULL to what ever you decide you want. In the form "AdmOBalance" you have a control named "TotalIssued".
The control source is
Code:
=IIf(IsNull([TotalIssued1]) And IsNull([TotalIssued2]) And IsNull([TotalIssued3]) And IsNull([TotalIssued4]) And IsNull([TotalIssued5]),Null,IIf(IsNull([TotalIssued1]),0,[TotalIssued1])+IIf(IsNull([TotalIssued2]),0,[TotalIssued2])+IIf(IsNull([TotalIssued3]),0,[TotalIssued3])+IIf(IsNull([TotalIssued4]),0,[TotalIssued4])+IIf(IsNull([TotalIssued5]),0,[TotalIssued5]))
Whew!!! Using the NZ() function, you would have
Code:
=NZ([TotalIssued1],0) + NZ([TotalIssued2],0) + NZ([TotalIssued3],0) + NZ([TotalIssued4],0) + NZ([TotalIssued5],0)
If the field "TotalIssued1" is NULL, the function will return a 0 (zero) instead of a NULL.
And in the query "Adm+Calcs", there is a calculated field "UsageAdmO"
Code:
UsageAdmO: (IIf(IsNull([TotalIssued1]) And IsNull([TotalIssued2]) And IsNull([TotalIssued3]) And IsNull([TotalIssued4]) And IsNull([TotalIssued5]),Null,IIf(IsNull([TotalIssued1]),0,[TotalIssued1])+IIf(IsNull([TotalIssued2]),0,[TotalIssued2])+IIf(IsNull([TotalIssued3]),0,[TotalIssued3])+IIf(IsNull([TotalIssued4]),0,[TotalIssued4])+IIf(IsNull([TotalIssued5]),0,[TotalIssued5])))-(IIf(IsNull([TotalReturned1]) And IsNull([TotalReturned2]) And IsNull([TotalReturned3]) And IsNull([TotalReturned4]) And IsNull([TotalReturned5]),Null,IIf(IsNull([TotalReturned1]),0,[TotalReturned1])+IIf(IsNull([TotalReturned2]),0,[TotalReturned2])+IIf(IsNull([TotalReturned3]),0,[TotalReturned3])+IIf(IsNull([TotalReturned4]),0,[TotalReturned4])+IIf(IsNull([TotalReturned5]),0,[TotalReturned5])))
This could be replaced with
Code:
UsageAdmO: (NZ([TotalIssued1],0) + NZ([TotalIssued2],0) + NZ([TotalIssued3],0) + NZ([TotalIssued4],0) + NZ([TotalIssued5],0)) - (NZ([TotalReturned1],0) + NZ([TotalReturned2],0) + NZ([TotalReturned3],0) + NZ([TotalReturned4],0) + NZ([TotalReturned5],0))
(a lot shorter)
Good luck with your project...