Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Selecting field from selected row (newb question)

Discussion in 'Information Technology' started by msaccess, Jul 28, 2006.

  1. msaccess

    msaccess
    Expand Collapse
    Guest

    I am trying to make an address book db. I have 3 tables:
    Company, CompanyAddress, and Contacts. Primary keys are CompanyID,
    AddressID, and ContactID respectively. The CompanyAddress table allows
    me to store multiple addresses for each company. In Contacts Table I
    have included fields for Company and Address. While entering contact
    Info I want to let the user be able to lookup values from Company and
    CompanyAddress Table. I used the lookup wizard and it was easy to
    lookup the company name. When I did same for company address, I
    obviously end up with all address values in CompanyAddress table. I
    want to show only the addresses that belong to the company that has
    been already selected.. Any ideas on how to change the select query..
    or any other way to get same results.

    at present I have following query for Lookup.rowsource
    SELECT CompanyAddress.AddressID, CompanyAddress.City,
    CompanyAddress.State FROM CompanyAddress;
     
  2. Loading...


  3. Arvin Meyer [MVP]

    Arvin Meyer [MVP]
    Expand Collapse
    Guest

    Are you sure that you have the CompanyID as a foreign key in the Address and
    Contacts tables? If so, just add a where clause to your select statement:

    WHERE CompanyID = 1234;
    --
    Arvin Meyer, MCP, MVP
    Microsoft Access
    Free Access downloads
    http://www.datastrat.com
    http://www.mvps.org/access

    "msaccess" <vaibhavsemail@gmail.com> wrote in message
    news:1150687969.553597.91260@r2g2000cwb.googlegroups.com...
    >I am trying to make an address book db. I have 3 tables:
    > Company, CompanyAddress, and Contacts. Primary keys are CompanyID,
    > AddressID, and ContactID respectively. The CompanyAddress table allows
    > me to store multiple addresses for each company. In Contacts Table I
    > have included fields for Company and Address. While entering contact
    > Info I want to let the user be able to lookup values from Company and
    > CompanyAddress Table. I used the lookup wizard and it was easy to
    > lookup the company name. When I did same for company address, I
    > obviously end up with all address values in CompanyAddress table. I
    > want to show only the addresses that belong to the company that has
    > been already selected.. Any ideas on how to change the select query..
    > or any other way to get same results.
    >
    > at present I have following query for Lookup.rowsource
    > SELECT CompanyAddress.AddressID, CompanyAddress.City,
    > CompanyAddress.State FROM CompanyAddress;
    >
     
  4. msaccess

    msaccess
    Expand Collapse
    Guest

    Arvin Meyer [MVP] wrote:
    > Are you sure that you have the CompanyID as a foreign key in the Address and
    > Contacts tables?


    Yes

    If so, just add a where clause to your select statement:
    >
    > WHERE CompanyID = 1234;


    I cannot solve my problem in simple way as above. My original question
    was exactly this, how do I refer to the value of CompanyID in current
    row. I do not have a constant value for CompanyID. I have tried the
    following where clause and some variations:

    WHERE CompanyID=CompanyAddress.CompanyID

    but it does not work. I do not get any values in drop down list with
    it.


    Thanks!


    > --
    > Arvin Meyer, MCP, MVP
    > Microsoft Access
    > Free Access downloads
    > http://www.datastrat.com
    > http://www.mvps.org/access
    >
    > "msaccess" wrote in message
    > news:1150687969.553597.91260@r2g2000cwb.googlegroups.com...
    > >I am trying to make an address book db. I have 3 tables:
    > > Company, CompanyAddress, and Contacts. Primary keys are CompanyID,
    > > AddressID, and ContactID respectively. The CompanyAddress table allows
    > > me to store multiple addresses for each company. In Contacts Table I
    > > have included fields for Company and Address. While entering contact
    > > Info I want to let the user be able to lookup values from Company and
    > > CompanyAddress Table. I used the lookup wizard and it was easy to
    > > lookup the company name. When I did same for company address, I
    > > obviously end up with all address values in CompanyAddress table. I
    > > want to show only the addresses that belong to the company that has
    > > been already selected.. Any ideas on how to change the select query..
    > > or any other way to get same results.
    > >
    > > at present I have following query for Lookup.rowsource
    > > SELECT CompanyAddress.AddressID, CompanyAddress.City,
    > > CompanyAddress.State FROM CompanyAddress;
    > >
     
  5. Arvin Meyer [MVP]

    Arvin Meyer [MVP]
    Expand Collapse
    Guest

    You cannot work from a row in a table or query because you cannot run code.
    From a form, use the expression:

    WHERE CompanyID = [Forms]![YourFormName]![YourControlName]

    a control being a textbox or combo box, etc. A Control can have a name which
    is the same as the underlying field it contains, but it is better to name it
    differently, so a textbox for CompanyID should have a name like
    txtCompanyID.

    --
    Arvin Meyer, MCP, MVP
    Microsoft Access
    Free Access downloads
    http://www.datastrat.com
    http://www.mvps.org/access

    "msaccess" <vaibhavsemail@gmail.com> wrote in message
    news:1150865225.689434.130390@i40g2000cwc.googlegroups.com...
    >
    > Arvin Meyer [MVP] wrote:
    >> Are you sure that you have the CompanyID as a foreign key in the Address
    >> and
    >> Contacts tables?

    >
    > Yes
    >
    > If so, just add a where clause to your select statement:
    >>
    >> WHERE CompanyID = 1234;

    >
    > I cannot solve my problem in simple way as above. My original question
    > was exactly this, how do I refer to the value of CompanyID in current
    > row. I do not have a constant value for CompanyID. I have tried the
    > following where clause and some variations:
    >
    > WHERE CompanyID=CompanyAddress.CompanyID
    >
    > but it does not work. I do not get any values in drop down list with
    > it.
    >
    >
    > Thanks!
    >
    >
    >> --
    >> Arvin Meyer, MCP, MVP
    >> Microsoft Access
    >> Free Access downloads
    >> http://www.datastrat.com
    >> http://www.mvps.org/access
    >>
    >> "msaccess" wrote in message
    >> news:1150687969.553597.91260@r2g2000cwb.googlegroups.com...
    >> >I am trying to make an address book db. I have 3 tables:
    >> > Company, CompanyAddress, and Contacts. Primary keys are CompanyID,
    >> > AddressID, and ContactID respectively. The CompanyAddress table allows
    >> > me to store multiple addresses for each company. In Contacts Table I
    >> > have included fields for Company and Address. While entering contact
    >> > Info I want to let the user be able to lookup values from Company and
    >> > CompanyAddress Table. I used the lookup wizard and it was easy to
    >> > lookup the company name. When I did same for company address, I
    >> > obviously end up with all address values in CompanyAddress table. I
    >> > want to show only the addresses that belong to the company that has
    >> > been already selected.. Any ideas on how to change the select query..
    >> > or any other way to get same results.
    >> >
    >> > at present I have following query for Lookup.rowsource
    >> > SELECT CompanyAddress.AddressID, CompanyAddress.City,
    >> > CompanyAddress.State FROM CompanyAddress;
    >> >

    >
     

Share This Page