Hi! First time posting. I'm somewhat experienced with Access, but I do not use VBA - I just don't have time to learn/maintain the code because I don't develop in Access that often.
I am building a form for creating a budget. The main form has no record source and uses unbound combo boxes to select and filter the corporation/division/department and fiscal year so the user can enter the amounts for each cost code associated with that department/FY on a subform. The filtering and selection works great.
The subform uses a table as its record source with controls for all the relevant info (FY, department, cost code (i.e., ledger subaccount)) as well as the amounts requested under that budget line item.
The amounts consist of
1) Amount of money already obligated through contracts (contracts are maintained in a separate table) - this is where the problem is
2) Amount of money not obligated already; basically, this is the discretionary money requested under this line item.
3) Total amount requested or submitted (default value is intended to be the sum of #1 and #2)
4) Total amount approved (default is #3 but can be modified after budget is approved to reflect actual budget amount for the FY)
For #1, I have developed a query that will sum all the contracts assigned to that budget line item. The query works perfectly on its own; I run it, enter the BudgetID parameter (identifies the budget line item) and the total comes back beautifully.
However, when I attempt to set the control source for #1 to the query, the control shows #Name?. I attempted to create a nested subform with just the query as the control source, but I can't nest a subform onto a continuous form (which the first subform has to be). I also tried to create a query that combines the results of the query and the table; works perfectly except then I can't add new records.
In summary:
I need to set the control source for a control on a subform to a query that is NOT part of the subform's record source. Is that possible?
Thanks in advance,
Phil