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. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 | |
|
|