A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Using Forms
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Conditional Drop Down Lists



 
 
Thread Tools Display Modes
  #1  
Old April 29th, 2008, 10:53 PM posted to microsoft.public.access.forms
Russell Pascoe
external usenet poster
 
Posts: 25
Default Conditional Drop Down Lists

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, but doesn't work;

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.


  #2  
Old April 30th, 2008, 03:50 AM posted to microsoft.public.access.forms
Arvin Meyer [MVP]
external usenet poster
 
Posts: 4,231
Default Conditional Drop Down Lists

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, but doesn't work;

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.




  #3  
Old April 30th, 2008, 10:01 AM posted to microsoft.public.access.forms
Russell Pascoe
external usenet poster
 
Posts: 25
Default Conditional Drop Down Lists

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, but doesn't work;

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.





  #4  
Old April 30th, 2008, 12:39 PM posted to microsoft.public.access.forms
Arvin Meyer [MVP]
external usenet poster
 
Posts: 4,231
Default Conditional Drop Down Lists

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, but doesn't work;

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.







  #5  
Old April 30th, 2008, 07:11 PM posted to microsoft.public.access.forms
Russell Pascoe
external usenet poster
 
Posts: 25
Default Conditional Drop Down Lists

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 not work?

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, but doesn't work;

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.








  #6  
Old May 1st, 2008, 12:42 AM posted to microsoft.public.access.forms
Arvin Meyer [MVP]
external usenet poster
 
Posts: 4,231
Default Conditional Drop Down Lists

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, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://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 not work?

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, but doesn't work;

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.










  #7  
Old May 6th, 2008, 08:41 PM posted to microsoft.public.access.forms
[email protected]
external usenet poster
 
Posts: 1
Default Conditional Drop Down Lists

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.
 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 07:15 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.