Results 1 to 13 of 13
  1. #1
    s@s is offline Novice
    Windows 10 Access 2016
    Join Date
    Nov 2020
    Posts
    14

    does an agregate function in a query make the relevant form read only?

    it makes sense that the above is true but how can we achieve the following if so?



    Click image for larger version. 

Name:	Screenshot 2020-11-23 134132.png 
Views:	12 
Size:	12.7 KB 
ID:	43487

    so i got tables: services, collections. for each service we may have many collections.

    in each service line inside the form, i need to display a column with total of collections.

    i managed to display the totals with a query with sum/group-by or even with a sub-query in my main query but the moment I do that, my form gets read only!

    i want to avoid making a new form for data entry and enable the user to add/modify records through the depicted list-like form if possible

    could i unbound the total field and vba-calculate it for each line?

    any ideas/work around?

    thx!

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    Yes ,aggregates tend to be read only.

  3. #3
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows 10 Access 2016
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,530
    ....but the moment I do that, my form gets read only!....
    That's because it it showing the result of a calculation. To change the result of that calculation you would need to change (add/edit) the related records in the "amount" field of table "collections". Perhaps a "Main" form "Sub" form arrangement would suit your needs.
    You can't have a subform on a datasheet form but you could have a main form with a subform in datasheet view for each of the tables, set up so that collections form only shows the data related to the selected record of the services form.
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

  4. #4
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows 10 Access 2016
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,530
    That's because it it showing the result of a calculation. To change the result of that calculation you would need to change (add/edit) the related records in the "amount" field of table "collections". Perhaps a "Main" form "Sub" form arrangement would suit your needs.
    You can't have a subform on a datasheet form but you could have a main form with a subform in datasheet view for each of the tables, set up so that collections form only shows the data related to the selected record of the services form.
    See attached db
    Attached Files Attached Files
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

  5. #5
    accesstos's Avatar
    accesstos is offline Expert
    Windows XP Access 2007
    Join Date
    Dec 2018
    Location
    Greece
    Posts
    551
    Quote Originally Posted by s@s View Post
    i managed to display the totals with a query with sum/group-by or even with a sub-query in my main query but the moment I do that, my form gets read only!
    So, did you try to use a DSUM(...) for the total field "Collections"?
    Domain Aggregate functions (DSUM, DCOUNT, MAX/MIN etc) are suggested for editable queries with totals but could slow down the performance.

    Cheers,
    John

  6. #6
    s@s is offline Novice
    Windows 10 Access 2016
    Join Date
    Nov 2020
    Posts
    14
    Quote Originally Posted by Bob Fitz View Post
    but you could have a main form with a subform in datasheet view for each of the tables, set up so that collections form only shows the data related to the selected record of the services form.
    This makes sense but, displaying the total collections on the services level helps the user choose what line they need to select and drill down into the relevant collections. Having to click in each service in order to to see all related collections and then decide where they need to go is what im trying to help them avoid.

    as a possible solution i thought i coul add the total_collections in the services table which i would need to calculate and store there each time a user exits the collections form.

  7. #7
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows 10 Access 2016
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,530
    Quote Originally Posted by s@s View Post
    This makes sense but, displaying the total collections on the services level helps the user choose what line they need to select and drill down into the relevant collections. Having to click in each service in order to to see all related collections and then decide where they need to go is what im trying to help them avoid.

    as a possible solution i thought i coul add the total_collections in the services table which i would need to calculate and store there each time a user exits the collections form.
    Have you seen the example that I attached in post #4
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

  8. #8
    s@s is offline Novice
    Windows 10 Access 2016
    Join Date
    Nov 2020
    Posts
    14
    Quote Originally Posted by accesstos View Post
    So, did you try to use a DSUM(...) for the total field "Collections"?
    hm this sounds interesting, i will look it up...

  9. #9
    s@s is offline Novice
    Windows 10 Access 2016
    Join Date
    Nov 2020
    Posts
    14
    Quote Originally Posted by Bob Fitz View Post
    Have you seen the example that I attached in post #4
    i am now... let you know what i think

  10. #10
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows 10 Access 2016
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,530
    Quote Originally Posted by s@s View Post
    hm this sounds interesting, i will look it up...
    That's what I'm using in the example that I posted
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

  11. #11
    s@s is offline Novice
    Windows 10 Access 2016
    Join Date
    Nov 2020
    Posts
    14
    ha! you are both sugesting dsum, very interesting! i think this will do... i will play around with it a bit...

  12. #12
    s@s is offline Novice
    Windows 10 Access 2016
    Join Date
    Nov 2020
    Posts
    14
    Yes that's exactly what i was looking for!

    Interesting, the field got auto-locked as well as it doesnt make sence to edit it now.

    Excelent, thx both!

    PS
    you just made me reconsider my decision to make vba the default editor when clicking the expression editor like in the case above!

  13. #13
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows 10 Access 2016
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,530
    Quote Originally Posted by s@s View Post
    Yes that's exactly what i was looking for!
    Glad we were able to help
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

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

Similar Threads

  1. Replies: 6
    Last Post: 05-17-2018, 02:08 PM
  2. Make subreport read only on a form
    By BRZ-Ryan in forum Forms
    Replies: 2
    Last Post: 12-09-2013, 03:15 PM
  3. Button on a Form to make record read only
    By billgyrotech in forum Forms
    Replies: 8
    Last Post: 08-08-2011, 03:28 PM
  4. Best way to make a query read only?
    By Remster in forum Queries
    Replies: 4
    Last Post: 12-17-2010, 04:36 AM
  5. Replies: 2
    Last Post: 10-19-2006, 04:37 PM

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