Page 1 of 2 12 LastLast
Results 1 to 15 of 23
  1. #1
    10 Gauge's Avatar
    10 Gauge is offline Getting the hang of it...
    Windows XP Access 2007
    Join Date
    Feb 2011
    Location
    Fredericksburg, VA
    Posts
    202

    Question Calculations based off of multiple subforms on the same master form?

    Is this possible? I have a series of tabbed subforms on my master form and I would like to make some calculations between information from these different subforms and display it on the master form.



    An example would be: IF the specified field from the newest record on Subform A minus the specified field from the newest record on Subform B is greater than or equal to 250, THEN display "PM Service Due".

    TIA.

  2. #2
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    Yes it is certainly possible. Maybe this link will be helpful: http://www.mvps.org/access/forms/frm0031.htm

  3. #3
    10 Gauge's Avatar
    10 Gauge is offline Getting the hang of it...
    Windows XP Access 2007
    Join Date
    Feb 2011
    Location
    Fredericksburg, VA
    Posts
    202
    Quote Originally Posted by RuralGuy View Post
    Yes it is certainly possible. Maybe this link will be helpful: http://www.mvps.org/access/forms/frm0031.htm
    Thanks RG. Could you possibly point me towards sample code? I'm not exactly sure how the references in you link are to be used.

  4. #4
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    I tell you what. You try one and report back on what you tried and what happened and then we'll help you get it to work.

  5. #5
    10 Gauge's Avatar
    10 Gauge is offline Getting the hang of it...
    Windows XP Access 2007
    Join Date
    Feb 2011
    Location
    Fredericksburg, VA
    Posts
    202
    Quote Originally Posted by RuralGuy View Post
    I tell you what. You try one and report back on what you tried and what happened and then we'll help you get it to work.
    I've determined there's no possible way to have Access do what I want, and if by some divine miracle there is, it's so advanced I probably wouldn't even be able to follow a step by step tutorial on how to make it work, haha.

  6. #6
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    Okay, start by creating a TextBox on the MainForm with a ControlSource that brings in one of the values from one of the SubForms. It will have a ControlSource something like: =SubForm1ControlName.FORM.ControlName
    ...using your names of course.

  7. #7
    10 Gauge's Avatar
    10 Gauge is offline Getting the hang of it...
    Windows XP Access 2007
    Join Date
    Feb 2011
    Location
    Fredericksburg, VA
    Posts
    202
    Quote Originally Posted by RuralGuy View Post
    Okay, start by creating a TextBox on the MainForm with a ControlSource that brings in one of the values from one of the SubForms. It will have a ControlSource something like: =SubForm1ControlName.FORM.ControlName
    ...using your names of course.
    I actually started to do that, and then I laid it out on a piece of paper just to realize I have to have it also reference a query on the form that is dependent on the equipment type displayed which I am not sure is even possible.

    So the equation is more like this....

    Subform 1's newest record (last pm mileage) + query result from list box (which is the interval) - Subform 2's newest record (last recorded mileage)

    Is this possible?

  8. #8
    10 Gauge's Avatar
    10 Gauge is offline Getting the hang of it...
    Windows XP Access 2007
    Join Date
    Feb 2011
    Location
    Fredericksburg, VA
    Posts
    202
    At first I was going to use a set interval and then when I actually wrote it down that's when I realized that since we log equipment in both miles and hours a set interval won't work for all of the equipment that we have. I could possibly add a field to the equipment type table that shows the basic interval. Or I could add the 2 basic intervals to the units of measure table, one for miles and one for hours, and have it reference that could I not?

  9. #9
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    I believe a Domain function in the Current event of the MainForm might be helpful here. DMax()

  10. #10
    10 Gauge's Avatar
    10 Gauge is offline Getting the hang of it...
    Windows XP Access 2007
    Join Date
    Feb 2011
    Location
    Fredericksburg, VA
    Posts
    202
    Yes that is definitely what I need, I just did some research on it. However in the examples I'm looking at for the function it's specifying criteria in the function itself, is there a way to make the criteria dynamic as per which record is currently selected?

    Edit: Would the DLast function also work?

  11. #11
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    DLast() does not do what you think it does. DMax() is better suited to what you are doing.

  12. #12
    10 Gauge's Avatar
    10 Gauge is offline Getting the hang of it...
    Windows XP Access 2007
    Join Date
    Feb 2011
    Location
    Fredericksburg, VA
    Posts
    202
    Holy crap I got it on the first try, and it works. HOOORAH! Thanks RG, you are the man!!

    Here's what I used to get it to work:

    =DMax("PMEventUsage","PMEvents","AssetID")+DLookUp ("PMInterval","AssetCategories","AssetCatID")-DMax("Usage","AssetUsage","AssetID")

  13. #13
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    Hold the phone a bit. Your WhereCondition argument makes no sense. http://www.mvps.org/access/general/gen0018.htm

  14. #14
    10 Gauge's Avatar
    10 Gauge is offline Getting the hang of it...
    Windows XP Access 2007
    Join Date
    Feb 2011
    Location
    Fredericksburg, VA
    Posts
    202
    You are right, it only worked on my test record, it didn't work on the others.

    I appended it to this as per the link you sent me and NOW it seems to work correctly across all records.

    =DMax("PMEventUsage","PMEvents","AssetID = " & [Forms]![Asset Details]![AssetID])+DLookUp("PMInterval","AssetCategories","AssetCat ID = " & [Forms]![Asset Details]![AssetCatID])-DMax("Usage","AssetUsage","AssetID = " & [Forms]![Asset Details]![AssetID])

    Still, thank you RG.

  15. #15
    10 Gauge's Avatar
    10 Gauge is offline Getting the hang of it...
    Windows XP Access 2007
    Join Date
    Feb 2011
    Location
    Fredericksburg, VA
    Posts
    202
    One last quick question for you....

    Is there a way to get this text box to auto-update after an entry to the Usage or PMEvents has been entered?

    I currently have the box set with an OnClick "Me.Text123.Requery" so that it updates when you click it. Would love to automate it more than that however.

    Edit: Got that too with the first link you sent me.... Did an AfterUpdate on the subform with Me.Parent!Text123.Requery and it seems to be working beautifully.

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

Similar Threads

  1. Master/Child between Subforms
    By Pilotwings_64 in forum Forms
    Replies: 3
    Last Post: 08-22-2010, 01:45 AM
  2. position multiple subforms
    By Mclaren in forum Programming
    Replies: 1
    Last Post: 07-12-2010, 08:52 AM
  3. Replies: 1
    Last Post: 02-03-2010, 08:17 AM
  4. Replies: 0
    Last Post: 12-16-2009, 01:14 PM
  5. Display and Empower Multiple Subforms
    By Schwagr in forum Forms
    Replies: 2
    Last Post: 03-28-2006, 03:51 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