Results 1 to 12 of 12
  1. #1
    Join Date
    Jan 2010
    Posts
    6

    Unanswered: Subform calculations per JobNo to show on main form?!?

    New member alert, am a bit of a novice with Access, but learning quickly!

    I have a form 'frmjobplan' with a subform 'frmhours' which contains job
    number, date, hours and machine process. Basically the user will be on the
    frmjobplan on a certain job number, this job number is linked to the
    jobnumber in the subform with master and child.

    The problem im having is with an 'Actual Hours' box on the main form which i
    want to display a total of hours per job in the subform.

    I have added a txt box in the footer of the subform 'frmhours' with the
    formula ""=Sum([Hours])"" which when i click the frmhours on is own, it
    successfuly displays the sum of all the hours, but not by job.

    I have added this to the control source of the 'Actual Hours' box on the main
    form

    =frmhours.Form!sumofhours

    but when the form is opened it just displays "name?" in the Actual hours box.

    Can anybody help?

    Thanks in advance!!!

  2. #2
    Join Date
    Jul 2004
    Location
    South Dakota
    Posts
    267
    I think you are not referencing the field on the subform correctly. Try this link to see how to reference subforms correctly.

    Forms: Refer to Form and Subform properties and controls

    C

  3. #3
    Join Date
    Nov 2009
    Posts
    49
    Quote Originally Posted by hermondo View Post
    I have a form 'frmjobplan' with a subform 'frmhours' which contains job
    number, date, hours and machine process. Basically the user will be on the
    frmjobplan on a certain job number, this job number is linked to the
    jobnumber in the subform with master and child.

    The problem im having is with an 'Actual Hours' box on the main form which i
    want to display a total of hours per job in the subform.

    I have added a txt box in the footer of the subform 'frmhours' with the
    formula ""=Sum([Hours])"" which when i click the frmhours on is own, it
    successfuly displays the sum of all the hours, but not by job.

    I have added this to the control source of the 'Actual Hours' box on the main
    form

    =frmhours.Form!sumofhours

    but when the form is opened it just displays "name?" in the Actual hours box.
    You are kind of on the right lines, the easiest way to do this, is to make a query, once this query works, right click on the query and change the view to SQL view.

    Copy the SQL statement that is written there and paste it into the control source of the box.

    Are you ok in making the query?

    Phil

  4. #4
    Join Date
    Jan 2010
    Posts
    6
    Thanks for your help so far on this.

    The query i created basically just contains job number and hours, both from tblhours. in the total part (in design view) i have 'sum' in the hours collum.
    once run it lists the job numbers and total hours accumulated for that job number (which is what i want)

    The sql code for it is.

    SELECT [tblhours].[Job Number], Sum([tblhours].[Hours]) AS SumOfHours
    FROM tblhours
    GROUP BY [tblhours].[Job Number];

    however, when i paste this on into the control source of the text box on the form it is still coming back with 'name?'

    am i getting any closer?

    i may have it all setup incorrectly, but i basically just want to be able to input the date,hours and staff into the subform. The subform must be linked to the mainform which contains the rest of the job information (predicted jobs, engineer etc) Then a total hours to show also on the mainform.

    thanks again

  5. #5
    Join Date
    Nov 2009
    Posts
    49
    Ah I think I know,

    Yes you are closer, when you run the query is there more than one row of results, if so (which I think is happening) then this will give you the error.

    What is needed is the criteria part of the query to focus on one jobnumber, is there a textbox on either your main form or subform that contains the jobnumber value?

    In the criteria write:

    =[Forms]![Name of Form]![Name of cell with JobNumber in it]

    If this doesn't work then try:

    Like "*" & [Forms]![Name of Form]![Name of cell with JobNumber in it] & "*"

    If the jobnumber is on the subform:

    =[Forms]![Name of Form]![Name of Subform]![Name of cell with JobNumber in it]

    hope this works

  6. #6
    Join Date
    Jan 2010
    Posts
    6
    The main form displays the job number whereas the subform has the jobnumber linked to it, but it is not displayed (i dont want it displayed, jus want to be able to show the hours committed to that job)

    i am unsure where to write that...do you mean in the criteria box of the query? if so would it be in job number collum or hours (bearing inmind the query in question only contains job number and hours and basically displays a list of job numbers in one collum and the hours totalled in the other collum)

    as a note, the form is based on the table source rather than a query....

    Thanks again!

  7. #7
    Join Date
    Nov 2009
    Posts
    49
    Thats ok,

    The criteria written above should go in the criteria of the query underneath the SumofHours column.

    If the criteria wasn't there your results would probably come out something like:

    JobNumber Sum of hours
    123 23
    426 87
    346 34

    The problem is a textbox gets confused if you try and plug in multiple records into the same textbox, which one should it choose? So it says #Name?

    But as you are only wanting the sum of hours for a certain jobnumber, then by applying the criteria you would get only one record therefore the textbox will be able to display it.

    This textbox can be put whereever you want it, it doesn't have to be on the subform, and you can delete the JobNumber column as Access is clever enough to know that the jobnumber column you are using is coming from the same table of where the hours is found.

    I'll put a pic up in a second for the query.

    Click image for larger version. 

Name:	Query.jpg 
Views:	22 
Size:	18.6 KB 
ID:	10412
    Last edited by pb190; 01-15-10 at 05:38.

  8. #8
    Join Date
    Jan 2010
    Posts
    6
    Thanks....

    Have inputted as guided but the name? still remains. The code in criteria of the query is as follows

    [Forms]![frmjobplan]![job number] Or Like '*' & [Forms]![frmjobplan]![job number] & '*'

    when i now run the query, it doesnt display any results, just the collum headers job number and sumofhours.

    I have a small zipfile with the blank db in, would you mind if i perhaps emailed it to you? it might make more sense to you than my babbling!!!

    Cheers

  9. #9
    Join Date
    Nov 2009
    Posts
    49
    lol

    Sure, I don't normally do this, but it so much easier to do it yourself then explain afterwards as there are so many little things that can ruin it.

    I am afraid the reason why it probably isn't working is my fault, on my picture I put both the Like coding and the =Forms coding in, which will confuse it. Pick either set of coding. Also you will need to put the '=' in front of Forms. try this before sending the db to me. It could really be that simple.

    Phil

  10. #10
    Join Date
    Jan 2010
    Posts
    6
    hmm, unfortunately still the same.

    Now it could be because the 'actual hours' txt box on the form is referencing to the sumofhours footer on another form called 'frmhours' the process using the footer to sum details....

    i could have it all mixed up and it could be simple.if i send it to you and its too in depth or messy, dont worry about it. But it may be simple!

    where would you like me to send it?

    If you havnt got time, do let me know!, much appreciated

  11. #11
    Join Date
    Nov 2009
    Posts
    49
    Private Messaged you

  12. #12
    Join Date
    Jan 2010
    Posts
    6
    sorry, didnt recieve the 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
  •