Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    pncbiz is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Jul 2020
    Location
    Maryland
    Posts
    66

    Using same subforms for two different main forms

    I have built a main form to manage contracts, with several subforms to capture details like the associated products and cost allocations. The form works great (well, there's a couple of small UI things I don't like but I've decided to live with them). Functionally, it does everything I need.



    Now I am building a separate main form to manage requisitions (one-time purchases vs contracts). The primary difference between the two (from the underlying field's perspective) is a checkbox; contract or not? However, from a UI standpoint, they are very different. The contract # on the contract form would be the requisition # on the requisition form. Some fields would not be displayed at all (like termination notice date) and some fields would be defaulted (contract box unchecked, intend to renew unchecked, etc.).

    I want to basically copy the contract form in its entirety, modify the main form and reuse the subforms as they are. Here's the rub: the subforms' underlying queries filter based on a control on the main form. How do I modify a query to reference a control on Me.Parent as opposed to [Forms]![MainForm]![ControlName]? Changing the query in SQL view was not successful, so I'm guessing I need to build some sort of VBA routine to either use the Me.Parent construct or to wholesale change the RecordSource of the subform based on the parent form it's currently displayed on.

    Any ideas?
    Thanks,
    Phil

  2. #2
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows 10 Access 2016
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,530
    Perhaps you could change the SQL used in the subform as it's Record Source when the main form opens.
    The criteria would be something like:

    [Forms]![SecondMainFormName]![ControlName]
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

  3. #3
    pncbiz is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Jul 2020
    Location
    Maryland
    Posts
    66
    Bob,
    That's kinda the road I figured I'd have to go down. If I do, I guess that implies that I can't have both forms (with subforms) open at once, because the criteria for the subform's recordsource query would be different. If that's the case, so be it - I'll have to build some sort of logic to prevent both forms from being open simultaneously.

    The easiest way for me to do this, I think, would be to build a second query with the second form's criteria, then switch the recordsource for the subform when the main form opens. Is that about right?
    Or should I use a variable for the criteria in a single query, and have the criteria change based on the main form? That may be more efficient, but more difficult for me because I'm a novice.
    Thanks,
    Phil

  4. #4
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows 10 Access 2016
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,530
    I think I would just make a copy of the subform with the required record source. Job done

  5. #5
    pncbiz is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Jul 2020
    Location
    Maryland
    Posts
    66
    Yeah....(hangs head because simple solutions are probably the best...)

    EDIT: UGH. I started to copy the subforms and all that...but here's the thing: the ONLY change required is the RecordSource. That's it, nothing else. If I replicate the entire structure, and at any point in the future I need to modify one of the subforms I'll have to do the mods to BOTH copies. Taking a shortcut now is going to create incalculable work in the future. I have to believe there's a better way to do this...

  6. #6
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    depends how your forms work, but a subfrom can reference the parent with

    me.parent.somecontrolname

    if the control is named differently on each main form, use code something like

    Code:
    select case parent.name
        case "Main1"
             ctrl=parent.control1
        case "Main2"
             ctrl=parent.controlxyz
    end select

  7. #7
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2016
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Phil,
    Can you show us your tables designs and relationships?

  8. #8
    pncbiz is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Jul 2020
    Location
    Maryland
    Posts
    66
    Orange,
    I attached a stripped down copy.
    frmContract is the one that works.
    Want to have the subs to work with both frmContract and frmRequisition. At this point, frmRequisition is a carboncopy of frmContract with just a few changes.

    PS. Noticed that the ServiceLineAllocation and OtherAllocation subs for some reason aren't saving the data entry to the underlying table field. ServiceCatalogAllocation does...which is strange because the three forms are essentially identical. If you can figure that one out while you're in there, that would be a great help, too. EDIT NEVER MIND - FIGURED THIS PART OUT. Underlying table had a field definition issue. Should all be Single vice Long Integer, and Percent as format. Still working on the above, though.

    I know there's a lot of amateur hour stuff in there - be gentle....
    Thanks,
    Phil
    Attached Files Attached Files
    Last edited by pncbiz; 08-13-2020 at 07:26 AM.

  9. #9
    pncbiz is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Jul 2020
    Location
    Maryland
    Posts
    66
    I spent a lot of time last night puzzling through how to do this efficiently, still coming up zero. Hopefully someone out there has an idea...

  10. #10
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2016
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Phil,

    Looks like you're getting things under control --especially if you are at the point of making it work a little more efficiently.

    Speaking for myself (others may understand) I'm not familiar enough with your business processes to offer anything more than general comments.
    If you have specific examples of what this is in "how to do this efficiently" with some sample of :
    -this is what I have and
    -this is what I need

    You might get more focused help/advice.

    As I mentioned in other thread, we often start with business description; build a model; refine the model with test data and scenarios --then build the database from the vetted model. Your posts -perhaps not your approach - have focused on forms.

    Good luck with your project.

  11. #11
    pncbiz is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Jul 2020
    Location
    Maryland
    Posts
    66
    Orange,
    I'm a CIO at a community hospital. We do not have a cost allocation model in place, but IT is seen as an over-resourced, under-serving black hole (like most organizations, I understand). We also do not have a real-time way of monitoring expenses vs budget. I'm building a database to assist with budget planning by tracking obligations (both contracts and one-time requisitions) and their associated invoices. It will also allow me to create cost allocations by IT service catalog, hospital service line, and by Gartner expense allocation categories (so I can do industry budget comparisons). While I envision this serving as an ITFM solution for my department, I'm hoping to make it high enough quality that other executives will adopt it as well. Unfortunately, buying and implementing cost allocation software seems a bridge too far at this point: other execs don't see the value, so I'm building this to show the power of cost allocation, and to demonstrate what COULD be achieved if we took that next step.

    While I have a long-standing personal computing history (Pet32s and tape drives...), I do not have a professional history of it and have no need/ability to maintain proficiency in VBA or Access. I know enough to be dangerous, as they say. I'm trying NOT to bring huge issues to this forum, because 1) I'm pretty far down this road and do not want to reengineer from scratch if I don't have to and 2) opinions are like coffee cups...even small issues often receive several different proposed solutions. The BIG idea will garner many more.

    I want to make the database as easily maintainable as possible because of my lack of proficiency and lack of time (as an executive). Wish I could just hire someone to write this for me, but that isn't going to happen. I've been resolving issues on my own (with a lot of great help from the folks on this forum). And frankly, I'm really close. Budgeting is done and working, contracts are working, allocation is working. Want to add requisitions (while capitalizing on the contracts), then I need to add invoice processing. Then it will be done.

    I appreciate the guidance, hope this helps scope what I'm trying to accomplish.

    In direct response to your question:
    1) What I have is attached in the post above (minus the parts I had to remove to make it fit under the size limit)
    2) What I need is to be able to use the same subforms currently used by the Contract form on the Requisition form without duplicating the entire structure.

    Thanks,
    Phil
    Last edited by pncbiz; 08-13-2020 at 10:58 AM. Reason: Add clarity

  12. #12
    pncbiz is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Jul 2020
    Location
    Maryland
    Posts
    66
    To be more specific about what I need/have:

    Here is a difference analysis between frmContract and frmRequisition (i.e., what needs to change to make the subforms work with a different mainform):
    1) frmContract - should work no problem without any changes
    2) subProduct - should work no problem without any changes
    3) subOrderProductList - RowSource for the list box is filtered by txtOrderID from frmContract. Need to figure out how to have it filtered by the same either frmContract or frmRequisition.
    4) subServiceCatalogAllocation - RecordSource for the form is filtered by OrderProductID from subOrderProductList. While this wouldn't change, how to refer to it ([Forms]![Contract].Form...etc.) would change because subOrderProductList would be sub to a different main. Same issue with the other two subs (so if I fix it for this one, I fixed it for them, too).

    That's ALL I need to change. There's a couple of things I have to modify because they reference the form absolutely instead of relatively, but that's easy. The crux of my issue is that it appears a query (all four Row/RecordSources above needing to change are query) cannot reference a form in a relative manner (Me.Parent. etc.). It must have the absolute reference.

    I guess it would be possible to have a generic query, then have VBA rewrite the query based on the form calling it. I'm researching that right now. Alternatively, I think I could write the query in VBA and use a variable to change the criteria based on the form calling the query. Anything else?

  13. #13
    pncbiz is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Jul 2020
    Location
    Maryland
    Posts
    66
    I figured it out. If anyone wants the gory detail, happy to supply, but on a broad scale I took a top-down approach as opposed to a bottom-up. Where a subform depended on information from another form, I had the other form PUSH the data, as opposed to having the subform PULL the data. If you PUSH to a subform, the subform can work on any main form...you just alter that main form to push down. It's a lot more work programmatically, and it breaks some things that usually happen automagically, but it works. Appreciate everyone's help.

  14. #14
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    just remember subforms open before the main form. Usually better to have the subform control sourceobject blank until mainform has loaded, then populated in the main form load or current event, depending on what you are doing
    Last edited by CJ_London; 08-14-2020 at 04:00 PM.

  15. #15
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    A subform isn't part of the Forms!collection either (unless you open it directly) which makes it more difficult to reference anything on it. Maybe that's why you weren't successful in "pulling" as the approach may not have dealt with that fact. You'd think if you could do one, you should be able to do the other.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

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

Similar Threads

  1. can't put 2 subforms on a main form
    By cjohnson in forum Forms
    Replies: 6
    Last Post: 04-28-2017, 01:19 PM
  2. Replies: 11
    Last Post: 05-13-2015, 11:10 PM
  3. Multiple subforms to one main form
    By mick3911 in forum Forms
    Replies: 8
    Last Post: 02-25-2013, 09:40 PM
  4. Replies: 5
    Last Post: 01-16-2013, 03:48 PM
  5. Replies: 0
    Last Post: 12-16-2008, 07:49 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