Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    3 Star Lounger
    Join Date
    Jun 2003
    Posts
    238
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Master/Child Link (Access 2000)

    I want to link drop downs in a form to a table in a subform. I know I can do this through master child links and if I want to link more than one I can put a semi-colon in between. My problem is that I want to be able to search with multiple drop downs but not all of them if i dont want too. For example, If employee ID and order # and Year are dropdowns, I want to be able to search by all 3 sometimes, but other times I will just want to search by employee ID and order # and leave the year blank so it would search all years, or even sometimes just by order # so it would search all employees and all years. I would like to make this possible on one form?? Help?! Thanks in advance to any help.

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Master/Child Link (Access 2000)

    One way to do what you describe is to manipulate the master/child links in VBA. Are you comfortable working with VBA and event driven procedures?
    Wendell

  3. #3
    3 Star Lounger
    Join Date
    Jun 2003
    Posts
    238
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Master/Child Link (Access 2000)

    yes I am , but I do not know enough about it myself to figure out the coding.

  4. #4
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Master/Child Link (Access 2000)

    The basic idea is to update the master/child properties of the subform control (as a text string) each time any of the combo boxes is updated, i.e. on the AfterUpdate event. You would need a common procedure that looks at the current value of each combo box and determines whether or not the field that combo represents should be included in the property. You may need to requery the subform to get it synchronized after you change the properties. Depending on the details of your situation, you may also want to change the data source of you subform as well. I assumed that the combo boxes were unbound by the way.

    If you need further assistance at the coding level, give us a few more of the details about your situation, and I'm sure someone can help you.
    Wendell

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Master/Child Link (Access 2000)

    You will need code to do this; there are several possibilities:
    1. <LI>Use the AfterUpdate event of the combo boxes to modify the Master and Child link fields to include only the fields corresponding to non-empty combo boxes; then Requery the subform.
      <LI>Leave the Master and Child link fields blank, and use the AfterUpdate events of the combo boxes to modify the RecordSource of the subform (build an SQL statement in code with a Where clause that only includes the fields corresponding to non-empty combo boxes.
      <LI>Leave the Master and Child link fields blank, and create a query that acts as RecordSource of the subform with Where-clause of the following form:
      ([Forms]![frmMain]![cbxEmployeeID] = [EmployeeID] Or Forms]![frmMain]![cbxEmployeeID] Is Null) And ([Forms]![frmMain]![cbxOrderID] = [OrderID] Or Forms]![frmMain]![cbxOrderID] Is Null) And ([Forms]![frmMain]![cbxYear] = [Year] Or Forms]![frmMain]![cbxYear] Is Null)
      with the appropriate names substituted, of course.

  6. #6
    3 Star Lounger
    Join Date
    Jun 2003
    Posts
    238
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Master/Child Link (Access 2000)

    how would I do #1?

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Master/Child Link (Access 2000)

    If nobody else reacts, I'll post some code later on, I don't have enough time right now...

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Master/Child Link (Access 2000)

    See if the "Cascading Combo Boxes Database" demo from Candace Tripp's MS Access Downloads helps you on your way. It contains code to set the master and child link fields of a subform in code.

  9. #9
    3 Star Lounger
    Join Date
    Jun 2003
    Posts
    238
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Master/Child Link (Access 2000)

    That was a little hard to follow, is there anyway to make Null be an option on the dropdown??

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Master/Child Link (Access 2000)

    I have attached a demo (Access 97) database that demonstrates how to do this.

    The main table tblProducts is derived from the sample table in the Northwind database. The form frmProducts is unbound; it contains two combo boxes cbxCategory and cbxSupplier, and a subform sfrmProducts. The AfterUpdate event of both combo boxes calls the function UpdateSubform. This function builds the master and child link fields in code, taking into account whether the combo boxes are empty, and then sets the master and child link fields. This is the code for the function:

    Private Function UpdateSubform()
    Dim strLinkChildFields As String
    Dim strLinkMasterFields As String

    ' Add category if combo box not empty
    If Not IsNull(Me.cbxCategory) Then
    strLinkMasterFields = strLinkMasterFields & ";cbxCategory"
    strLinkChildFields = strLinkChildFields & ";fkeyCategoryID"
    End If

    ' Add supplier if combo box not empty
    If Not IsNull(Me.cbxSupplier) Then
    strLinkMasterFields = strLinkMasterFields & ";cbxSupplier"
    strLinkChildFields = strLinkChildFields & ";fkeySupplierID"
    End If

    ' Clear link fields first
    Me.sfrmProducts.LinkMasterFields = ""
    Me.sfrmProducts.LinkChildFields = ""

    If strLinkMasterFields <> "" Then
    ' Get rid of first semicolon if string not empty
    strLinkMasterFields = Mid(strLinkMasterFields, 2)
    strLinkChildFields = Mid(strLinkChildFields, 2)
    ' Then set new links fields
    Me.sfrmProducts.LinkMasterFields = strLinkMasterFields
    Me.sfrmProducts.LinkChildFields = strLinkChildFields
    End If
    End Function

    Try the form; you will see that you can leave either of the combo boxes empty.

    Note: this demo was partially inspired by the Cascading Combo Boxes demo by Candace Tripp (see previous reply for link)
    Attached Files Attached Files

  11. #11
    3 Star Lounger
    Join Date
    Jun 2003
    Posts
    238
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Master/Child Link (Access 2000)

    This did not work, it kept saying "istric box" when i know in the code it said district box everywhere, it kept taking the "D" off??

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Master/Child Link (Access 2000)

    Did you notice the semi-colons in the code I posted? For example<pre>strLinkMasterFields = strLinkMasterFields & ";cbxCategory"</pre>

    If you don't have those, the first letter will be removed near the end.

  13. #13
    3 Star Lounger
    Join Date
    Jun 2003
    Posts
    238
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Master/Child Link (Access 2000)

    yes i did include the semi-colons? and it still deleted one letter? not sure why

  14. #14
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Master/Child Link (Access 2000)

    To see what's going on, insert the following two lines before If strLinkMasterFields <> "" Then:

    Debug.Print strLinkMasterFields
    Debug.Print strLinkChildFields

    After your code has run (and probably caused an error), open the Immediate window in the Visual Basic Editor (use Ctrl+G to activate it)
    You can inspect the lines printed there, and if you wish copy them and paste them into a post.

  15. #15
    3 Star Lounger
    Join Date
    Jun 2003
    Posts
    238
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Master/Child Link (Access 2000)

    The immediate box had nothing in it when I did that, here is my code, maybe you can see what i did wrong....

    Private Function Districtbox_Click()

    Dim strLinkChildFields As String
    Dim strLinkMasterFields As String

    If Not IsNull(Me.Districtbox) Then
    strLinkMasterFields = strLinkMasterFields & ";Districtbox"
    strLinkChildFields = strLinkChildFields & ";ISR_NO"
    End If

    If Not IsNull(Me.Monthbox) Then
    strLinkMasterFields = strLinkMasterFields & ";monthbox"
    strLinkChildFields = strLinkChildFields & ";month"

    If Not IsNull(Me.yearsbox) Then
    strLinkMasterFields = strLinkMasterFields & ";yearsbox"
    strLinkChildFields = strLinkChildFields & ";year"

    Me.Service_Query_subform_location.LinkMasterFields = ""
    Me.Service_Query_subform_location.LinkChildFields = ""

    Debug.Print strLinkMasterFields
    Debug.Print strLinkChildFields

    If strLinkMasterFields <> "" Then
    ' Get rid of first semicolon if string not empty
    strLinkMasterFields = Mid(strLinkMasterFields, 2)
    strLinkChildFields = Mid(strLinkChildFields, 2)
    ' Then set new links fields
    Me.Service_Query_subform_location.LinkMasterFields = strLinkMasterFields
    Me.Service_Query_subform_location.LinkChildFields = strLinkChildFields
    End If

    End Function

Page 1 of 2 12 LastLast

Posting Permissions

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