Searching middle form of 3-linked subforms on a unbound main form (1 Viewer)

[NBRJ]

I'm going to detail the structure I have, the question is after (highlighted)

I have a main form (well, almost, it's inside the navigation subform....): fSoftwareItem. This is unbound and houses 3 subforms that are linked to each other, in 1:M relationship as ordered below.

These forms are linked as per instructions here:
Microsoft Access FormsSynchronize Two Related Subforms on a Microsoft Access Form

1. fDevelopersSubform
   • Table: tDeveloper
   • Fields:
     • IDDeveloper
     • DeveloperName
2. fTitleSubform
   • Table: tTitle
   • Fields:
     • IDTitle
     • FIDDeveloper
     • FIDSuite (cbo lookup on form to tSuite (IDSuite, FIDDeveloper, SuiteName) where FIDDeveloepr fields match because Suite is optional)
     • Title
     • SoftwareFunction
     • FIDType (cbo lookup to tType)
3. fVersionSubform
   • Table: tVersion
   • Fields:
     • IDVersion
     • FIDTitle
     • Version
     • FIDAvailability (cbo lookup to tAvailability (i.e. Live, Proposed, Archived, etc))
     • FIDLicenseType (cbo lookup to fLicenseType (i.e. Standard, Concurrent, Site, Free)
     • LicenseCount
     • LicenseCurrent (Y/N)
     • LicenseDeactivationReq(Y/N)
     • DETAILS link to open a MODAL form (fVersionDetailSubform) that houses the full tVersion fields. The above are the key info fields, but users can store more as required, via this detail form.
       • FIDPreviousVersion (cbo lookup to iself to indicate the version that preceeded it. Filtered via a form_load() event to only show the entries where the Title match AND is not the current record.
       • (plus a few more...)
       • There is also an embedded Subform within that Modal form, fVersionDetailSubform, called fSoftwareAssociationSubform. This is where users can specify which software items(versions) has a relationship (i.e a plugin item will have a relation to a full software item). It is a self-join (tVersion 1:M tAssociations M: tVersion).

Within the header of fDeveloperSubform I have a search function. It works to filter the developer list and then when a developer is selected fTitleSubform and fVersionSubform appropriately filter to related records (obviously the first in each returned recordsource):

Private Sub cmdFilter_Click()

Dim strFilterStart As String
Dim strFilterEnd As String
Dim strFilterDeveloper As String

    strFilterStart = "SELECT * FROM [tDeveloper] WHERE "
    strFilterEnd = " ORDER BY [tDeveloper].[DeveloperName];"
    strFilterDeveloper = "[tDeveloper].[DeveloperName] Like ""*"" & Nz(Forms![fNavigation].[NavigationSubform].Form![fDeveloperSubform].form.[txtFilterDeveloperName]) & ""*"""

    Me.RecordSource = strFilterStart & strFilterDeveloper & strFilterEnd
    
End Sub

HOWEVER.... it has occured to me, after I'd done the Developer search, and was thinking about the Title search, that in some cases users will NOT always know the Developer of a software Title (bourne out by my perusal of the import data today. Some are going to be entered with UNKNOWN as the Developer. Especially with some of the smaller VERY specific software where it's not always clear who made it... and even if they do have a Developer, people don't remember those Developer names much anyway - the Title is the main thing people go by (but we still need Developer info for other purposes).

Ok, what has me scratching my head:
I wanted to put a textbox to use as a Title search and have fTitleSubform display all matching records (similar to Developer search above). When the user then selects a record from that filtered list, obviously the linked child subform (fVersionSubform) will update with matching records, but how to get the PARENT fDeveloperSubform to reload with the parent record? And not error when the search results appear.

Would it be easier with a combobox, where you can only select ONE title? Although that's going to be one long cbobox list... and they don't always know the exact name and some are similar, so a return of like results is better. Is the Title search/filter txt/cbo better placed in the unbound mainform, fSoftwareItem?

Is this even possible?

Or do I have a modal search form that displays a list of search results (i.e. using a query that includes tDeveloper and tTitle) and then when a user selects one it closes the form and reloads the 3 subforms in response to the selection. Seems a bit cumbersome, I'd like to avoid that if possible.

It's working well for data entry. The relationships between the tables are correct.... I'm just baffled how to do this one small thing

 

[NBRJ]

So the logic so far is:

I have text boxes in the header of fSoftwareItem

• txtFilterDeveloperName (moved out of the fDeveloperSubform)
• txtFilterTitleName

And for now 2 seperate buttons, one for each field test:

• cmdDev
• cmdTitle

(eventually I'll have the filter actions on tab and/or return out of fields)

[B]Private Sub cmdDev_Click()
[/B]
Dim strFilterStart As String
Dim strFilterDeveloper As String
Dim strFilterEnd As String

    strFilterStart = "SELECT * FROM [tDeveloper] WHERE "
    strFilterDeveloper = "[tDeveloper].[DeveloperName] Like ""*"" & Nz(Forms![fNavigation].[NavigationSubform].Form![txtFilterDeveloperName]) & ""*"""
    strFilterEnd = " ORDER BY [tDeveloper].[DeveloperName];"

    Me!fDeveloperSubform.Form.RecordSource = strFilterStart & strFilterDeveloper & strFilterEnd
    
[B]End Sub[/B]

Still working, happy days.

Now for the other one, txtFilterTitleName. Below will work, but only, of course, if I remove the Master/Child links between fDeveloperSubform and fTitleSubform (via the intermediary textbox, txtDevID, as linked to from thread starting post.

Private Sub FilterTitle()

Dim strFilterStart As String
Dim strFilterTitle As String
Dim strFilterEnd As String

    strFilterStart = "SELECT * FROM [tTitle] WHERE "
    strFilterTitle = "[tTitle].[Title] Like ""*"" & Nz(Forms![fNavigation].[NavigationSubform].Form![txtFilterTitleName]) & ""*"""
    strFilterEnd = " ORDER BY tTitle.Title;"

Me!fTitleSubform.Form.RecordSource = strFilterStart & strFilterTitle & strFilterEnd
    
End Sub

So the logic. Because of the use of wildcards, I can get more than one record back from tTitle search, and because Access will pick the first record returned as the "current" selected record, even if I then matched that FIDDeveloper to IDDeveloper, because of the linking, it could only ever get in fDeveloperSubform and therefore fTitleSubform, that first item and the rest would be ignored. I can't see there is any way other than to have some sort of form (fTitleSearchResults) pop up on the cmdTitle_click with the search results, from which a user selects the record they want, have the popup form close, and populate the relevent fDeveloperSubform and then find that record in fTitleSubform. However, if only one result is returned, I'd like to avoid the popup entirely.

How does that sound and any hints how to do that?

Or any better suggestions?

 

[NBRJ]

Here's what I've done so far, I just passed the variables in order of the forms, i.e. parent to child.

from fSoftwareItem (the unbound form where the Title seach box is) it passes the search results. An empty field will result in all the title records.

Private Sub FilterTitle()

Dim strFilterStart As String
Dim strFilterTitle As String
Dim strFilterEnd As String
Dim strFilterTitleToForm As String
Dim strFormName As String

strFormName = "[B]fTitleSearchResults[/B]"[COLOR=SeaGreen] 'Popup form with Title search results[/COLOR]

    strFilterStart = "SELECT tTitle.IDTitle, tTitle.Title, tTitle.FIDDeveloper, tDeveloper.DeveloperName, tTitle.FIDSuite, tSuite.SuiteName, tTitle.FIDType, tType.SoftwareType, tTitle.SoftwareFunction FROM tDeveloper RIGHT JOIN (tType RIGHT JOIN (tSuite RIGHT JOIN tTitle ON tSuite.IDSuite = tTitle.FIDSuite) ON tType.IDType = tTitle.FIDType) ON tDeveloper.IDDeveloper = tTitle.FIDDeveloper "
    strFilterTitle = "WHERE [tTitle].[Title] Like ""*"" & Nz(Forms![fNavigation].[NavigationSubform].Form![txtFilterTitleName]) & ""*"" "
    strFilterEnd = " ORDER BY tTitle.Title;"

    strFilterTitleToForm = strFilterStart & strFilterTitle & strFilterEnd

    DoCmd.OpenForm strFormName, , , , , , [COLOR=Indigo]strFilterTitleToForm[/COLOR]
    
End Sub

The popup form that results then lists the those records found. Not quite sure why Null returns all, surely it should return none (not that I mind!

Private Sub Form_Open(Cancel As Integer)
    If Not IsNull(Me.[COLOR=Indigo]OpenArgs[/COLOR]) Then
        Me.RecordSource = Me.[COLOR=Indigo]OpenArgs[/COLOR]
    End If
End Sub

When they click on a record this then runs:

Private Sub Title_Click()

Dim x As Long
Dim y As Long

x = Me.FIDDeveloper
y = Me.IDTitle

    Dim strReturnDev As String
    strReturnDev = "SELECT * FROM [tDeveloper] WHERE [tDeveloper].[IDDeveloper] =" & x & ";"

    Forms![fNavigation].[NavigationSubform].Form![fDeveloperSubform].Form.RecordSource = strReturnDev 
    
    Dim strReturnTitle As String
    strReturnTitle = "SELECT * FROM [tTitle] WHERE [tTitle].[IDTitle] =" & y & ";"

    Forms![fNavigation].[NavigationSubform].Form![fTitleSubform].Form.RecordSource = strReturnTitle 
End Sub

What I can't figure out how to do, is the for Title form, I want to display all Titles that match the FIDDeveloper, but have the matching IDTitle selected. Anyone have any suggestions?