Results 1 to 7 of 7
  1. #1
    pncampbell is offline Novice
    Windows 10 Office 365
    Join Date
    Jul 2020
    Posts
    2

    Using a total query result in a subform control where the subform is based on another table

    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

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Can't set ControlSource to a table or query, only field or expression (e.g. =qty * price). Use DLookup() expression to pull value from table or query.

    =DLookup("fieldname", "table or query name", "criteria here")

    Do some research on domain aggregate functions.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    pncampbell is offline Novice
    Windows 10 Office 365
    Join Date
    Jul 2020
    Posts
    2
    Thanks so much for the quick reply! Forgot to say, I set the controlsource to the calculated field in the query, but it just showed a null.

    Tried to use DLookup but it wouldn't grand total...so I took another look at DSUM. Tried DSUM originally in a query but couldn't get it to work at all. Set the ControlSource directly to DSUM and I guess something clicked because it worked! So awesome!

    One follow up though: in the new record line of my continuous form, the DSUM throws an error (no BudgetID to match against, I guess). Is it possible to hide the #Error until BudgetID is populated? Really appreciate your help.

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Just referencing field of table or query not part of RecordSource is not appropriate. How should Access know which record to pull from? That's what domain aggregate functions are meant for.

    I suggested DLookup because you said you had a query with sums calculated.

    Why is this showing on NewRecord row?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    PMFJI,

    I would probably write a UDF (user defined function) for #1. I know you said you didn't use VBA, but it would/could be easier.

    Any chance you would make a copy of your dB and post it?



    And Welcome to the Forum....

  6. #6
    pncbiz is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Jul 2020
    Location
    Maryland
    Posts
    66
    This is the OP. I registered with a .me account, which apparently can't receive emails. And I couldn't get my password reset emails, so I couldn't log in...blah blah blah. Anyway, DSUM worked. Would mark this solved if I could!

  7. #7
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Thread Tools on menu above first post.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 8
    Last Post: 06-19-2015, 02:19 AM
  2. Replies: 1
    Last Post: 06-12-2015, 12:03 AM
  3. Replies: 1
    Last Post: 01-12-2015, 01:32 PM
  4. Total Control Value in SubForm
    By libraccess in forum Forms
    Replies: 4
    Last Post: 06-06-2012, 01:34 AM
  5. Replies: 4
    Last Post: 10-18-2011, 10:18 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums