Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    Aug 2011
    Posts
    56

    Unanswered: Can a combo box selection on a form determine which table data is entered into?

    *Access 2007*
    I am looking to create a form that can fill in multiple tables. I think I have to use form/subform with primary/foreign keys. This is my layout:

    Table 1: EquipMaster
    Field1-WMI Equipment # (primarykey)
    Field2-Equip Type
    Field3- Manufacturer
    Field4- Model

    Table 2: VibroHammerSpec
    Field1-Vibro # (primary key)
    Field2-WMI Equipment # (foreign key)
    Field3-Serial #
    Field4-Eccentric Moment
    Field5-Frequency
    Field6-Length
    Field7-Width
    Field8-Height
    Field9-Vibratory Case Capacity


    Table 3: PowerPackSpec
    Field1-Powerpk# (primary key)
    Field2- WMI Equipment # (foreign key)
    Field3- Serial#
    Field4- EngineManufacturer
    Field5- EngineModel
    Field6- HydraulicOilCapacity
    Field7-HydraulicOilFilter

    These arent all the fields but enough to explain what I am having a problem with.

    I would like to create a form/subform that could enter data into both Table 1 and Table 2 or 3. Is that even possible?

    I am an access / database noob so it is sometimes hard for me to tell if something is possible, but I have some of the logic worked out in my head.

    Form contains:

    Field1-WMI Equipment # (primarykey)
    Field2-Equip Type
    Field3- Manufacturer
    Field4- Model

    Now based on the users combo box selection of Field2-Equip Type, a subform could be generated.

    Example

    User selects "Vibratory Hammer" in Field2-Equip Type on form. The subform that is generated for use contains fields from Table 2: VibroHammerSpec.

    or

    User selects "Power Pack" in Field2-Equip Type on form. The subform that is generated for use contains fields from Table 3: PowerPackSpec
    Last edited by sehenry; 09-13-11 at 21:25.

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    If you create one subform for each possibility, you can change the contents of the SubForm/SubReport control of the main form (i.e. the subform it displays) by modifying its SourceObject property.
    Code:
    Sub Combo_SubFormSelect.AfterUpdate()
    
        Me.ControlSubForm.SourceObject = Me.Combo_SubFormSelect.Value
    
    End Sub
    Have a nice day!

  3. #3
    Join Date
    Aug 2011
    Posts
    56
    Quote Originally Posted by Sinndho View Post
    If you create one subform for each possibility, you can change the contents of the SubForm/SubReport control of the main form (i.e. the subform it displays) by modifying its SourceObject property.
    Code:
    Sub Combo_SubFormSelect.AfterUpdate()
    
        Me.ControlSubForm.SourceObject = Me.Combo_SubFormSelect.Value
    
    End Sub
    Ok. Would this use the function of creating subform/subreport from existing form? Meaning I would make all my subform possibilities as forms first. Then on my data entry form, I would create a subform that chooses the correct one by using the code as its sourceobject.

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You do not create the subforms while the parent (Main form) is open. The subforms exist already (you create them while building the application) and you just change which subform appears in the parent when this one is open and the value of the ComboBox, which contains the list of all available subforms, changes. You can then change other properties of the loaded subform, for synchronizing it with its parent, for instance.
    Have a nice day!

  5. #5
    Join Date
    Aug 2011
    Posts
    56

    Cascading ComboBox

    I didn't want to start a new thread but I can not get this cascading combobox to work.

    Heres what I have:

    Table 1: EquipMaster
    Field1- WMI Equipment # (primarykey)
    Field2- EqType
    Field3- Manufacturer
    Field4- Model

    What kind of table do I have to make in order to be able to select 'EqType' from a combo box and have that cascade into limited selection in 'Manufacturer'?

    I tried making a table 'TypeManufacturer' that consisted of possible 'EqType' and 'Manufacturer' combinations. Then I set the source in Field 3, Table 1 to give me [TypeManufacturer].[Manufacturer] selections based on [EquipMaster].[EqType] = [TypeManufacturer].[EqType]

    Does someone have an example?

    Should I be doing this work in a form or in a table?
    Last edited by sehenry; 09-13-11 at 18:01.

  6. #6
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    The normal way of doing this uses 3 tables:

    1 table with all possible EqTypes.
    1 table with all possible Manufacturers.
    1 junction table that links every Manufacturer to its related Eqtypes or the contrary or both (it depends on the context and the specificities of the data involved). This table only contains 2 foreign key in each row, each key pointing to the primary key of each table (EqTypes and Manufacturers). This creates a many-to-many relationship between both tables.

    You can perhaps spare 1 or 2 tables and just work with one table for EqType or for Manufacturers, storing the foreign key directly in the table EquipMaster. Personally I would not do it, but I know nothing about the business plan of your application, so it's hard to tell for sure.
    Have a nice day!

  7. #7
    Join Date
    Aug 2011
    Posts
    56
    Quote Originally Posted by Sinndho View Post
    The normal way of doing this uses 3 tables:

    1 table with all possible EqTypes.
    1 table with all possible Manufacturers.
    1 junction table that links every Manufacturer to its related Eqtypes or the contrary or both (it depends on the context and the specificities of the data involved). This table only contains 2 foreign key in each row, each key pointing to the primary key of each table (EqTypes and Manufacturers). This creates a many-to-many relationship between both tables.

    You can perhaps spare 1 or 2 tables and just work with one table for EqType or for Manufacturers, storing the foreign key directly in the table EquipMaster. Personally I would not do it, but I know nothing about the business plan of your application, so it's hard to tell for sure.
    Ok thanks. If 3 tables makes it the easiest, I will do that. My goal is to create something that isn't to complicated. I think if I get down the organization and programming format for cascading combo boxes and subform generation based on type of equipment I should be able to move along. The problem I have is understanding some of the code, syntax, and where to actually put the code.

  8. #8
    Join Date
    Aug 2011
    Posts
    56
    Well, I finally figured out how to do cascading combo boxes (two different ways). Now onto the subforms.

  9. #9
    Join Date
    Aug 2011
    Posts
    56
    In access help it says subforms are for tables/queries with one-to-many relationship, but is it also effective for one-to-one relattionship?

  10. #10
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    There can be any type of relationships between a Parent form and a subform, though some implementations are more useful than others, and some are useless.

    If there exists a one-to-one relationship between to tables, you can create a query gathering the data on a one-row for one-row base and display the resulting data in a single form.

    The only justification I can imagine for this type of form/subform implementation in a typical application is that you would give the possibility to show or mask part of the data. There are other situations where you could use this implementation but they are rather uncommon.
    Have a nice day!

  11. #11
    Join Date
    Aug 2011
    Posts
    56
    Quote Originally Posted by Sinndho View Post
    If you create one subform for each possibility, you can change the contents of the SubForm/SubReport control of the main form (i.e. the subform it displays) by modifying its SourceObject property.
    Code:
    Sub Combo_SubFormSelect.AfterUpdate()
    
        Me.ControlSubForm.SourceObject = Me.Combo_SubFormSelect.Value
    
    End Sub
    Pardon my lack of knowledge with code, but I having trouble figuring out how to link a subform to a selection in a combo box.

    The combobox i am using is cboxEqType which is sourced from selecting distinct values from another table. How do I link a value selected in that cboxEqType to a subform I have created?

    I have attached a screenshot of what I am working with so far.
    Attached Thumbnails Attached Thumbnails code.jpg  

  12. #12
    Join Date
    Aug 2011
    Posts
    56
    Wow didn't realize if I name the subform the same as the selections in the combo box it would work. Thanks for your help Shinndho.

  13. #13
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Glad you figured it out. You're welcome!
    Have a nice day!

  14. #14
    Join Date
    Aug 2011
    Posts
    56
    Semi-related question:
    If I wanted to have the subform area on the main for go "blank" (until the combo box selection is made) after starting a new record, could I do that using an event? Right now when I hit new record button, it goes to a new record but the subform stays the same until I select a different equipment type.

  15. #15
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Very easily:
    Code:
    Me.ControlSubForm.SourceObject = ""
    Have a nice day!

Posting Permissions

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