Results 1 to 7 of 7
  1. #1
    Join Date
    Mar 2008
    Posts
    3

    Unanswered: haw to read datasheet values in Form

    Hi
    I'm new in Access, please help with code

    I have subform in datasheet view. In subform I created function:

    Public Function getLineTotal() As String

    getLineTotal = Me.Count & "_" & Me.listLineTotal

    End Function

    It returns 14_290.
    I have 14 records in datasheet

    290 it is value of listLineTotal selected item in datasheet.

    Haw to get sum of all values column listLineTotal in datasheet?

  2. #2
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    =Sum(listLineTotal) in the Form Footer or Form Header sections will give you the total for that field. Then you need only reference this in your mainform to show the total.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  3. #3
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    To add to StarTrekker's post - you need to put the =sum(xxxx) in the Form Footer or Form Header. These are the places where you can put equations against the records in the detailed section such as sum(FieldXXX). If you put this equation in the detail section, it won't work - thus, you put the textbox field with the equation in the Footer/Header and then in the detail area have a textbox field which references the sum(xxxx) field (i.e. for the unbound textbox field in the detail section you would have =MySummedFormFooterName (or =Forms!MyFormName!MySummmedFormFooterName) where MySummedFormFooterName is what you called the textbox containing the =sum(xxxxx) in the Header/Footer.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  4. #4
    Join Date
    Mar 2008
    Posts
    3
    Hi
    Thanks for replay

    in my subform in footer I created a text field Name : "listSubtotal" with control Source: "=Sum([listLineTotal])"

    the footer has Name : "FormFooter"

    in my main form I created text field "fromListSubtotal" with Control Source

    "=[invList Subform]![FormFooter]![listSubtotal]" where "invList Subform" the Name of subform in main form

    it doesnt' work
    I have " #Name?" in my fromListSubtotal


    another way I did use VB:
    in main form:

    Code:
    Private Sub getSubtotalBtn_Click()
    Me![fromListSubtotal] = Me![invList Subform].Form.getLineTotal
    End Sub
    in subform:

    Code:
    Public Function getLineTotal() As Currency
    
    Dim mySubTotal As Currency
    
    
    Dim rst As DAO.Recordset
    Dim strSearchName As String
    
    Set rst = Me.Recordset
    rst.MoveFirst
    
    Do Until rst.EOF
    mySubTotal = mySubTotal + rst![listLineTotal]
    
    rst.MoveNext
    Loop
    
    getLineTotal = mySubTotal
    rst.MoveFirst
    
    End Function
    it works but slow and I can see on my screen haw it jumps from one record to another. I wanted tight this calculation to Change Event in cell of subForm but with such performance seems like I cant.

    Haw can I do it?

  5. #5
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    In the main form create textbox control "fromListSubtotal" with Control Source

    "=[invList Subform]![listSubtotal]"

    It should work.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  6. #6
    Join Date
    Mar 2008
    Posts
    3

    Yes it works

    Thank you StarTrekker

    It works now

  7. #7
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    You're welcome
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •