Search (Filter) button on main form to also get proper records on subform

[PlayerPianoMan]

I have a music database that tracks titles, songs, composers and performers. I would like to set up a search button for the TitleNumber field on the main form and have the subform with two nested subforms updated when the filter is applied to the main form. The record source for frmTitles is tblTitles. The subform subfrmSongs is related to frmTitles. On the subform are two nested subforms, subfrmComposer and subfrmPerformer.

On the main form I have created btnSearchCatNumber. It asks the user to input a catalog number. When the button is click the proper information is displayed on the main form but not the subfrmSongs and the nested subforms. How do I accomplish this?

Private Sub btnSearchCatNumber_Click()

      Dim strCatNumber As String
     
      strCatNumber = InputBox("Enter Catalog Number", "Catalog Number", TitleNumber)
     
      If strCatNumber = " " Then
           Exit Sub
      End If
     
      Me.Filter = "TitleNumber = """ & strCatNumber & """"
      Me.FilterOn = True
     
End Sub

[June7]

Suggest input to an unbound textbox or better, a combobox, instead of InputBox. InputBox is harder to validate input and prone to user mistyping. With combobox can use TitleID for filter.

I don't quite understand table relationships. What data entity is tblTitles for? Title is not a song?

[PlayerPianoMan]

The table tblTitles holds information about each title like media type (CD, LP, 45, etc.), Catalog Number or purchase source and so on. Each Title can have many songs (SongTitle) so the TitleID (autonumber,PK) is related to tblSongs (MediaTitle, FK). Each song can have more than one performer and more than one composer hence the many to many relationship for both using junction tables. I would rather use the input box. I will be the only one using the database so the input values will be valid.

When I click the button the information for frmTitles shows the correct information but neither the subfrmSongs or the nested subforms are updated. I get a box asking for a parameter ComposerID Ok or Cancel. If I hit the esc key 10 times (the number of songs for the filtered item) the subforms then update. If I don't press the esc key the database crashes.

[June7]

Are Master/Child Links properties set on subform container controls?

Could provide db for analysis. Follow instructions at bottom of my post.

[PlayerPianoMan]

MMTestDb.zip

Attached is a stripped copy of my database. None of the image files will display and you may get an error.

[June7]

I tested with search for "HMK 026CD" on form frmTitles. It works - all forms are updated. No additional popup prompt. What data should I use to replicate issue?

Why do some records not have TitleNumber?

[PlayerPianoMan]

Some titles will not have a TitleNumber aka Catalog Number because they are a digital file that was downloaded.

I tried the same search for "HMK 026CD" and I was prompted for a parameter and the songs, composer, group/performers in the subform were not correct. I circled the prompt and the items on the subform.

This would seem to indicate that it may be the different versions of Access. I am using the most recent Microsoft 365 version of Access 64 bit.

[June7]

As shown by my profile, I use Access 2010 32-bit.

There is little or no advantage gained by use of 64-bit for most.

[PlayerPianoMan]

Are you saying that this is the problem? I had to go to 64 bit because most of the people that I write code for has switched to 64 bit because their work required it so I had to since you can only have one installation either 32 bit or 64 bit. When you ran your search what was the first song name, the composer(s) and the performer?

[June7]

I have never used 64-bit Office apps but haven't read anything positive about 64-bit Access. I can't say for certain issue is Office 365 and/or 64-bit, just know I can't get the issue you describe. My understanding is can install 32-bit Access even if other apps are 64-bit.

Circular As Our Way
Byrd, Marc and Thompson, Andrew
Hammock

[Welshgasman]

Just how do you do a search?
Please explain the steps, I cannot see the method a user is meant to use?

[PlayerPianoMan]

That is correct so it should work. Maybe I will try Microsoft Support to see it they can figure it out. Microsoft has said that you can not install 32 bit Access and have 64 bit Microsoft 365 on the same computer.

[PlayerPianoMan]

You just click on the binocular icon next to the catalog number on the main form. The form that you referenced is used in conjunction with another process entirely.

[Welshgasman]

You just click on the binocular icon next to the catalog number on the main form. The form that you referenced is used in conjunction with another process entirely.

OK. I get the same request as you?

Multiple times as well.

[Welshgasman]

OK,
It appears it is down to having a filter remaining in your composer subform which was something along the lines of Lookup.Composer = 'b'. Actually

([Lookup_ComposerID].[Display]="b")

I removed that filter and now that search works for me.

I also amended the code of the requery before I did that, as i was not sure you can requery a subform control?

Private Sub btnSearchCatNumber_Click()


     Dim strCatNumber As String
     
     strCatNumber = InputBox("Enter Catalog Number", "Catalog Number", TitleNumber)
     
     If strCatNumber = " " Then
          Exit Sub
     End If
     
     Me.Filter = "TitleNumber = """ & strCatNumber & """"
     Me.FilterOn = True
     Me.subfrmSongsB.Form.Requery    'Added Form property?
     
End Sub

Edit: I put the code back as it was and it still searches successfully.