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

    Best way to do significant, repetitive data entry

    Overview: I am building an IT financial management database. The database has a table/form for storing contract details, and a separate table/subform for storing the products associated with each contract. That portion of the database is working very well. The database also has allocation tables to store cost allocation data for each of the products in that contract against various things like the IT service catalog, business unit, etc. The allocation table includes a percentage field so that one can allocate, say, 30% of a product to Business Unit A, and 70% of the same product to Business Unit B. The allocation tables are many-to-many tables, joining IT service catalog items/business units/etc to products. Building the form for this data entry is where I need help.



    Details: The contract table has a PK called OrderID. Each product associated with that contract has the respective OrderID as FK, but also a OrderProductID as PK. Each allocation table (let's just use the Business Unit allocation table) has a PK of OrderBusinessUnitID, with FKs of OrderProductID and BusinessUnitID (drawn from the business unit tables) with a field to store the percentage of allocation. Currently, I have a form whereby the user enters the contract details, with the subform where they select the products on the contract and enter quantity/cost/accounting info. The subform is a continuous form to make the product data entry fast, and to make data carryover a little simpler. However, there's just not enough screen real estate to also have the user do the allocations at the same time as the product entry (which would be ideal).

    Courses of Action considered:
    1) Create an allocation button on the contract form that opens another form to do allocation for all the products associated with that contract. I like this idea the best, and if each product was 100% allocated to a business unit it would work perfectly. Just open the form with a list of all the products in a continuous form, then have combo boxes to select the allocations. But the form needs to allow for multiple allocations per product, and I don't know how to do that.
    2) Create an allocation button on the products subform for each product that opens another form to do the allocation. This is probably the most easily workable solution, but it means the user has to click a button after each product to enter the data for that product. There's going to be lots of carryover/repetitive data, and bouncing between forms product by product (some contracts have fifty or more products) is not ideal. This is my least favorite option.
    3) Add the allocation combo boxes to the existing product subform (continuous form). This might be ok, but 1) it consumes a lot of vertical screen real estate and 2) I've rarely had any success with a single form drawing on two tables (relationship or not). If I can work out the screen real estate piece, I'd probably be ok with this one.

    Left field thought: Is it possible to use a split form as a subform? It would have to open up in a separate popup window, but it would probably resolve the screen real estate issue and allow me to have all the data entered in one form (as long as I can draw on two tables...)

    So I'm throwing this out to the hive mind. Does anyone have any suggestions on the best way to handle this kind of thing? One caveat: I'm an IT executive. I am not experienced with VBA and don't really have the time to learn/maintain the skill set. I'm writing this database because 1) I need it to help defend my budget and 2) because my data analysts are too busy doing other things. I can follow instructions, and understand a little VBA, but I'm never going to write long subroutines...

  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,815
    Have you considered using Tab control to arrange controls and/or subforms onto multiple pages to consolidate real estate?

    No, a split form cannot be a subform. Try it.

    A form opened in a separate popup window is not a subform.

    If you don't use form/subform arrangement with master/child links and want to enter related records, will need code.
    Last edited by June7; 07-28-2020 at 05:18 PM.
    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
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Maybe you would post your dB for analysis?

  4. #4
    pncbiz is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Jul 2020
    Location
    Maryland
    Posts
    66
    June7,
    That was my initial thought. On the Contracts form, have a Products tab to enter all the products, then an Allocations tab to enter the allocations. But how would I allow the user to enter multiple allocations for one product? If I followed the data hierarchy, there would be a Contracts form, with a Products subform, and the Products subform would have an Allocations subform. That's actually the "real" solution...but it makes the screen incredibly messy and I don't have that much real estate. Can I have the Allocations subform (sub to Products) open in a separate tab? Or must it be embedded in the Products subform?

    If I have to have code, so be it...but I'll unfortunately have to lean heavily on smart folks like you to do it.

  5. #5
    pncbiz is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Jul 2020
    Location
    Maryland
    Posts
    66
    Steve,
    Unfortunately I can't upload the database because it is too big (even zipped). Would screenshots help?
    Thanks
    Phil

  6. #6
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    If I followed the data hierarchy, there would be a Contracts form, with a Products subform, and the Products subform would have an Allocations subform.
    you can have your contracts form with both products subform and allocation subform. You just need the following

    note - change names to match your actual names

    1. a hidden textbox control on your contracts form called say 'ProductPKLink'
    2. in the products subform current event put

    parent.productPKLink=ProductPK

    3. for the allocation subform set the linkmaster property to ProductPKLink and the linkchild property to ProductFK

  7. #7
    pncbiz is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Jul 2020
    Location
    Maryland
    Posts
    66
    Click image for larger version. 

Name:	Contracts form.jpg 
Views:	18 
Size:	139.7 KB 
ID:	42535

    Ajax,
    Thanks for that tip! I get how that would work, but it would sort of defeat the purpose of having the Products subform in continuous forms. I've attached a screenshot to show what I'm talking about. So Products are in continuous forms to make data entry easier. With the solution you proposed, the user would have to bounce between the tabs, selecting a new product every time, to then set allocations by product. It would work, but it would make data entry challenging.

    What I would like to do is have the allocation piece work sort of similarly...and quite literally I think I just thought of the solution. What if, in the allocations tab, I had a continuous form with a combo box where you select the product you want to allocate, then set the allocation? I could set the combo box to only pull the products associated with the current contract. Let me give that a shot.

    I really appreciate this forum. While the solutions offered may not be the ones I ultimately choose, they expand my mind to think about the issue and allow my brain to find the right answer. Will get back to you on how it goes!
    Thanks,
    Phil

  8. #8
    Join Date
    May 2018
    Location
    Living in Scotland UK
    Posts
    1,557
    Hi Phil

    If you want to PM me then you can email the database to me to take a look.
    You can PM me if you need further help.
    Good Reading https://docs.microsoft.com/en-gb/off...on-description

  9. #9
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows 10 Access 2016
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,530
    Quote Originally Posted by pncbiz View Post
    Steve,
    Unfortunately I can't upload the database because it is too big (even zipped). Would screenshots help?
    Thanks
    Phil
    What if you remove almost all data or remove all data and then create a few fictitious records before zipping
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

  10. #10
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows 10 Access 2016
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,530
    Also Compact and Repair before zipping
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

  11. #11
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Quote Originally Posted by pncbiz View Post
    Steve,
    Unfortunately I can't upload the database because it is too big (even zipped). Would screenshots help?
    Thanks
    Phil
    @ Phil,
    What Bob said......

  12. #12
    pncbiz is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Jul 2020
    Location
    Maryland
    Posts
    66

    Database attached

    Database is attached. Didn't delete any tables or queries, but I did blow out a bunch of maintenance forms, etc.

    frmITFM is the main form from which almost everything is done. It's a navigation form that holds default comboboxes that drive the data for all the subforms. I'm working on the Service allocations part under the Contracts tab. To recap: I want to be able to add many allocations to each product. There are many products for each contract.
    Attached Files Attached Files

  13. #13
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Products subform should link to OrderID field on Contracts form, not the textbox name.

    Advise not to use exact same field name in multiple tables.

    Idea for a combobox to select product would be tricky. Code would have to make sure record in OrderProduct table and grab that table ID to carry over to allocation record. Might need filtered combobox list. Only provide products associated with Order and if not yet there, use NotInList event to add record 'on the fly' during data entry.

    Maybe the allocation form(s) should be on tab control within subProduct form. Or allocation tab control sits next to Product from with form linking trick described by Ajax in post#6. Arrange controls more vertically.

    The more 'user friendly' the more code.
    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.

  14. #14
    Join Date
    May 2018
    Location
    Living in Scotland UK
    Posts
    1,557
    Hi

    I have added a Subform for Service Allocation

    The orange Unbound Control displaying under the Start Date Control is an example of how to link the Main Form to the Nested Subform.
    Attached Files Attached Files
    You can PM me if you need further help.
    Good Reading https://docs.microsoft.com/en-gb/off...on-description

  15. #15
    pncbiz is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Jul 2020
    Location
    Maryland
    Posts
    66
    I'm wary of wearing out my welcome here, and if you guys have better things to do - please don't hesitate to stop helping me.

    June7,
    I tried that exact idea (filtered combobox) but ran into all sorts of problems implementing it. RowSource worked just fine, but I couldn't use the same product more than once (i.e., could only make one allocation). Suspect some issues in my underlying queries, etc. I keep coming back to this fundamental issue: if the link between the two forms is the OrderProductID, then the allocation form will only address one product at a time. If I link to the OrderID (at the Contract level), I can do multiple products at one time, BUT I have to pull data from multiple sources (it seems) which causes Access to balk at editing the allocation table. I will admit that I am terrible at conceptualizing joins/relationships/queries, and the fix is probably dead simple. On the same fields name issue: I try to use a naming convention that makes it easier for me to track. Should I use PK and FK as a suffix to the names? I've seen that convention too.

    Mike,
    Thank you so much for taking the time to help (always amazing how rapidly you guys can observe/orient/decide/act)! I see what you did, and it may be the way I have to go eventually. My initial desire was to make the allocations form a list of ALL the products associated with that contract, not just the current record in the products form. That would make the data entry less "clicky", so you wouldn't have to bounce back and forth between the two forms.

    What do you experts think of this idea? At the top of the allocations form, have an unbound combo with rowsource of the products associated with the current contract (provides OrderProductID without needing to be in an underlyinq query/table). Underneath, have the allocation set up like Mike provided. It's no more "clicky" than selecting the product in a continuous form (still have to select the product) but you don't bounce between forms. The only challenge, off the top of my head without trying it, is getting the bound controls to move to either a new record when the OrderProductID changes in the unbound combo or to find the existing record and display it for editing. Maybe that's not an issue at all, but I often run across the challenge that the perfect form/logic for adding data is completely unusable for editing data.

    Again - please don't waste your time helping this inept CIO if you don't have it to waste...
    Thanks for all your help,
    Phil

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

Similar Threads

  1. Replies: 7
    Last Post: 03-05-2015, 07:34 AM
  2. Eliminating Repetitive Data in a query/report
    By Ranger7913 in forum Queries
    Replies: 3
    Last Post: 11-03-2014, 12:40 PM
  3. Replies: 19
    Last Post: 03-29-2014, 12:48 AM
  4. Quick repetitive data entry in form
    By scottfrock in forum Forms
    Replies: 1
    Last Post: 11-15-2013, 11:08 AM
  5. format number filed to significant digits
    By stileguru in forum Reports
    Replies: 3
    Last Post: 08-13-2010, 01:26 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