Form and Subforms link Problems..

[mtnc6 via AccessMonster.com]

Good day to everyone!

Ok, before I start, please take a look at my table...


tbl_Operating_Units
Unit_ID
Operating_Unit
Acronym ==== (ex. BAS, ATI, BAR)

tbl_Quarter
Quarter_ID
Quarter ==== (ex. First, Second, Third, Fourth)

tbl_Report_List
Report_ID
Report_Name ==== (ex. Quarterly Reports, Directives, Statistical Surveys)
Report_Acronym

tbl_Submission_Mode
Mode_ID
Mode ==== (ex. email, hardcopy, email and hardcopy)

tbl_Report_Submission
Unit_ID ==== lookup field value is [Acronym]
Report_ID ==== lookup field value is [Report_Name]
Quarter_ID ==== lookup field value is [Quarter]
Mode_ID ==== lookup field value is [Mode]
Date Received

In a form, I want to see something like this:

Main Form = [frm_Reports]; Record Source = [tbl_Report_List]; Controls =
[Report_Name]
==> Subform = [sfrm_Qtr]; Record Source = [tbl_Quarter];
Controls = [Quarter]
==> Subform = [sfrm_OU]; Record Source =
[tbl_Report_Submission]; Controls = [Unit_ID], [Mode_ID],
[Date Received]

The main form [frm_Reports] has [tbl_Report_List] as its record source. The
main form then has a subform [sfrm_Qtr] with [tbl_Quarter] as the record
source. The [sfrm_Qtr] has a subform [sfrm_OU] whose record source is
[tbl_Report_Submission].

Now, the anomally is that when I try to put some data on the 2nd subform
[sfrm_OU] and fill-in the fields, all other [Report _Name] fields of the main
form have the same record. For example, for the first row of the [sfrm_OU],
I tried to input “ATI†on [Unit_ID]; “email†on [Mode_ID]; and “08-Nov-09†on
Date Received. But the record is showing on all [Report_Name] of the main
form.

How can I make it work so that the records in [sfrm_OU] only shows on
specific [Quarter] of each [Report_Name]?

I hope I explained it well.

Thanks again in advance.

mtnc6