how do I filter 2 subforms?

[mdavid]

Hi, I'm trying to search/filter 2 subforms on the main form, this is the code:

Private Sub btnSearch_Click()
Dim MempSQL As String
Dim BercSQL As String
MempSQL = "SELECT Complaint, ComplaintEdited, " _
      & "Preparation, PreparationEdited, " _
      & "Administration, AdministrationEdited, " _
      & "PartUsed, TimesRecorded, Healer, " _
      & "SpeciesID, ComplaintID, FloraPalComplaintID " _
      & "FROM MempComplaintTom " _
      & "Where Complaint LIKE '*" & Me.txtSearch & "*';"
Me.MempComplaintSubFrm.Form.RecordSource = MempSQL
Me.MempComplaintSubFrm.Form.Requery


BercSQL = "SELECT SpeciesID, ComplaintID, System, Complaint, TimesRecorded, " _
        & "PartsUsed, Preparation, Administration, InformationSource, ScientificStudies " _
        & "FROM BercComplaints " _
        & "Where Complaint LIKE '*" & Me.txtSearch & "*';"
Me.BercComplaintsSF.Form.RecordSource = BercSQL
Me.BercComplaintsSF.Form.Requery
End Sub

The strange thing is only the first filter works, and if comment out one of the queries the other will work.

Can anyone explain to me what's going on, and how I can fix it.

Thanks
David

[pbaldy]

Do you get an error, or it just doesn't filter? If you comment out the first, the second starts working?

[mdavid]

Hi Paul, very much appreciate if you could take a look at the attached db. I was mistaken, it's the second subform - BercComplaintsSF - that stops working, as soon as you use the Search Complaints it turns blank.

Thanks
David

[pbaldy]

Something weird is going on. The master/child links are what prevent the form from showing the results. I delete them and they come back. You can try a decompile to see if that fixes the problem, but this works as a workaround:

Me.BercComplaintsSF.LinkMasterFields = ""
Me.BercComplaintsSF.LinkChildFields = ""

[mdavid]

Thanks very much for taking the time to look at this

Where should I put

Me.BercComplaintsSF.LinkMasterFields = ""
Me.BercComplaintsSF.LinkChildFields = ""

[pbaldy]

I think I had them after the line setting the record source.

[mdavid]

Hi,
Today I decided to build the main form and subforms from new, but it didn't help still the same problem.
The difference between the subform that works and the one that doesn't is the working subform LinkMasterField is the primary key and the LinkMasterField of the subform that's not working is not a PK. On inspection I discovered that the value of the LinkMasterKey of the subform that's not working gets changed to the working subform's LinkMasterKey which results in it not returning any records.
In addition to these problems all the tables connected to this form have lost their primary keys - all the data is ok. Do you have any explanation for these problems?

Thanks
David

[pbaldy]

No, it seemed the link values were repopulating themselves, which I've never had happen before.