Friends,
I am having a wonderful time trying to get my continuous form to filter correctly.
I have a QBF search form which returns record results in a subform located at the bottom of the search form. The QBF works utilizing MS's dynamic QBF approach (using QueryDef to build an SQL string that populates the search results subform).
My search form searches on fields from three separate tables [tblTranscripts],[tblFreqs] and [tblTargets]. [tblTranscripts] is the main table and has a 1-to-Many relationship to both [tblTargets] and [tblFreqs]. The search results subform shows fields from each of the three tables. When the user clicks the search results subform record selector, the associated main data entry form [frmTranscripts] is opened.
The Problem: When records are returned, I get more returned than I want. For example, if I have 3 records in [tblTranscripts] that match the criteria, but each of those [tblTranscripts] records has two linked [tblFreqs] records, then the search results subform recordset returns six records. These records are not repeat records, of course, since they all have something unique. Because of this, changing the SELECT keyword in the SQL statment to SELECT DISTINCT (or DISTINCTROW) has no effect on the recordset returned. When the user clicks on the returned records in the subform detail, they realize that there are only three unique records. I want (and the users expect) only the three main records to be returned.
I need to be able to filter the returned recordset one more time based on a unique primary key in the main table, I think. Does anyone know how to do this? I have no idea how to write the filter that does this. (filter = unique tblTranscripts.TranscriptID
) Just kidding.
Also, I searched the boards and came across a post from Pat Hartman which made me think that I should try to set up my [tblTranscripts] to have a double index (two primary keys) to enable this thing to sort they way I want. I tried it, but it did not change anything.
Any help, hints for this would be GREATLY appreciated. I've been searching all over.
-Lou
I am having a wonderful time trying to get my continuous form to filter correctly.
I have a QBF search form which returns record results in a subform located at the bottom of the search form. The QBF works utilizing MS's dynamic QBF approach (using QueryDef to build an SQL string that populates the search results subform).
My search form searches on fields from three separate tables [tblTranscripts],[tblFreqs] and [tblTargets]. [tblTranscripts] is the main table and has a 1-to-Many relationship to both [tblTargets] and [tblFreqs]. The search results subform shows fields from each of the three tables. When the user clicks the search results subform record selector, the associated main data entry form [frmTranscripts] is opened.
The Problem: When records are returned, I get more returned than I want. For example, if I have 3 records in [tblTranscripts] that match the criteria, but each of those [tblTranscripts] records has two linked [tblFreqs] records, then the search results subform recordset returns six records. These records are not repeat records, of course, since they all have something unique. Because of this, changing the SELECT keyword in the SQL statment to SELECT DISTINCT (or DISTINCTROW) has no effect on the recordset returned. When the user clicks on the returned records in the subform detail, they realize that there are only three unique records. I want (and the users expect) only the three main records to be returned.
I need to be able to filter the returned recordset one more time based on a unique primary key in the main table, I think. Does anyone know how to do this? I have no idea how to write the filter that does this. (filter = unique tblTranscripts.TranscriptID
) Just kidding.Also, I searched the boards and came across a post from Pat Hartman which made me think that I should try to set up my [tblTranscripts] to have a double index (two primary keys) to enable this thing to sort they way I want. I tried it, but it did not change anything.
Any help, hints for this would be GREATLY appreciated. I've been searching all over.
-Lou
Last edited:
