Running a query based of a field within a Navigation Subform

[RayMilhon]

I have an Access 2010 Database that I'm building for several Departments. I have a Form for the user to enter the Advertisements that a customer has replied To.

I'm using a Navigation Form to navigate through the data for the departments Structure is this:

MainForm Customer Specific Information
There is a tab control that has the various departments that will be using this database to enter the specific information for their department.

One of those tabs is for Potential Members. Those people that have responded that are not current Members.

We have 4 different categories so there is a tab Control for each category
Advertisements
Events that we put on
Programs That we've devised to attract customers
and Promotions that we've used to attract customers

For each of those we have several things to track.

Ads we track the type of Ad (Radio, Newspaper, facebook and some others) The Ad Content and the date the ad ran
For Events we track which event the date of the event
Programs which Program and the year and quarter of the program
Promotions. The type of promotion and the year and month used

I use a tab control for each of those and a data entry form on each tab with the appropriate fields.

what I'm trying to do is on the Advertisement Page I have 3 combo boxes. 1 for the type of Ad, 1 for the different Ad Content for that type of ad and 1 for the dates that type of ad ran.

I've included code in the after update event of the first combo box (ad type) to requery the 2nd combo box but I can't get the criteria right to limit the choices in the Ad Content to the ad type in combo1.

I'm using [Forms]![MainMenu]![Navigationsubform].[Form]![frm_Person].Form![frm_Potential_mbrs].Form![frm_advertisement_responded_to]![cbo_Advertisement]
but it keeps prompting me for the answer instead of getting it from the form. Anybody have a thought on what I'm doing wrong on the syntax

[aytee111]

To refer to an object on another form: Forms!mainform!objectname. The object could be a subform with its own object: Forms!mainform!subform!objectname.

Where is this being referred to, in code or in the combobox row source?

[RayMilhon]

combobox row source

and that is how I am referring to it

The Main form contains the Navigation Control There are 3 tabs in the Navigation Control. 1 for Data Entry, 1 for Reports and 1 for Maintenance of Lookup tables.

The Data Entry page is Frm_Person Which contains the fields in the tbl_Person table with the Demographic Information. last name, first name, dob. The form also contains Several subforms for addresses (a person could have several residence, mailing, work) emails(again a person could have several I know I do), Phone numbers (Cell, home, work) Then there is a tab control that contains a number of tabs (Contacts, Comments, Health Education, Prospective Members, Senior Programming, Fitness) each tab contains a subform
for data entry for that specific department. 2 of those (Prospective Members and Senior Programming) Also contain a tab control for subforms for specific items.
Prospective Members is (Advertisements that person may have responded to, Events they may have attended, Programs they may have signed up for and Promotions they've responded to) Senior Programming also has a couple (Senior Events attended, Senior Programs)

on the Advertisement form there are 3 fields. Type of Ad, Content of Ad, date of ad The form is based on a table that contains 3 fields (AdvRespID, PersonID,AdvSchedID)

The AdvRespID is an autonumber field that is the PrimaryKey, the PersonID is a numeric foreign key to the person tbl and the AdvSchedID is a foreign key to the Advertisement Schedule table.

The combobox for the advertisement type gets it's choices from the tbl_Adv_Source table It is unbound. Once the user selects the Advertisement type the Advertisment Schedule combo box is supposed to populate with the advertisements of that source for example if the Source selected is Radio then all of the scheduled Radio Ads would populate the combo box. once the second combo box populates and is selected the 3rd combo box would show all of the dates that particular ad ran. That way we can track which ads brought in calls and which didn't the problem is that for the 2nd combo box to populate I need to know what was selected in the 1st combo box and nothing I've tried gives me that data instead I get prompted to enter the value for that field. That tells me I've got the syntax wrong which doesn't surprise me. Since what I'm looking for is on the subform of a subform of a subform of a subform. Since it's the same form I thought I could just use the combo box name that didn't work. So I tried the full path to the combo box and that didn't work so either I've got the syntax wrong (Probable)

[aytee111]

and that is how I am referring to it

Please post your SQL.

[RayMilhon]

SELECT dbo_tbl_Adv_Schedule.adv_SchedID, dbo_tbl_Adv_Schedule.Adv_SourceID, dbo_tbl_Adv_Schedule.Adv_text
FROM dbo_tbl_Adv_Schedule
Where dbo_tbl_Adv_Schedule.Adv_SourceID = [Forms]![MainMenu]![Navigationsubform].[Form]![frm_Person].[Form]![frm_Potential_mbrs].[Form]![frm_advertisement_responded_to]![cbo_Advertisement] ;

[aytee111]

Please refer to post #2 for how to refer to form objects

[Micron]

The object could be a subform with its own object: Forms!mainform!subform!objectname.

Unless I misunderstand the meaning, this is incorrect. To refer to a control on a subform, you have to sequence from the forms collection > main form > subform control > Form object of the subform control, then you can refer to an object on the subform. Subform control name is not the the name of the subform, although Access often uses the subform name for the control if you use a form wizard. I will use the wizards for speed, but always go through and fix the names to avoid ambiguity, such as controls and fields being named the same.

[Forms]![Main form name]![subform control name].[Form]![control name on subform]

To refer to a subform property, I use a different notation since I found the above syntax didn't seem to work

Forms("MainFormName").Controls("subformControlName").Form.Recordset.Recordcount

As an aside, I'm wondering about the design of your form(s). To have a tab for each department does not seem right. As an example, any time a department is added/eliminated/renamed or whatever, that necessitates a design change (such as adding/eliminating/renaming tabs) is bad practice.

[RayMilhon]

The reason is that originally this was 4 different databases 1 for each department. Recently there was a need to compare the 4 databases to see where there was overlap. I was tasked with combining the 4 different databases into 1. Each of the 4 departments have different data elements. there is some overlap between 2 of those departments but it is minimal. There will be no additional departments added or eliminated. The only reason these 4 databases were combined into one is that the members/potential members could be in any of them. That data was combined into 1 table. Otherwise the only thing in common is 2 of the databases have special events but the events themselves are different in each of the 2 databases. Those are combined into 1 table with a field to differentiate between the 2 departments