Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Anil Bagga is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Apr 2020
    Posts
    69

    Issue in Totals in a subform - Continuous or Datasheet?

    I have enclosed a DB with only the tables associated with the forms. The other tables linked to the forms are not enclosed to make the file lightTestDBTotalissue.zip

    I wanted total of 2 fields in the footer of the Sub form - SO Value and Weight

    The original subform was in datasheet view. The footer was not showing. When I switched the subForm to a continuous form, as I read somewhere, I could see the footer with total fields.



    However the function to increase the height of the rows is not functioning. The row height function works in datasheet view. There is one field which is long and I wanted the text to flow down. Somehow the Can Grow configuration does not work and I also tried to change the format using Anchoring. So I used VBA to increase the height and it worked.

    In a nutshell

    Is it possible to have sub totals in the bottom of the Sub form? If yes now

    If continuous form is the only option, how can I get the row height to increase (best it increases automatically based on text length)

  2. #2
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    in the footer of the subform , put a textbox w source =Sum(field)

  3. #3
    Anil Bagga is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Apr 2020
    Posts
    69
    Raman

    I have done that. I can see totals in a continuous form . I want to see the total in the datasheet view of Subform so that I can size the height of the rows

  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
    You can not show the footer section when in datasheet view but you can put a text box on the main form and and set its Record Source property to reference the textbox on the footer of the suform. Something like: =[SubFormName].[Form]![FooterFieldName]
    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
    Anil Bagga is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Apr 2020
    Posts
    69
    A) I am enclosing the DB with the main form, the sub form and the 2 related tables. I have some issues as under and have struggled for last 2 days but could not solve the problem. I just cant understand what is going wrong

    1. The subtotal is showing an error on the sub form. The values of individual records are showing in the sub form and all I did was copied the formula of the fields and put =sum(formula of the field) in the text field in the footer.
    2. Naturally I get an error in the main form when I call this value. Is this formula correct for the main form - =[CustSODtlsfrm].[Form]![SOValue] where CustSODtlsfrm is the name of the Sub form and SOValue is the name of the text box in the footer which would hold the sum value

    B) There are some other queries too

    1. I cannot reorder the columns in the Sub form. The column order does in the form view is NOT as per the tab order , nor as per Design view
    2. The Sub form is wide and when I tab to fields outside the current view, the form does not autoscroll and i need to manually scroll to see the fields. Is this normal?
    3. How do you reference a main form field in the footer? I used [CustSOHdrTbl].[EndCustCode] in the query form of footer. Logically one should be referring to the Form and not the table but I cannot reference the form. I want to use the form value in the criteria of a combo box query of the footer.
    4. How do I requery the footer cbo field in the header combo box? the 2 fields in main form and subform are in a cascading combo box relationship. The subform field is supposed to search for values in a table based on the value of cbo in mainform! Will the code [CustSODtlsFrm].[Form]![Category].Requery be correct in Afterupdate of the main form cbo box where CustSODtlsFrm is the Subform and Category is the field in Subform
    Attached Files Attached Files

  6. #6
    Join Date
    May 2018
    Location
    Living in Scotland UK
    Posts
    1,557
    Hi Anil

    The Forms were corrupt.

    I recreated these 2 Forms.

    In the modified database attached I based the subform on a Record Source.

    I then modified the Record Source to do the Calculation for Item Rate. I named this Calculation "New Item Rate"

    See the attached.
    Attached Files Attached Files

  7. #7
    Anil Bagga is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Apr 2020
    Posts
    69
    Hi Mike

    Many thanks. Taking all this trouble to recreate is exceptional!

    I notice that the sub form is continuous. Can it be in datasheet or has to be continuous? The reason I had opted for the datasheet was row height. Can row height be increase - as you can see one of the fields is big - 24 characters and need the field to grow based on the data

    You created the sub form with a query instead of putting the formulae in the text boxes like I did. Is this the best option - the query mode?

    Can these subtotals be called on the main form. If yes how?

    I have a couple of more questions but will ask them later

  8. #8
    Join Date
    May 2018
    Location
    Living in Scotland UK
    Posts
    1,557
    Hi Anil

    If you PM me I will talk you through your problems.

  9. #9
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Had no problem with forms in db from post 5.

    Form sections and controls on forms do not grow. Textboxes on form in Single or Continuous view will activate a vertical scroll bar when text is more than textbox can display but shows only when textbox has focus. Detail section and textboxes on reports can grow.

    Doing calcs in query might improve performance but not that I've ever noticed difference.

    A form in Datasheet can display aggregate total at bottom by simply clicking the Totals icon on ribbon and selecting whatever function you want showing from dropdown list at bottom of column. However, if the column is a calculation in textbox, some functions will not be available. This is another reason for doing calcs in query instead of textbox. Since there is no textbox involved, cannot reference this calculation anywhere else.

    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.

  10. #10
    Anil Bagga is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Apr 2020
    Posts
    69
    1. In datasheet one can increase row height. Possible in Continuous form?

    2. In a continuous form I see the scroll bar in the cell with large text length, but the text does not scroll. What could be the reason?

    3. I used the following formula in the footer for summing a column. All field names used in the formula are numbers , not calculated values. That should work, right? I wonder why it does not. I used the expression builder so the field names are not misspelt

    =Sum(([Qty]+[QtyChange])*[UnitWeightKg])

    4. If the text field in the footer is say SOWeight and sub form name is SOdtlsfrm, then the formula in the main form text box as below would be correct?

    =[SOdtlsfrm].[Form]![SOweight]

  11. #11
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    1. Not dynamically, user cannot change, sizes are set in design view

    2. Have to click on the scroll arrows

    3. Remove calculations in the other footer textboxes then see what happens

    4. Must reference subform container control that holds form - if container and form have same name, yes, this should work, however, your container is named [SO Details]
    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.

  12. #12
    Anil Bagga is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Apr 2020
    Posts
    69
    1. Done. Checked
    2. Done
    3. Checked in new DB
    4. Checked in new DB

    I will now test this subform in the main form.

    How do I setup a cascading combobox between main and sub form. I need to put a criteria in the subform using values of cbomain field and also requery the cbosubform in the events of cbomain field

    Ultimately I need to use this in my split form. Would any other precaution be needed if we use the sub form in a split form fe file?

    Thanks June7
    Last edited by Anil Bagga; 05-29-2020 at 09:52 PM. Reason: missed one point

  13. #13
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Don't appreciate split form much, only ever used it in couple of very simple dbs and never had a subform on it.
    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.

  14. #14
    Anil Bagga is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Apr 2020
    Posts
    69
    The application I am developing is multi user and the be file will be on a server to allow simultaneous usage of forms. The split database is the only solution I understand

  15. #15
    Anil Bagga is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Apr 2020
    Posts
    69
    The application I am developing is multi user and the be file will be on a server to allow simultaneous usage of forms. The split database is the only solution I understand

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. updating subform in datasheet/Continuous view
    By Avizan05 in forum Programming
    Replies: 2
    Last Post: 05-02-2014, 07:04 AM
  2. Totals in a subform (continuous Form)
    By scoe in forum Forms
    Replies: 26
    Last Post: 07-08-2013, 07:42 AM
  3. Issue with using a datasheet in a subform
    By Meichmann in forum SharePoint
    Replies: 2
    Last Post: 01-17-2012, 07:26 AM
  4. Replies: 4
    Last Post: 01-14-2011, 10:37 AM
  5. Subform, Totals, in Datasheet view
    By eww in forum Programming
    Replies: 1
    Last Post: 09-27-2010, 10:22 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