Selecting field from selected row (newb question)

[msaccess]

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;

 

[Arvin Meyer [MVP]]

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]

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 [MVP]]

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