Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    AutumnBeds is offline Novice
    Windows 10 Access 2016
    Join Date
    Nov 2019
    Posts
    14

    Combo Box variable change subform

    Hi All,



    I need of some help. I'm creating a simple 'safety report database' for the workforce where I work.

    I've set up the tables etc, and a parent form with a combo box which allows the operators to select a 'safety event' (accidents, near miss, spills etc). Depending on their choice in the combo box I require a different subform (relevant to the event) to load on the main form which allows the operator to input the relevant data.

    Any help is appreciated.

  2. #2
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    use vba code to change the subform controlsource

    e.g. something like this in the combo after update event

    Code:
    dim fName as string
    SELECT Case cboEventType
       Case "Accident"
           fName="frmAccident"
        Case "Spills"
           fName="frmSpills"
       etc
    End Select
    
    me.subformname.controlsource=fName
    change names to suit your application

  3. #3
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Just wondering - is this a case where you have several forms that all look the same but have different data sources (tables or queries)? If so, you should be changing the subform recordsource, not developing several forms that basically look the same.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  4. #4
    AutumnBeds is offline Novice
    Windows 10 Access 2016
    Join Date
    Nov 2019
    Posts
    14
    I'm having some trouble with the "me.subformname.controlsource=fName". I've changed fname to subform as this is what I've titled the subform.

    Micron, the tables have different sets of data relating to different types of event. The data is specific to those events, hence why separate tables and forms to complete the data.

    Also, my access skills are novice at best, simple tables, simple relationships and simple forms are my knowledge field. Minimum VBA experience.

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

    Can you upload a zipped copy of the Db?

  6. #6
    AutumnBeds is offline Novice
    Windows 10 Access 2016
    Join Date
    Nov 2019
    Posts
    14
    SAFETY EVENT REPORTING.zipSee attached.

    Note however, tables and forms are yet to be constructed. I've just made test forms 'Form1' and 'Form2' to try the form change from the combo box.

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

    Is this what you are after?
    Attached Files Attached Files

  8. #8
    AutumnBeds is offline Novice
    Windows 10 Access 2016
    Join Date
    Nov 2019
    Posts
    14
    Hi Mike,

    Not exactly. What I was trying to do is the following, the operators when an incident occurs they can select the type of incident, accident, near miss etc. Based on this selection a subform would load on the main form for them to give sufficient detail of the event with various other pre-determined options in comboboxes.

    So, one main form which can display/change the subform depending on the type of event.

    I think I'll change it so that they can fill in a main form then select from a button which event happened and have pop up dialog box open

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

    Logic says that a Member of Staff reports an Event on a given date / time in a specific Location. This is the Main Form as shown.

    What would you then report about this specific Event?

    Whatever you need to report about the Event would be entered into a Subform. I have given you an example but you are saying this does not suit.

    Please explain a Typical Event that you would need to record so we can better understand your requirements.

  10. #10
    AutumnBeds is offline Novice
    Windows 10 Access 2016
    Join Date
    Nov 2019
    Posts
    14
    I'll try a different analargy. Say I had a master form with a combo box with the option of raising a new purchase order or creating an invoice, once selected, I would like to the subform embedded in the main form to change from purchase order form to invoice form.

  11. #11
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Not sure how many of these "issues" apply to your real db as you've indicated this is a mockup of sorts:

    - the control has no event associated with it (the link is broken)
    - don't use ControlSource property for the subform control object. You want SourceObject property
    - the combo doesn't hold text values; they are numeric because the bound field is 1. Even if you made it field 2, those values are still text but look like numbers. Why have "1" as text at all, especially if you're trying to find "Spills"?
    - perhaps most importantly, you have neither Option Compare or Option Explicit at the top of this module, so expect trouble in all your coding.
    - related to the previous comment, there is no variable for cboEventType. If I make it the same name as the event (EventType) then I can get somewhere, but the Case value has to be "1" the way you have it
    - typically, combos used for such things as this (or filtering) should not be bound (have a control source)

    When I take care of these issues, it works.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  12. #12
    AutumnBeds is offline Novice
    Windows 10 Access 2016
    Join Date
    Nov 2019
    Posts
    14
    - the control has no event associated with it (the link is broken)

    Can I assume this is the event associated with the combo box?

    - don't use ControlSource property for the subform control object. You want SourceObject property

    I think I get this

    - the combo doesn't hold text values; they are numeric because the bound field is 1. Even if you made it field 2, those values are still text but look like numbers. Why have "1" as text at all, especially if you're trying to find "Spills"?

    Yes, forgot about this, each item is a numerical value and I assign that. In the combo box, I show two, and set the sizing to 0;5 so it only show the second column, but assigns the first column, rightly or wrongly this what I was taught a number of years ago.

    - perhaps most importantly, you have neither Option Compare or Option Explicit at the top of this module, so expect trouble in all your coding.

    My vba skills are limited to docmd at best

    - related to the previous comment, there is no variable for cboEventType. If I make it the same name as the event (EventType) then I can get somewhere, but the Case value has to be "1" the way you have it

    As mentioned above, I understand this.

    - typically, combos used for such things as this (or filtering) should not be bound (have a control source)

    Think I understand this

    I've most likely bitten off more than I can chew at the moment with this project

  13. #13
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    I've most likely bitten off more than I can chew at the moment with this project
    This is how you learn so don't give up. If you want I can post the 'fixed' version of your db but have to go out now so that would have to be later tonight or tomorrow.

  14. #14
    accesstos's Avatar
    accesstos is offline Expert
    Windows XP Access 2007
    Join Date
    Dec 2018
    Location
    Greece
    Posts
    551
    Also, you can use a tab control on main form, with a page for each subform, setting the tab style to "None". Then, you can manage the visibility of the subforms via the combobox as follows:

    Code:
    Private Sub cboSafetyEvents_AfterUpdate()
             'Select the page of tab control via the combobox.
             Me.tabForms.Value = Nz(Me.cboSafetyEvents, 1) - 1
    End Sub
    Setting the Style of tabcontrol to "Tabs" eliminates the need of a combobox and VBA code.

    I think that this is a simple and robust way.

  15. #15
    AutumnBeds is offline Novice
    Windows 10 Access 2016
    Join Date
    Nov 2019
    Posts
    14
    Quote Originally Posted by accesstos View Post
    Also, you can use a tab control on main form, with a page for each subform, setting the tab style to "None". Then, you can manage the visibility of the subforms via the combobox as follows:

    Code:
    Private Sub cboSafetyEvents_AfterUpdate()
             'Select the page of tab control via the combobox.
             Me.tabForms.Value = Nz(Me.cboSafetyEvents, 1) - 1
    End Sub
    Setting the Style of tabcontrol to "Tabs" eliminates the need of a combobox and VBA code.

    I think that this is a simple and robust way.
    With some and error I imagined to get this set up to work. I think this will be suitable for what I need. It's only so the operators are given the correct form to complete with bring more windows up.

    Thanks,

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

Similar Threads

  1. Use combo box to change subform
    By JQuinnC in forum Access
    Replies: 4
    Last Post: 04-11-2016, 03:25 PM
  2. Replies: 3
    Last Post: 05-28-2013, 12:53 PM
  3. change subform completely based on combo box
    By benjammin in forum Forms
    Replies: 3
    Last Post: 03-13-2013, 11:16 AM
  4. Replies: 4
    Last Post: 09-04-2012, 07:41 PM
  5. Updating subform based on combo box change
    By kev921hs in forum Forms
    Replies: 3
    Last Post: 04-01-2010, 08:43 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