Results 1 to 7 of 7
  1. #1
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Reference a Control on a subform? (2k1a)

    Hi Jim,

    DSum works on a table or query. You are trying to apply it to a subform; that is not possible. Instead, try applying it to the table or query behind the subform. Where is the text box ttat should display the sum, and what is the record source of the subform?

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Reference a Control on a subform? (2k1a)

    You can't do what you're trying to do because DSum is a domain aggregate function and isn't intended to do this. You DSum against a table or query and pass it a Where criterion like "[EmpNo] = " & Forms!Customers!JobDAta![AAMaintLabor sub ].form!EmpNo.
    Charlotte

  3. #3
    Star Lounger
    Join Date
    Aug 2001
    Location
    Cleveland, Ohio, Ohio, USA
    Posts
    76
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Reference a Control on a subform? (2k1a)

    (Edited by HansV to correct some formatting quirks)

    Help Please, I give up!

    Am working on Employee Time Tracking project. Each employee can work several different type of jobs on a job site and I want do a DSum on Time for each employee. This field is used by the data entry person to see if the total time for all the jobs for each employee equals the time at the job site - the field is not saved. I have used Dev Ashish's web site for refering to control on a form, but I just don't seem to get it right. Below is one form of the equation that I'm trying to use - I've tried several variations to this and all I get is #Error in the field.

    =DSum("[aamaintlabor sub].form!<!t>[time]<!/t>","aamaintlabor sub","[aamaintlabor sub].form![empno]=" & [empno]

    This subform is in a two tab Tab Control . The total form construction is below;

    Main form - Customers
    Subform on Customers - JobData
    Subform on JobData - MaintJob
    Tab Control on MaintJob - Tab Names - Labor and Equipment
    Form name on Labor Tab - AAMaintLabor sub - where I'm having trouble.

    I have used =Sum(<!t>[time]<!/t>) which works properly but it gives total time for all the Employees. While this can be used, it makes it hard to see where an error was made in entering each employee's time.

    Thank You for any assistance.
    Jim

  4. #4
    Star Lounger
    Join Date
    Aug 2001
    Location
    Cleveland, Ohio, Ohio, USA
    Posts
    76
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Reference a Control on a subform? (2k1a)

    Hans,
    Thank You for the reply. I think both you and Charlotte told me where I'm going wrong - can 't use DSum on the form. I added the text box to the subform and a query is the source.

    Thanks again
    Jim

  5. #5
    Star Lounger
    Join Date
    Aug 2001
    Location
    Cleveland, Ohio, Ohio, USA
    Posts
    76
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Reference a Control on a subform? (2k1a)

    Thanks, Charlotte. You and Hans said the same - I can't use DSum on the form. Will see if I can understand how to use your suggestion - I don't do good in SQL.

    Jim

  6. #6
    Star Lounger
    Join Date
    Aug 2001
    Location
    Cleveland, Ohio, Ohio, USA
    Posts
    76
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Reference a Control on a subform? (2k1a)

    Charlotte,

    I should have included the Query in my first replay. Stupid me.

    SELECT MaintLabor.MaintJobRelation, MaintLabor.EmpNo, [Employee tbl].FullName, MaintLabor.Time, MaintLabor.CodeNo, MaintLabor.Lunch, MaintLabor.ExtraTTime
    FROM [Employee tbl] INNER JOIN ([Labor Code Key tbl] INNER JOIN MaintLabor ON [Labor Code Key tbl].[Code #] = MaintLabor.CodeNo) ON [Employee tbl].EmpNo = MaintLabor.EmpNo;


    Thanks again
    Jim

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Reference a Control on a subform? (2k1a)

    Jim,

    To be able to help, we need to know
    a) the name of the query that is the record source of the subform - if the the record source of the subform is the SQL statement you posted, you should save it as a query.
    [img]/forums/images/smilies/cool.gif[/img] the name of the form that contains the text box - it is either Customers, JobData or MaintJob, I suppose, but which?

Posting Permissions

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