Results 1 to 6 of 6
  1. #1
    Join Date
    May 2006
    Posts
    25

    Unanswered: Conditional Select Statement for a combobox

    Hello,

    I have a database that keeps a record of all our local politicians' addresses and portfolios. Most politicians have more than one portfolio, but when we address a letter to them, we want to be able to pull out the portfolio that is pertinant to the subject of the letter. For example, if we are sending a submission for funding for a youth project, we would send it the the Minister for Young Territorians (but this politician is also the Minister for Women's Policy, Senior Territorians, etc. etc).

    I have set up a table called Politicians, and another called PoliticianPortfolios that is linked by the PolitciansID field.

    I have set up a form that has a combo box on it. What I want it to do is pull down a list of the portfolios of just the selected politician instead of every portfolio available.

    I am having trouble with the Select Statement and this is what I've managed so far:

    SELECT PoliticianPortfolios.PortfolioID, PoliticianPortfolios.Portfolio, FROM PoliticianPortfolios, WHERE (((PoliticianPortfolios.PoliticiansID)=[Forms]![frmPoliticians]![txtCurrentID))

    I am getting an error message that tells me that there is a reserved word or argument name that is misspelled or missing.

    Can anyone steer me in the right direction?

    Thanks,
    Bronwyn

  2. #2
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,424
    Provided Answers: 8
    Where the closing ]

    =[Forms]![frmPoliticians]![txtCurrentID]

    Its been a long Day LOL
    hope this help

    See clear as mud


    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

  3. #3
    Join Date
    May 2006
    Posts
    25

    Okay closing ] is in there!

    Thanks for picking that up. I've put that closing bracket in so the select statement now reads:

    SELECT PoliticianPortfolios.PortfolioID, PoliticianPortfolios.Portfolio, FROM PoliticianPortfolios, WHERE (((PoliticianPortfolios.PoliticiansID)=[Forms]![frmPoliticians]![txtCurrentID]))

    But I'm still getting the same error message.

  4. #4
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,424
    Provided Answers: 8
    I would Have 3 Tables

    1st = politicians Table
    politiciansID = autonumber
    Name
    ...
    ...
    ...
    ...
    ...


    2rd = portfolio Table
    portfolioID = autonumber
    portfolioName
    ....
    ....
    ....
    ....
    ....

    3rd PoliticianPortfolios Table
    PoliticianPortfoliosID = autonumber
    PoliticianID = Link to Politician Table
    PortfoliosID = Link to Portfolios Table

    my mail merge of the PoliticianPortfolios joining the Other to tables by there ID and getting the text you want.


    Doing it this way you only need 1 Politician and 1 Portfolios

    but PoliticianPortfolios will store the Dup ID's So if the Politician changes the Phone number You only have to Change 1 records not all the Dups.
    Last edited by myle; 08-27-06 at 23:41.
    hope this help

    See clear as mud


    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

  5. #5
    Join Date
    May 2006
    Posts
    25
    Yep, that was clear as mud!

    Sorry, I'm having trouble understanding what you're suggesting. At the moment, with the current setup, I have the politician's address details, phone numbers etc. stored in the Politicians table, and the Portfolios stored in the Portfolios table.

    On the Form, I have the Politician's address details from the Politicians table, and I have a subform that shows just the Portfolios for that politician based on their PoliticiansID in a data table format. I have that working okay.

    What I want to do, is rather than having the subform, I want a combobox that drops down a list of just that politician's portfolios. Then, if I select one portfolio from that list, it will populate another field called PertinantPortfolio, so I can refer to it when I send out mail.

    Remember, the PertinantPortfolio will change depending on what I am writing to the minister about.

    Does any of this make sense?

  6. #6
    Join Date
    Jun 2004
    Location
    Seattle, WA
    Posts
    601
    Myle is suggestion you basically rework the structure of your database. From what I understood from your first email and I think Myle's is also, is the following:

    You have a database of politicians. Each politician can serve on multiple committees or be the heads of them, and you are calling these portfolios.

    So what Myle is saying you do is to make a table with the politicians immediate information, like name, height, age, etc. Then make a table that has the specific information on the portfolio, title, phone, address, etc.

    You will connect these two by a third table, that is solely the ID for the politician and the portfolio.

    This will make quiries basically easier to make.
    Ryan
    My Blog

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •