Are If statements possilbe in Queries?
in [Forms]
|
From: Iram on 3 May 2010 15:43 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 3 May 2010 18:00 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 3 May 2010 18:22 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 4 May 2010 16:54 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 4 May 2010 17:23 > >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
|
Next
|
Last
Pages: 1 2 Prev: Retain combo box order Next: Is it possible to have If statements in a query? |