From: Iram on
Hello,
I have a subform and in this suform I have a combobox field called
"Category". In this combobox I have a select query that pulls the Category
and TeamName. On the master form I have a floating combobox called Team. When
I choose a team in this field I would like the subform combobox to be
narrowed down a little as such if possible...

If the Team field on the master form is ACCOUNTING I would like the Category
combobox to be limitted to "Accounting" and "Other" team names.

If the Team field on the master form is ESTABLISHMENT I need the Category
combobox to be limitted to "Establishment", "Enforcement", "Legal", "Other".

If the Team field on the master form is ENFORCEMENT I need the Category
combobox to be limitted to "Establishment", "Enforcement", "Legal", "Other".


If this is not possible how else can I do this?


Thanks.
Iram/mcp

From: Dorian on
It seems from your description that your tables are not normalized since you
have values in a related table that are dependent on the values in the other
table. I would think carefully about yoour table design.
What tables are yopu dealing with?
Ideally, your possible combinations of main form combo box value and subform
combo box values should be defined in another table.
However, there is no reason why the query in the subform combo box cannot be
dynamically changed when the value in the main form combo box is changed.
-- Dorian
"Give someone a fish and they eat for a day; teach someone to fish and they
eat for a lifetime".


"Iram" wrote:

> Hello,
> I have a subform and in this suform I have a combobox field called
> "Category". In this combobox I have a select query that pulls the Category
> and TeamName. On the master form I have a floating combobox called Team. When
> I choose a team in this field I would like the subform combobox to be
> narrowed down a little as such if possible...
>
> If the Team field on the master form is ACCOUNTING I would like the Category
> combobox to be limitted to "Accounting" and "Other" team names.
>
> If the Team field on the master form is ESTABLISHMENT I need the Category
> combobox to be limitted to "Establishment", "Enforcement", "Legal", "Other".
>
> If the Team field on the master form is ENFORCEMENT I need the Category
> combobox to be limitted to "Establishment", "Enforcement", "Legal", "Other".
>
>
> If this is not possible how else can I do this?
>
>
> Thanks.
> Iram/mcp
>
From: Marshall Barton on
Iram wrote:
>I have a subform and in this suform I have a combobox field called
>"Category". In this combobox I have a select query that pulls the Category
>and TeamName. On the master form I have a floating combobox called Team. When
>I choose a team in this field I would like the subform combobox to be
>narrowed down a little as such if possible...
>
>If the Team field on the master form is ACCOUNTING I would like the Category
>combobox to be limitted to "Accounting" and "Other" team names.
>
>If the Team field on the master form is ESTABLISHMENT I need the Category
>combobox to be limitted to "Establishment", "Enforcement", "Legal", "Other".
>
>If the Team field on the master form is ENFORCEMENT I need the Category
>combobox to be limitted to "Establishment", "Enforcement", "Legal", "Other".


Fairly straightforward, once you understand how relational
data bases do things. What you need is three tables to
model the many to many relationship between the categories
and the teams. You probably have a table for teams and a
table for categories, but you should also have a
TeamCategories table with just two fields, one for a foreign
key back to a categories record and the other field for a
foreign key to a related record is the team table.

With that in place, you can use a query as the category
combo box's RowSource that selects only category records
that agree with the team selected in the team combo box:

SELECT CategoryPrimaryKey, CategoryName
FROM Categories INNER JOIN TeamCategories
ON Categories.CategoryPrimaryKey =
TeamCategories.CategoryForeignKey
WHERE TeamCategories.TeamForeignKey =
Forms!yourform.teamcombobox

Then, to sync the category combo box whenever you select a
team on the main form, add a line of code to the team combo
box's AfterUpdate event:
Me.subformcontrol.Form.categorycombobox.Requery

--
Marsh
MVP [MS Access]
From: Iram on
Thanks Dorian and Marshall.

Actually what I need is the Category combobox field to be limitted to
certain teams and not so much just a single team.
If I am part of Accounting I want to see categories belonging to Accounting
and Other

If I am part of Enforcment I want to see categories belonging to
Enforcement, Establishment, OP and Other.

If I am part of etc....

This is why I need the If statement to be customized accordinally.


Thanks.
Iram/mcp





"Marshall Barton" wrote:

> Iram wrote:
> >I have a subform and in this suform I have a combobox field called
> >"Category". In this combobox I have a select query that pulls the Category
> >and TeamName. On the master form I have a floating combobox called Team. When
> >I choose a team in this field I would like the subform combobox to be
> >narrowed down a little as such if possible...
> >
> >If the Team field on the master form is ACCOUNTING I would like the Category
> >combobox to be limitted to "Accounting" and "Other" team names.
> >
> >If the Team field on the master form is ESTABLISHMENT I need the Category
> >combobox to be limitted to "Establishment", "Enforcement", "Legal", "Other".
> >
> >If the Team field on the master form is ENFORCEMENT I need the Category
> >combobox to be limitted to "Establishment", "Enforcement", "Legal", "Other".
>
>
> Fairly straightforward, once you understand how relational
> data bases do things. What you need is three tables to
> model the many to many relationship between the categories
> and the teams. You probably have a table for teams and a
> table for categories, but you should also have a
> TeamCategories table with just two fields, one for a foreign
> key back to a categories record and the other field for a
> foreign key to a related record is the team table.
>
> With that in place, you can use a query as the category
> combo box's RowSource that selects only category records
> that agree with the team selected in the team combo box:
>
> SELECT CategoryPrimaryKey, CategoryName
> FROM Categories INNER JOIN TeamCategories
> ON Categories.CategoryPrimaryKey =
> TeamCategories.CategoryForeignKey
> WHERE TeamCategories.TeamForeignKey =
> Forms!yourform.teamcombobox
>
> Then, to sync the category combo box whenever you select a
> team on the main form, add a line of code to the team combo
> box's AfterUpdate event:
> Me.subformcontrol.Form.categorycombobox.Requery
>
> --
> Marsh
> MVP [MS Access]
> .
>
From: PieterLinden via AccessMonster.com on
>
>Actually what I need is the Category combobox field to be limitted to
>certain teams and not so much just a single team.
>If I am part of Accounting I want to see categories belonging to Accounting
>and Other
>
>If I am part of Enforcment I want to see categories belonging to
>Enforcement, Establishment, OP and Other.
>
>If I am part of etc....
>
>This is why I need the If statement to be customized accordinally.
>
>Thanks.
>Iram/mcp

Sounds like this is what you want:
http://www.mvps.org/access/forms/frm0028.htm

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/201005/1