 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Forms / May 2008Conditional 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. An example of filling a list from another is here: http://www.accessmvp.com/Arvin/Combo.zip  SignatureArvin Meyer, MCP, MVP http://www.datastrat.com http://www.mvps.org/access http://www.accessmvp.com > Hi, > [quoted text clipped - 23 lines] > Thanks! > Russell. 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. > An example of filling a list from another is here: > [quoted text clipped - 26 lines] > > Thanks! > > Russell. 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. SignatureArvin Meyer, MCP, MVP http://www.datastrat.com http://www.mvps.org/access http://www.accessmvp.com > Thanks Arvin, > [quoted text clipped - 47 lines] >> > Thanks! >> > Russell. 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. > 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 [quoted text clipped - 50 lines] > >> > Thanks! > >> > Russell. 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?]; SignatureArvin Meyer, MCP, MVP http://www.datastrat.com http://www.mvps.org/access http://www.accessmvp.com > Arvin, > [quoted text clipped - 68 lines] >> >> > Thanks! >> >> > Russell. > 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 [quoted text clipped - 102 lines] > > - 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. |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.