Results 1 to 10 of 10
  1. #1
    BigJohn89 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Dec 2017
    Posts
    37

    Question Dlookup from subform

    Hi!

    I cannot find a solution to my problem:

    I have a continuous subform named "Sub1". This subform is a result of a query. There are two main values in query "function" and "sumofquantity".
    I have created another form with this subform. A also have few textboxes "text1", "text2", "text3". I need to make Dlookup function:

    Subform:

    function sumofquantity
    "cutting" 100
    "preparing" 200
    "packing" 50

    Form:
    permanent textboxes:
    "cutting"
    "preparing"
    "packing"

    blank texboxes: (dlookup quantity from subform)
    "text1"


    "text2"
    "text3"


    I need to dlookup values from subform to each blank texboxes in form. I will set this function to OnLoad event of the form.


    Many thanks for help!

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Domain aggregate functions pull data from table or query, not form.

    An expression in textbox can reference control names.

    Why do you need to pull subform data up to main form? What you are describing is not making sense to me.

    If you want to provide db for analysis, follow instructions at bottom of my post.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    BigJohn89 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Dec 2017
    Posts
    37
    Is there another way to make mathematical operation (in this case - multiplication) with values from this Subform and values from the form?

    For e.g. If worker have done 100 cutting per day and I know that for 100% performence per day he needed to cut 200 - his performence is only 50%. Worker need be able to count that.

    I thought that was a faster solution (not necessarily the best one )

  4. #4
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    It is often beneficial to identify your "business facts" then create a draft data model based on the facts. You can do this with pencil and paper. Then using some sample data and test scenarios, ensure you can take your test inputs and get the proposed outputs. Adjust as necessary, retry, repeat until your model does what you need. Then build the physical database from that model/blueprint.
    As for the mathematics involved, I would consider expected performance and observed performance in your design. Then
    (observed performance/expected performance ) *100 will give you percentage.
    The key is to get all the business facts and build/design/vet the model on paper. Trying to do detailed analysis while working with physical database is a long, frustrating process. Many do the long thing because they think Access is going to do some sort of magic for them. Reality is Access won't do anything you don't tell it.
    Good luck with your project.

  5. #5
    BigJohn89 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Dec 2017
    Posts
    37
    Ok but is there a possibility to get those values from subform to main form?

  6. #6
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Post a copy of your database or provide a graphic of your tables and relationships showing all fields in all tables.
    Forms are like a window into your tables, so form and subform relate to tables and tables are where data is stored.

  7. #7
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Yes, textbox expression on main form can reference fields/controls on subform.

    Something like:

    =[subformcontainername]![fieldname] / [controlname]

    or

    =[subformcontainername]![controlname] / [controlname]

    If you need more help, provide db for analysis
    Last edited by June7; 10-22-2019 at 03:25 AM.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  8. #8
    BigJohn89 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Dec 2017
    Posts
    37
    Hi,

    I attached my database. Thank you!
    Attached Files Attached Files

  9. #9
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    How this should work:

    1. textboxes in subform footer named like tbxMontage with expressions like:
    =Sum(IIf([function]="Montage",[SumaOfQuantity],Null))

    2. textboxes on main form reference those textboxes:
    =[frmSub]![tbxMontage]

    This works in my db but not on your frmMain. Shows #Name? error and I have no idea why. I tried building new forms in your db and still didn't work. So I imported your table into my db and built forms. The calculations work. Then I imported my forms into your db. They still work.

    Here is the db.



    Attached Files Attached Files
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  10. #10
    BigJohn89 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Dec 2017
    Posts
    37
    Thank you!! It works like a charm

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Using Dlookup in Subform
    By dnjtdjq in forum Forms
    Replies: 6
    Last Post: 03-23-2017, 05:20 PM
  2. Trouble with DLookup on Subform
    By GregShah in forum Forms
    Replies: 13
    Last Post: 12-14-2016, 12:41 AM
  3. Trouble with DLOOKUP in a Subform
    By Mubashir sabir in forum Forms
    Replies: 2
    Last Post: 08-21-2015, 01:38 PM
  4. Dlookup not working in Subform
    By keiath in forum Forms
    Replies: 1
    Last Post: 04-23-2014, 09:03 AM
  5. Subform Dlookup
    By jbedward in forum Forms
    Replies: 7
    Last Post: 01-13-2010, 08:06 AM

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