Results 1 to 7 of 7
  1. #1
    Join Date
    Oct 2009
    Posts
    15

    Unanswered: Mutiple Subforms in a tab

    I am asked to do the following tasks:
    create a tab includes 6 subforms. One subform will be the default and will always be there, the other 5 subforms take the same area and are invisible until users activate a button associates with each of them. Once a subform is activated, the subform will replace each other, making the previous-display subform invisible, by the desired one.

    I have create all subforms with the exact same format; and the buttons on the same tab, on area above the subforms, but dont know how to write the code to swap them since I am learning both Access and VBE at the time. I also thought of a common subform, then change the datasource, but also don't know how to make it correctly.
    Can somebody shed some light in either ways or both, and advice which over what to be better choice, please?

    Thanks
    Last edited by cspectra; 11-07-09 at 08:08.

  2. #2
    Join Date
    Oct 2009
    Posts
    340
    well it is an ugly requirement

    if all the subforms have all the exact same field names - - and layout - - then you could change the record source on the fly just put at the onclick event of the appropriate button: me.subformname.recordsource = "name of recordset"

    you might need to do a refresh/requery to change the screen data if that subform isnt' opening and closing

    if they are not all with exactly same field names then you have to overlay all the subforms and just make visibile tactically based on the button .....very messy to deal with in design mode....you will want to slightly stagger their overlay in order to highlight one vs the other. but in any case just set them all to visible-No as their default and then put a me.subformname.visible=true at the click of the button...

  3. #3
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    The preferred route to go would be to make 1 subform on the mainform and then simply manipulate the SourceObject of the subform (I do this quite often.) You just change the sourceobject to the appropriate subform's name. There are some occasions where you would want to manipulate the recordset of a form/subform but typically if you have 6 different subforms designed, each subform is based on a separate relational table (and usually ONLY the relational table) and serves the purpose of working with just that subform's dataset.

    (But you could for example, have 2 subform's (each based on the same relational table) and one of the subforms is an "adding new records" type subform and the other subform is a "view records only" type subform. I've done this but it is more work keeping both subform's maintained for data/design changes.)

    To change the source object of a subform, you again, do this by manipulating the SourceObject property of the subform on the main form. For example, you could create buttons on the main form and then utilize the button's OnClick event (ie. viewing the button's properties, select [Event Procedure] for the OnClick property and then click the 3 ... next to the dropdown) and then in the vba code...it might look like this for the buttons:

    Sub Procedure MyButtonForm1_OnClick()
    me.MySubformName.SourceObject = "MyFirstSubFormName"
    end sub

    Sub Procedure MyButtonForm2_OnClick()
    me.MySubformName.SourceObject = "My2ndSubFormName"
    end sub

    (also note: see my 2nd post if the above doesn't work and you're trying to manipulate the subform's sourceobject from another form versus the form the buttons are on.)

    I prefer to use buttons and the OnClick event but if you're using a tabControl, you want to utilize the OnChange event of the tab control to execute your code to manipulate the visible/invisible or SourceObject (or other properties.) Thus, using the OnChange event of the tab control, your code would look like this (I think - I may be wrong as I'm doing this from memory):

    Sub Procedure MyTabControlName_OnChange()
    Select case me!MyTabControlName
    case 1 'whichever tab control is numbered as 1
    me.MySubformName.SourceObject = "MyFirstSubFormName"
    case 2
    me.MySubformName.SourceObject = "My2ndSubFormName"
    case 3
    me.MySubformName.SourceObject = "My3rdSubFormName"
    case 4
    me.MySubformName.SourceObject = "My4thSubFormName"
    case 5
    me.MySubformName.SourceObject = "My5thSubFormName"
    case 6
    me.MySubformName.SourceObject = "My6thSubFormName"
    end select
    end sub

    If that doesn't work, see my 2nd post below to refer to the subform within a main form directly.

    I would not suggest using the tab control and subform within a tab control simply because it's easier to work with a subform directly within a main form versus a subform within a tab control on a main form but this depends on what you want to accomplish. Subforms are often used within tab controls so don't exclude this option.

    In regards to make 6 forms visible/invisible (but the above would be preferred.)....

    1. If using a tab control and you have subforms within the tab control (depending on how you have it setup)...

    Sub Procedure MyTabControlName_OnChange()
    Select case me!MyTabControlName
    case 1 'whichever tab control is numbered as 1
    me.MyFirstSubformName.visible = true
    me.My2ndSubFormName.visible = false
    me.My3rdSubFormName.visible = false
    me.My4thSubFormName.visible = false
    me.My5thSubFormName.visible = false
    me.My6thSubFormName.visible = false
    case 2
    me.MyFirstSubformName.visible = false
    me.My2ndSubFormName.visible = true
    me.My3rdSubFormName.visible = false
    me.My4thSubFormName.visible = false
    me.My5thSubFormName.visible = false
    me.My6thSubFormName.visible = false
    case 3
    me.MyFirstSubformName.visible = false
    me.My2ndSubFormName.visible = false
    me.My3rdSubFormName.visible = true
    me.My4thSubFormName.visible = false
    me.My5thSubFormName.visible = false
    me.My6thSubFormName.visible = false
    case 4
    me.MyFirstSubformName.visible = false
    me.My2ndSubFormName.visible = false
    me.My3rdSubFormName.visible = false
    me.My4thSubFormName.visible = true
    me.My5thSubFormName.visible = false
    me.My6thSubFormName.visible = false
    case 5
    me.MyFirstSubformName.visible = false
    me.My2ndSubFormName.visible = false
    me.My3rdSubFormName.visible = false
    me.My4thSubFormName.visible = false
    me.My5thSubFormName.visible = true
    me.My6thSubFormName.visible = false
    case 6
    me.MyFirstSubformName.visible = false
    me.My2ndSubFormName.visible = false
    me.My3rdSubFormName.visible = false
    me.My4thSubFormName.visible = false
    me.My5thSubFormName.visible = false
    me.My6thSubFormName.visible = true
    end select
    end sub

    Also make sure only 1 subform is visible on the main form at a time (ie. make 5 of the subform's Default Visible property = false). You'll get a faster opening time of the main form.

    If using buttons on the main form to manipulate a subform (within a tab control), I 'think' it's the same syntax, otherwise it becomes a little bit tricker and I can't recall the code offhand.

    You'll get a faster form loading time though using the method of manipulating the SourceObject of just 1 subform on a mainform versus having multiple subform's on a form even if they are invisible. You do have to come up with a standard subform size though that works for all the subforms.

    Also, make sure each of your subform's are ONLY based on the relational table (if you're updating/adding records in them). If you need to add new records in the subform, make sure your joining field is also ON the subform and set it's 'Default Value' property to =Forms!MyMainFormName!MyJoiningFieldName.
    Last edited by pkstormy; 11-07-09 at 20:37.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  4. #4
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Also note: If you need to add buttons ON the subform (or another form) to manipulate the SourceObject of a subform on a mainform, you can reference the subform DIRECTLY and you would use code as such in the button's OnClick event of each button (for the subform buttons):

    Sub Procedure MyButtonForm1_OnClick()
    Forms!MyMainFormName!MySubformName.SourceObject = "MyFirstSubFormName"
    end sub

    Sub Procedure MyButtonForm2_OnClick()
    Forms!MyMainFormName!MySubformName.SourceObject = "My2ndSubFormName"
    end sub

    You could also utilize the .parent to reference the subform on the main form but I prefer to just code in a reference directly to Forms!MyMainFormName!MySubFormName itself as this is usually the least problematic. You do have to be careful though if you start changing form names!

    Also Note: If you're using a tab control (ON the subform) and you're trying to manipulate the sourceobject of the subform on a mainform, you would again, reference the subform on the mainform as shown above using the OnChange event of the tab control.
    Last edited by pkstormy; 11-07-09 at 20:31.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  5. #5
    Join Date
    Oct 2009
    Posts
    15

    Smile

    Thanks a lot to all of you.
    It is quite a luck that I designe the 5 tables with the same format. There for I used one separately, the rest I applied buttons and change sources and enable/disable buttons as you suggested.
    I will reorganize later with "case" and another sub to avoid repetitive tasks.
    Anyway, I appreciate your prompt and detail response.

  6. #6
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    5 tables all designed with the same format (ie. fields) sounds like a very concerning design and that you should instead, combine all 5 tables and simply create a grouping type field in 1 table to distinguish the different data sets. But there may be some unusual (and rare) reason that you want it designed this way. Hopefully it's for a class project or something that isn't real world.
    Last edited by pkstormy; 11-09-09 at 20:37.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  7. #7
    Join Date
    Oct 2009
    Posts
    15
    Well, you are almost right. The database records 5 different systems connected together. Each of them is commplicate enough to be a separate database; but i have to make an inventory control that will keep track of all the changes.
    You are right for the part that the "tables" are not the same since the fields do different lookups. I made 5 queries to show the assets, and use controlSource to change the loading data, and they are doing well.
    The next common subform will be a little different, 5 subforms are necessary for the loading changes since on this common subform, users can change data using combo boxes (fields). I am partially success with it, but need a little more work. I'll ask for more advice if I hit the wall.
    Thanks again.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •