Results 1 to 7 of 7
  1. #1
    Tlo is offline Advanced Beginner
    Windows 8 Access 2013
    Join Date
    Jan 2016
    Posts
    41

    Add Subform to Form Using Button/VBAq

    I have a very specific form that I'm trying to build and am having difficulty with it.
    Table Design:
    -tblEstimates contains the estimate number (primary key), customer (there is a proper relationship established and a seperate Customer table), and date estimated.
    -tblServices ID (primary key), contains estimate number (foreign key to tblEstimates), service (coming from a seperate lookup table containing all the services)
    -tblItems ID (primary key), contains service number (foreign key to tblServices), item, quantity, item cost

    I'm building an estimate form, and there are three hierarchy levels to the form: estimate number, service, and item. Each estimate can have many services, and each service can have many items. I have created a form containing a subform, which contains a subform. The form is the estimate level, the subform is service level, and the subform inserted in the subform is the item level. The items subform is a continuous form. So basically with this current form I can have one estimate, with one service, with 1-many items. What I need is a form that can have one estimate, 1-many servivces, and 1-many items. (Each estimate can have 1 service or many services).

    I've been working on this for a week and have been able accomplish this if the subform (with items subform inserted) is in datasheet view, but I don't like that as this form will be used by other users, and it isn't intuitive and smooth to navigate in my opinion. I've also been able to insert a continuous form for items into the footer section for a continuous form for services, but this too is not intuitive as the items for service "x" are not listed directly below service "x", but rather in the footer of the form.



    What I'm trying to accomplish is to build take the form I currently have, and insert a button that adds another subform to the main form. This way there is no unnecessary clutter in the form (I don't want to create a form with 20 subforms if only one subform is required at times). It would also be intuitive, as the user can select a service, then select multiple items directly below it, and then repeat the process by clicking the add service button if needed.

    Does anyone know if this is possible through VBA? If so, can you point me in the right direction?

    Thanks in advance.

  2. #2
    John_G is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2011
    Location
    Ottawa, ON (area)
    Posts
    2,615
    Why would you want to add (e.g.) 20 subforms when they presumably are all containing the same data? I don't see why you can't just display each form in Single Form view. If you have defined the links between the levels properly, the subforms should display the correct data as you move from one record to another. Adding a new service record will (or can) automatically put the Items subform into Data entry mode.

    Am I missing something?

  3. #3
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    insert a button that adds another subform to the main form.
    ...
    ...Does anyone know if this is possible through VBA?
    Simply, it can't be done. You can only add subforms (or any control) in design view.

    Sounds like you are trying to display a form like you can a report - which is not really possible.

    What I have done in the past is to have mainform as single view with two subforms side by side with some conditional formatting to indicate which record in one of the subforms has been selected.

    In your case - mainform - estimates, subform1 - services with conditional formatting to indicate selected record and subform2 for items.

    to make subform2 dependant on subform1, have a hidden textbox on the mainform called say ctlLink and in your subform1 current event populate it with the ID of the selected record and set the linkMaster properties of subform2 to this control. You can also use ctlLink to set your conditional formatting in subform1

    if you can determine which row in subform1 has been selected and know the height of the detail section, you can move the top of subform2 to line up.

  4. #4
    Tlo is offline Advanced Beginner
    Windows 8 Access 2013
    Join Date
    Jan 2016
    Posts
    41
    Thanks for the replies.

    John G - imagine a HVAC company comes out to your house and gives you a quote. The estimator will have one estimate number, then say AC maintenance as a service, then replace filter as an item. Then he may quote Equipment installation as a service, and labor and parts as an item to install a new furnace. That flow of Est Number -> Service -> Item and how it reads is important. So it actually isn't the same data, it's the same estimate, but not the same data. It wouldn't make sense if he gave you two different sheets of paper, one that had both estimate numbers on it, and one that had the services and items.

    Ajax - thanks for the reply. I found the createcontrol function last night and that got my hopes up, but apparently that can only be done in design view, which doesn't make sense to me...if you're in design view then why not create the control normally. Anyway, I think what I'm going to have to do is to build a form with 20-30 subforms, and in the form on load event set subforms 2-30 invisible and their height to 0.. Then add a button in the main form next to each subform, and add event on click to set the next subform visible equal to true, and set proper height. I'll have to figure out how to size the subforms properly (some subforms may have 20 items, and some may have one), but if I have questions on that I'll post a new thread. This will provide the same view that our estimator is used to using in our current software.

    Thanks again for the replies.

  5. #5
    John_G is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2011
    Location
    Ottawa, ON (area)
    Posts
    2,615
    By "same data", I meant same type of data, i.e. multiple records of similar data. You have a three-level hierarchy - Estimate - Service - Item; no problem there.

    Your form will have Estimate as the main form. Then, since one estimate can have many Services, you create a subform for the services. But you don't need a separate subform for each service in an estimate - you can enter multiple records into the subform, and they will all be linked to the same estimate (again, provided you set up the Link Master Files and Link Child Fields correctly). The same concept applies to the Services with multiple items.

    If you want to see all the records (or many of them, anyway) at the same time, you could have the subforms as continuous forms, one line per record, and if the there is more data than will fit on one line without scrolling, you could put a button on each line which would display a popup form with all the details for that record.

    But I think trying to manipulate many copies of the same subform, and trying to keep track of which record each one is displaying will be a "challenge".

  6. #6
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716

  7. #7
    Tlo is offline Advanced Beginner
    Windows 8 Access 2013
    Join Date
    Jan 2016
    Posts
    41
    This is the relationship window. I'm trying to build this form in a test database, not the one it will actually go in. Until I figure out how to do it I'm not going to attempt in the real database.
    Click image for larger version. 

Name:	Relationships.jpg 
Views:	12 
Size:	31.5 KB 
ID:	25536

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

Similar Threads

  1. Replies: 7
    Last Post: 11-03-2015, 02:43 PM
  2. Form-Subform-Button-RecordSelect
    By TechKEYS in forum Access
    Replies: 7
    Last Post: 01-14-2015, 10:08 PM
  3. Replies: 5
    Last Post: 12-19-2012, 01:05 PM
  4. Call a button on the main form from the subform
    By Grooz13 in forum Programming
    Replies: 1
    Last Post: 09-28-2011, 01:15 PM
  5. Replies: 0
    Last Post: 07-26-2010, 04:36 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