On 1 May, 00:42, "Arvin Meyer [MVP]" wrote:
Your statement calls for tenants from all properties because you haven't
limited the property to a single property. Look at my where clauses and
notice how I use them to limit the value to a specific one from the form:

SELECT Products.*
FROM Products
WHERE (((Products.ProductID)=[Forms]![frmProducts]![lstProducts]));

lstProducts is a simple list box having a selected bound column value of a
specific ProductID

If you wanted it to prompt for a value instead of using one from the form,
you'd use something like:

SELECT *
FROM Products
WHERE Products.ProductID=[Which Product?];
--
Arvin Meyer, MCP, MVPhttp://www.datastrat.comhttp://www.mvps.org/accesshttp://www.accessmvp.com

"Russell Pascoe" wrote in message

...



Arvin,

Thank you for taking the time to reply.

I'm kind of with you now, which sort of takes us full circle to my first
question, which maybe you can help me with? Why does my simple ( I
thought)
SQL statement notwork?

Thanks in advance.
Russell.

"Arvin Meyer [MVP]" wrote:

The first list picks the second. The second list chooses the record to be
displayed on the form. It can be done with saved queries or SQL, or a
combination of VBA code and SQL.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

"Russell Pascoe" wrote in
message
...
Thanks Arvin,

I must be stupider than I thought I've looked through your attachment
but
am
not terribly much wiser.

You have introduced several new concepts from what I can understand, is
your
point that you have divided into categories to pick the 2nd list? If it
is,
why can't I do that dynamically using the SQL?

I am now very confused! Are there any accompanying notes to what you
sent
me? It might be easier than trying to Reverse Engineer it!

Thanks for your time!
Russell.

"Arvin Meyer [MVP]" wrote:

An example of filling a list from another is he

http://www.accessmvp.com/Arvin/Combo.zip
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

"Russell Pascoe" wrote in
message
...
Hi,

I have a table of Property Addresses, and a Table of Tenants within
the
properties, and I am trying to build a table for maintenance issues
reported, populated from a form.

The first Drop Down list in the form is a straightforward lookup
combo
box
derived from the addresses table. SELECT Addresses.Property FROM
Addresses;

The second drop down box should be populated with the name of
tenants
currently
living in the property by using another lookup combo box with an SQL
query
behind it, which currently looks like this, butdoesn'twork;

SELECT Tenants.ID, Tenants.[Last Name] FROM Tenants WHERE
Tenants.Property=[Property];

But this actually produces the whole list of tenants regardless of
the
property
they are in! When I actually only want the names of the tenants
currently
occupying the property. Somehow my statement is wrong!

What am I doing wrong?

Thanks!
Russell.- Hide quoted text -

- Show quoted text -

Arvin,

Thank you for your help - this did work for me, once I understood
where I had gone wrong, from reading your replies.

I would have replied sooner, but all of my PCs have now decided they
can't open windows for these forums...another problem to deal with.

Thank you.
Russell.