Displaying only certain records in a subform

[Tim Graichen]

In subFormC, I would like to display only those records that have TrialDate
and CompanyCode equal to TrialDate and CompanyCode in subFormB.

Can somebody please help me?

Thanks

Tim

[Rich P]

You can base the recordsource of subformC on a query with a query string
like this:

Dim strSql As String

strSql = "Select * from sourceTbl Where (TrialDate in (Select TrialDate
from subformBsourcetbl Group By TrialDate )) And (CompanyCode in (Select
CompanyCode From subformBsourcetbl Group By CompanyCode ))"

Me.subFormC.Form.RecordSource = strSql

Me.Requery
Me.Refresh

The Group By in the sub queries of the sourceTable of subformb groups
Trialdate to Distinct TrialDates. Like say you had 10 dates of 1/1/01
and 20 dates of 2/1/01 and 30 dates of 3/1/01, the group by gives you 3
dates, 1/1/01, 2/1/01, and 3/1/01. The same for companycode.

HTH
Rich

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Tim Graichen]

Rich,
I tried the code that you wrote below, but I got the following error
whenever I tried to include the Dim statement
" Invalid SQL Statement; Expected 'Delete', 'Insert', 'Procedure',
'Select', or 'Update'

Here is my code now in a simplified version

SELECT *
FROM TblRunSpecs
WHERE TblRunSpecs.TrialDate=TblTrialsMainData.TrialDate And
TblRunSpecs.CompanyKey=TblTrialsMainData.CompanyKe y;

This code DOES produces the results I'm looking for except that I have to
manually enter the parameters

I know that I need the Dim statement at the beginning, and the RecordSource
statement at the end, but I can't get around the error.

Can you help?

Thanks...Tim

"Rich P" <rp*****@aol.com> wrote in message
news:41**********************@news.newsgroups.ws.. .

You can base the recordsource of subformC on a query with a query string
like this:

Dim strSql As String

strSql = "Select * from sourceTbl Where (TrialDate in (Select TrialDate
from subformBsourcetbl Group By TrialDate )) And (CompanyCode in (Select
CompanyCode From subformBsourcetbl Group By CompanyCode ))"

Me.subFormC.Form.RecordSource = strSql

Me.Requery
Me.Refresh

The Group By in the sub queries of the sourceTable of subformb groups
Trialdate to Distinct TrialDates. Like say you had 10 dates of 1/1/01
and 20 dates of 2/1/01 and 30 dates of 3/1/01, the group by gives you 3
dates, 1/1/01, 2/1/01, and 3/1/01. The same for companycode.

HTH
Rich

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Rich P]

If TblTrialsMainData is the source table for subformB then the following
sql string should work:

strSql = "Select * from TblRunSpecs Where _
& "(TrialDate In (Select TrialDate " _
& "From TblTrialsMainData Group By TrialDate)) And " _
& "(CompanyKey In (Select CompanyKey From " _
& "TblTrialsMainData Group By CompanyKey))"

This code assumes that TblRunSpecs contains a bulk of data and you want
to retrieve a subset of records from TblRunSpecs where the records
contain TrialDates and CompanyKey the same as the TrialDates and
CompanyKey from TblTrialsMainData

Again, the two subqueries in the sql string above will retrieve all the
unique TrialDates from TblTrialsMainData and all the unique CompanyKeys
from TblTrialsMainData.

Before implementing this sql into your project you should test it as
follows:

Sub testSql()
Dim strSql As String, RS AS DAO.Recordset

strSql = "Select * from TblRunSpecs Where _
& "(TrialDate In (Select TrialDate " _
& "From TblTrialsMainData Group By TrialDate)) And " _
& "(CompanyKey In (Select CompanyKey From " _
& "TblTrialsMainData Group By CompanyKey))"

Set RS = CurrentDB.OpenRecordset(strSql)
RS.MoveLast
Msgbox RS.RecordCount
End Sub

In your hardcoded query, the number of records you get from that should
match RS.RecordCount.

Rich

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!