Page 1 of 2 12 LastLast
Results 1 to 15 of 23
  1. #1
    Johnw is offline Novice
    Windows 10 Access 2016
    Join Date
    Sep 2016
    Posts
    11

    Cannot get DSUM function to display and then save to textbox in main form. HELP!

    Not sure what I am doing incorrectly.... Any assistance would be appreciated



    I am designing a "Service Orders" database which is mostly completed but I am having troubles getting a few of the key remaining but very important details completed. Hopefully my wording is not too confusing. PS: Running Access 2016.

    I have a main form named [frmOrders] with an included subform named [frmLabor] within it. In the subform [frmLabor]'s control source table (named [tblLabor]) there is a calculated field named "SubtotalTime" that calculates the difference in time between 2 other fields ("StartTime" and "EndTime") which is displayed in decimal format. It's expression is: Round(([EndTime]-[StartTime])*24,4). This works fine and is displayed in the sub-form [frmLabor] for each of the main form's filtered records (only the labor records associated with the current [frmOrders]'s client name). Again, hopefully this is not confusing...

    Within my main [frmOrders] form but outside of the [frmLabor] subform there is a text box named "TotalLaborTime" (formatted as General Number) that I am trying to display the SUM of all of the table [tblLabor]'s "SubtotalTime" field entries that are filtered for the current service order. I would like to have the sum result stored in the [tblOrders] table's "TotalLaborTime" field and not just display it on the form without saving it.

    I have tried to go about this in a couple of different ways: I have tried entering the expression: =DSum("[tblLabor]![SubtotalTime]","[tlbLabor]") in the [frmOrder]'s "TotalLaborTime' text box's control source (which would only display the result sum but not store it in the [tblOrders]'s "TotalLaborTime" field). For simplicity I temporarily I left out the criteria section in the DSUM expression so that the sum would be of all of the [tblLabor]'s "SubtotalTime' fields and not just the sum of the current service order's "SubtotalTime"field. When viewing the form, the field displays "#ERROR" which flashes on and off rapidly. For what it's worth, from reading people's comments about this error, I don't believe that I have any other controls that have the same name.

    I also tried entering the DSUM expression as the Default Value in the [frmOrders]'s "SubtotalTime" text box with its control source set to the [tblLabor]'s "SubtotalTime" field.There was no error shown in the text box but also there was no sum displayed.

    Bottom line is that I need this total number of hours for all labor records for the current order # in order to calculate a total labor cost by multiplying the total hours by the labor rate. I would have attached my database with my post but the forum restricts the size of upload documents so much that I cannot even upload an extremely basic database with just a few sample records in it. Thanks again for anyone that can help!

  2. #2
    Bulzie is online now VIP
    Windows 7 64bit Access 2007
    Join Date
    Nov 2015
    Posts
    1,463
    Maybe one way is to use the AfterUpdate event of the subform frmLabor so each time you add or change records/values in frmLabor, it will recalculate and save the value in tblOrder. The syntax below is not all there but maybe something like this:

    dbs.Execute "UPDATE tblOrders SET TotalLaborTime = DSum("SubTotalTime","tblLabor", "OrderID = " & Me.OrderID) Where OrderID = " & Me.OrderID

    Forms![frmOrder].Requery

  3. #3
    Johnw is offline Novice
    Windows 10 Access 2016
    Join Date
    Sep 2016
    Posts
    11
    Thanks Bulzie for responding. In VBA I am trying to add your code to the [frmLabor]'s AfterUpdate event but I get a "'Compile error: Expected: end of statement" error on the first line with the text "SubtotalTime" highlighted. Being a newbee programmer, I am not familuar wint the first part of the line (dbs.Execute). What am I missing?

  4. #4
    Bulzie is online now VIP
    Windows 7 64bit Access 2007
    Join Date
    Nov 2015
    Posts
    1,463
    Add this to the top:

    Dim dbs as Database
    Set dbs = CurrentDb

    Then test out the DSUm part to get that right. I am not sure the syntax is right so you need to test it and change as needed. Maybe others can spot any syntax issues off the bat.
    Add the below at the top to test that Dsum. Put a breakpoint on it etc. See if x gives you the correct value to know if that DSum line is right.
    x = DSum("SubTotalTime","tblLabor", "OrderID = " & Me.OrderID)

  5. #5
    Johnw is offline Novice
    Windows 10 Access 2016
    Join Date
    Sep 2016
    Posts
    11
    Bulzie,

    This is the coding in my [frmLabor]'s AfterUpdate event. When run, it gets stuck with the "Compile error: end of statement" error and "Where" is highlighted in the code.
    If I put the: Where OrderID = " & Me.OrderID" on a new line just below the rest, VBA doesn't highlight either of the lines in red and doesn't show the compile error.

    Here is the current code:

    Private Sub Form_AfterUpdate()
    Dim dbs As Database
    Set dbs = CurrentDb
    dbs.Execute "UPDATE tblOrders"
    Set TotalLaborTime = DSum("SubTotalTime", "tblLabor", "OrderID = " & Me.OrderID)Where OrderID = " & Me.OrderID"
    Forms![frmOrder].Requery
    End Sub

    If you wouldn't mind, I could email you the database so that you can examine it better. I could pay you. Let me know. My email address is john@thewellsfamily.com

  6. #6
    John_G is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2011
    Location
    Ottawa, ON (area)
    Posts
    2,615
    Code:
    dbs.Execute "UPDATE tblOrders SET TotalLaborTime = DSum("SubTotalTime","tblLabor", "OrderID = " & Me.OrderID) Where OrderID = " & Me.OrderID
    The syntax of this statement is incorrect. Because the bold part of the string contains embedded quotation marks, those quotes inside the string must be doubled, like this:

    dbs.Execute "UPDATE tblOrders SET TotalLaborTime = DSum(""SubTotalTime"",""tblLabor"", ""OrderID = "" & Me.OrderID) Where OrderID = " & Me.OrderID

  7. #7
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    I would write the procedure a little different:
    Code:
    Private Sub Form_AfterUpdate()
        Dim dbs As DAO.Database
        Dim sSQL As String
        Dim TotalTime As Double   '<<-- not sure what the type should be
    
        Set dbs = CurrentDb
    
        TotalTime = DSum("SubTotalTime", "tblLabor", "OrderID = " & Me.OrderID)
    
        sSQL = "UPDATE tblOrders"
        sSQL = sSQL & " SET TotalLaborTime = " & TotalTime
        sSQL = sSQL & " WHERE OrderID = " & Me.OrderID
        '  Debug.Print sSQL
    
        dbs.Execute sSQL, dbFailOnError
    
        Forms![frmOrder].Requery
    
        Set dbs = Nothing
    End Sub
    Or a couple of lines shorter:
    Code:
    Private Sub Form_AfterUpdate()
        Dim sSQL As String
        Dim TotalTime As Double   '<<-- not sure what the type should be
    
    
        TotalTime = DSum("SubTotalTime", "tblLabor", "OrderID = " & Me.OrderID)
    
        sSQL = "UPDATE tblOrders"
        sSQL = sSQL & " SET TotalLaborTime = " & TotalTime
        sSQL = sSQL & " WHERE OrderID = " & Me.OrderID
        '  Debug.Print sSQL
    
        CurrentDb.Execute sSQL, dbFailOnError
    
        Forms![frmOrder].Requery
    
    End Sub
    This way you can uncomment the Debug statement, set a breakpoint, single step through the code and see if the SQL statement is properly formed.

  8. #8
    Johnw is offline Novice
    Windows 10 Access 2016
    Join Date
    Sep 2016
    Posts
    11
    Thanks Steve (ssanfu) very much for your response! I have replaced the code I had in the After Update event of my [frmLabor] subform with yours. When I open my main form, it shows nothing (blank) in my TotalLaborTime field even though all of the subform [tblLabor]'s "SubtotalTime' fields are populated with numbers (formatted as Type Double). Not sure what I am missing...

  9. #9
    Johnw is offline Novice
    Windows 10 Access 2016
    Join Date
    Sep 2016
    Posts
    11
    John_G, I tried entering double quotes around SubTotalTime, tblLabor and, OrderID as you suggested. Unfortunately it didn't seem to make any difference. There must be something else going on. I'll keep at it until I can get it to work (with all of your help!). I am creating this DB for my own business needs and I really need it to work Thanks again!

  10. #10
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Did you single step through the code, checking the value for "TotalTime"?
    Did you uncomment the debug line and check the SQL??
    Is "tblOrders.TotalLaborTime" updated correctly?
    What is the data type in the table "tblOrders" for the field "TotalLaborTime"?

    Checking the main form:
    If you open "tblOrders" and enter 123.99 (or pick a number that is unique from other records) for the "TotalTime", close the table, then open the form "frmOrder", select the orderID, is 123.99 when the orderID is selected?

  11. #11
    Johnw is offline Novice
    Windows 10 Access 2016
    Join Date
    Sep 2016
    Posts
    11
    ssanfu, I apologize, I am a newbe and I don't know what I am doing

    I compiled and tried to step through the code both with the Debug.Print sSQL line commented in and out but each time nothing happened.
    I was unable to hover the mouse over the tblOrders.TotalLaborTime code to check it's value.

    As to your last suggestion to check the main form: In the tblLabor table the calculated field that calculates the difference between the StartTime field and the EndTime field is named SubtotalTime. It's expression is "Round(([EndTime]-[StartTime])*24,4)" and works fine.

    In the tblOrders table I have a field named TotalLaborTime that is set as General Number/Double and set to 4 decimal places. This is where I would like the SUM of the tblLabor form's SubTotalTime for filtered for that OrderID only.
    In my frmOrders main form I also have a text field named TotalLaborTime which is where I would like to have the value of the tblLabor form's TotalLaborTime displayed. That is straightforward.

    UNLESS:


    The SUM of the tblLabor form's SubTotalTime field needs to be calculated within the subform frmLabor (which is in the main frmOrders form).
    Do I have to open and refresh the main frmOrders form in order to save the TotalLaborTime's value to the tblOrders table's TotalLaborTime field?

    I'm lost in the ozone...
    Thanks again and I would understand if you give up on me

    John

  12. #12
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Can you make a copy of your dB and post it here?

  13. #13
    Johnw is offline Novice
    Windows 10 Access 2016
    Join Date
    Sep 2016
    Posts
    11

    CP Service Orders DB

    Here's a zipped copy (unzipped too large).
    Thanks
    Attached Files Attached Files

  14. #14
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    I noticed a few things:

    The DSum():
    TotalTime = DSum("SubTotalTime", "tblLabor", "OrderID = " & Me.OrderID)
    The table in the database window is named "tlbLabor" <<<---- spelling!!

    ---------------------

    EVERY module should have two lines at the top.
    These two lines are important:
    Code:
    Option Compare Database
    Option Explicit
    ---------------------

    Shouldn't use spaces in objectnames.
    Shouldn't use punctuation in object names.
    Shouldn't use special characters in object names.

    ---------------------

    "Date" is a reserved word in Access. (Used in table "tblContracts")

  15. #15
    Johnw is offline Novice
    Windows 10 Access 2016
    Join Date
    Sep 2016
    Posts
    11
    I check and recheck and recheck and I still miss those stupid things! Thanks ssanfu.
    I made the changes you suggested:
    Renamed the table tlbLabor to tblLabor and checked that it's form's source had changed (yes).
    The DSum expression in the frmLabor's After Update event looks okay so no changes except that I did add the Option Explicit line to the General section (Option Compare Database was already there).
    In the table tblContracts I changed the name of the field Date to ContractDate. I'd forgotten that you shouldn't name fields with reserved words. Thanks.
    I don't believe that I have any object names with spaces between words. I used to put "_" between words but I think that all my objects use no spaces but with capitol letters for each word.

    When I open the main frmOrders form now, it still doesn't display anything in the TotalLaborTime field. Not sure what I am missing now.

    Attached is my latest DB file.

    Thanks so much for helping me. I really appreciate it.
    Attached Files Attached Files

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

Similar Threads

  1. Save form textbox values
    By NISMOJim in forum Programming
    Replies: 4
    Last Post: 07-30-2015, 04:48 AM
  2. Replies: 5
    Last Post: 04-30-2015, 01:50 AM
  3. Replies: 2
    Last Post: 01-08-2014, 05:18 PM
  4. Replies: 7
    Last Post: 11-07-2011, 06:31 AM
  5. Replies: 9
    Last Post: 01-20-2011, 02:22 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