Results 1 to 13 of 13
  1. #1
    cap.zadi is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    May 2011
    Location
    KSA
    Posts
    481

    Cascading to Continuous Subform

    Hi

    I have TblSpareparts with fields SPID, Desc., EquID (The Equipment to this spare part is assigned), etc

    I have created two tables to record the history of changing spare parts as "TblSPChangeHead" have fields "ChangeID", "Dated" and EquID" etc and respresnts "FrmSPChange" whereas TblSpChangeDetails" SChangeID, ChangeID (FK), SPID(FK), etc and represents subform "SfrmSPChange"

    Requirement:

    If select a equipment from combo box (CboEqu) then at subform the list of assigned spare parts should be only shown in combo box "CboSpare" than whole list.


    Regards

  2. #2
    cap.zadi is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    May 2011
    Location
    KSA
    Posts
    481
    Click image for larger version. 

Name:	Access Form - a.jpg 
Views:	33 
Size:	97.5 KB 
ID:	35802

    The above is the view of the form and selection of spares from cbobox which should be cascade according to the Equipment.

    Note: The spare parts are already assigned to related equipment in master table.

  3. #3
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Since the choices don't vary per record, it seems like a basic cascading combo:

    http://www.baldyweb.com/CascadingCombo.htm
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  4. #4
    cap.zadi is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    May 2011
    Location
    KSA
    Posts
    481
    Hi

    Thanks for the feedback. Its working on the main form combo boxes but not at depended combo box which is at subform.

    As you can see the CboEqu as at FrmSPChange whereas CboSpare is at subform "SFrmSPChange".


    Kindly advise in this scenario.



  5. #5
    Join Date
    Apr 2017
    Posts
    1,673
    So subform will display a list of used spare parts for equipment active in main form at certain date (the date field in main form is probably unbound text box).

    You have said nothing neither about tables both forms are based on, nor about linking of forms, so I can only guess about this.

    Let's assume you have a single form fEquipment0, based on table tblEquipment: EquipmentID, EquipmentName, ... . The form is read-only and us used only to select an equipment. It has also an unbound text box txtMaintenanceDate;

    You also have a continuous subform sfEquipmentMaintenence in fEquipment0. The source of subform is form fEquipmentMaintenence, based on table tblEquipmentMaintenence: EMID, EquipmentID, MaintenanceDate, PartID, PartQty, Reason, .... LinkMasterFields property of subform will be "EquipmentID; txtMaintenenceDate", LinkChildFields property of subform will be "EquipmentID; MaintenenceDate" (the list delimiter may be comma instead of semicolon depending on your language settings). Such design quarantees, that any new record added into subform gets EquipmentID and MaintenenceDate assigned automatically - it is for best for you to set according controls invisible in form fEquipmentMaintenence.

    You have to write a Current event for form fMaintenence0, which changes the RowSource property of combo linked to PartID in tblEquipmentMaintenance depending of value of EquipmentID in main form (fEquipment0).

  6. #6
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    You say it's not working; what's the code behind the main form combo and the row source of the subform combo?
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  7. #7
    cap.zadi is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    May 2011
    Location
    KSA
    Posts
    481
    Hi

    Sorry for late reply.

    There is a ItemMaster Table in which already Spar Part is assigned to a Equipment Id at the time of entry.
    ItemID (PK)
    ItemCode
    ItemDesc
    EquID (FK) Equipment ID from Table Equipment.
    Etc. Fields

    So the requirement; When we select the equipment from CboEqu from main form FrmSPChange whereas CboSpare is at subform "SFrmSPChange" which should shows only the list of spares assigned to that equipment.

    Note: The each spare part is already assigned to equipment at the time of entry in ItemMaster.

    I hope, i am able to explain now.


    regards

  8. #8
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Since you're not posting any code, I'm guessing you haven't actually tried any of the suggested solutions? Can you attach the db here?
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  9. #9
    cap.zadi is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    May 2011
    Location
    KSA
    Posts
    481
    Hi

    Ok here is DB.

    Thanks
    Attached Files Attached Files

  10. #10
    Join Date
    Apr 2017
    Posts
    1,673
    On form FrmSPChange, you have a subform SFrmSPChange with form SFrmSPChange as source. It may get somewhat confusing in future!

    Subform and the form it is based on are different objects and belong to different object classes. One is container, another form.

  11. #11
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Exactly as described in my link, in the after update event of the combo on the main form:

    Code:
        Dim strSQL                As String
    
        strSQL = "SELECT ItemID, Description, InstalledQty, EquID, Status " _
               & "FROM TblItemMaster " _
               & "WHERE EquID = " & Me.CboEquipment
    
    
        Me.SFrmSPChange.Form.CboSParts.RowSource = strSQL
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  12. #12
    cap.zadi is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    May 2011
    Location
    KSA
    Posts
    481
    Hi

    Thanks for efforts and its working fantastic.

    Appreciated.

  13. #13
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Happy to help!
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

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

Similar Threads

  1. Replies: 1
    Last Post: 06-06-2018, 03:54 PM
  2. Replies: 2
    Last Post: 09-10-2013, 09:10 AM
  3. Cascading Combo box in Continuous Form
    By neo651 in forum Access
    Replies: 1
    Last Post: 09-15-2011, 02:34 AM
  4. cascading combo boxes in continuous subform
    By ayamali in forum Programming
    Replies: 1
    Last Post: 03-29-2011, 06:33 PM
  5. cascading combo boxes on Continuous Forms
    By Jerry8989 in forum Forms
    Replies: 0
    Last Post: 10-12-2009, 10:02 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