1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: 2nd Subform won't display when using combo on Main form

Discussion in 'Business Applications' started by mariaa33, Sep 14, 2010.

Thread Status:
Not open for further replies.
Advertisement
  1. mariaa33

    mariaa33 Thread Starter

    Joined:
    Aug 7, 2008
    Messages:
    164
    I have a Main form with 2 subforms. The first subform is in Dataview and the second is in single form view. I have a hidden field on the main form called txtscvid with control of =[Path subform].[Form].[SVCID]. On the 2nd subform I have Child=scvid and Master=txtscvid. I also have on the 1st subform the code for "on click" Me.Parent![Record subform2].Requery. Also, both subforms uses the same query. This all works fine as I cycle through the Main Form records. I can click on each record in subform1 and that record is displayed in subform2. However I also have a combo box on the Main form where I search the for orderid in the subform1. When I select the desired orderid in the dropdown, the subform1 displayed the desired records but the second subform disappears. What am I missing in the coding to make the combo search work correctly for the second subform?

    Note: If I open my form and the first record that appears does not have any records for the the subform, and I do a search then the subform2 will never appear. But if I cycle to one that does and then close my form and reopen then the combo box works and displays my records with the subform2 at the bottom.
     
  2. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    Maria, it sounds like you need to refresh the master/child links for the 2nd subform after you have "found" the orderid you select in the Combo.
     
  3. mariaa33

    mariaa33 Thread Starter

    Joined:
    Aug 7, 2008
    Messages:
    164
    How and where do I do that?
     
  4. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    What is the VBA for your "Find" Combo?
     
  5. mariaa33

    mariaa33 Thread Starter

    Joined:
    Aug 7, 2008
    Messages:
    164
    Private Sub Combo31_AfterUpdate()
    Dim rs As Object, SQL As String, fstring As String
    SQL = "SELECT [Path].* " & _
    "FROM [Path] " & _
    "WHERE [Inventory].[orderid] = '" & Me.Combo31 & "' "
    Set rs = CurrentDb.OpenRecordset(SQL)
    If rs.RecordCount = 0 Then
    MsgBox "orderid" & Me.Combo31 & " does not exist in the database."

    Else
    fstring = rs.[Node]
    rs.Close
    Set rs = Nothing
    Me.Filter = "[Node] = '" & fstring & "' "
    Me.FilterOn = True
    End If
    'clear out the search field for the next search
    Me.Combo31 = Null
    Set rs = Nothing

    End Sub
     
  6. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    After Me.FilterOn = True try adding
    Me.[Record subform2].Requery

    You should also check that the mainform txtscvid has updated to the new [Path subform].[Form].[SVCID].
     
  7. mariaa33

    mariaa33 Thread Starter

    Joined:
    Aug 7, 2008
    Messages:
    164
    I tried that requery and it still does not work. The txtscvid changes/updates to the corresponding SVCID as I click on each record. I don't understand why the subform2 does not show up as at least a blank form when there are no records on subform1. It only appears when cycling through the Main form records and there is data on subform1.
     
  8. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    If there are no records in Subform1, what value do you get in txtscvid for the 2nd subform to find?
     
  9. mariaa33

    mariaa33 Thread Starter

    Joined:
    Aug 7, 2008
    Messages:
    164
    It shows #Error
     
  10. mariaa33

    mariaa33 Thread Starter

    Joined:
    Aug 7, 2008
    Messages:
    164
    Also, when I use the combo, and have records, I show the correct info in txtscvid but still no subform2.
     
  11. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    Well that explains why you don't get any records in subform2 when there aren't any in subform1.
    Can you post an Access 2003 format of the database, or send me a copy to look at?

    It sounds like the "updating sequence" is not correct for when there are records.
     
  12. mariaa33

    mariaa33 Thread Starter

    Joined:
    Aug 7, 2008
    Messages:
    164
    I understand that I would not get any records but I thought it would at least display the form but be blank. This is 2007 and the data is confidential.
     
  13. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    Is the subform & it's Record Source set to allow New Records?
    That usually allows blank forms to be displayed because of the new record.

    The next point is can the 2nd subform have records to match the mainform when there are no records in the 1st subform?
    If so it suggests that you need a different Link.
     
  14. mariaa33

    mariaa33 Thread Starter

    Joined:
    Aug 7, 2008
    Messages:
    164
    I tried setting to allow new records and it is still the same. Also, the subform2 can't have records in it without subform1 having records.
     
  15. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    What about the Record Source, is it updatable?
    If it isn't the form can't add records anyway, in fact how do you add records?
     
  16. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/949920

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice