 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / New Users / March 2006subforms and searches
I've been searching this site for a simple explanation of what I want to accomplish, but I can't seem to find it. I have a form with a subform in it (database view), and I would like to look up records that match a criteria and have the subform display all of the records that match this criteria. Specifically, I'd like a textbox or combo box to look for all records that are from the same state, and have it display all of the matching records in the subform. I have gotten it to work somewhat, with the combobox being populated with ALL of the values in the 'State' field, and when the state is selected it shows the right record, but it shows every single state record in the combobox (each state repeated multiple times). I want to only have each state show up once in the combo box then ALL the records that match the state show up in the subform. What is the basic concept needed to accomplish this? the table is 'prosmembers' the state field is 'HOME_STATE', and each record has an autonumber primary key named 'ID_number' Thank you, Mark Change the query for the combobox's row source to select distinct values. Generically, the SQL would look something like SELECT DISTINCT [Tablename].[State] FROM [TableName] ORDER BY [Tablename].[State] > I've been searching this site for a simple explanation of what I want to > accomplish, but I can't seem to find it. [quoted text clipped - 25 lines] > > Mark Hmm, let me approach it differently. I tried that sql, and it did indeed give me individual states in the combo box, but I coudn't make it filter the records in the subform. If I had a textbox in the main form, and the datasheet in the subform set to display all records, could I type something in the textbox (say NY for the state) and then it apply a filter to show only (and all) the fields that contain 'NY' in the State field? The picture in this link shows about what I want, except instead of saying "Last name, or Card# to search for" it would say "State to search for". http://www.members.shaw.ca/AlbertKallal/Search/index.html Thanks, Mark >Change the query for the combobox's row source to select distinct values. > [quoted text clipped - 9 lines] >> >> Mark Mark: Do you really need a subform? If you make your table, or better still a sorted query based on the table, the RecordSource of the main form and design it as a continuous form with the bound controls in a single row detail section and the column headings in the form header you can just put the unbound States combo box in the form header. In its AfterUpdate event procedure filter the form with: Me.Filter = "Home_State = """ & Me.cboStates & """" Me.FilterOn = True where cboStates is the name of the combo box. To show all rows again put a 'Show All' button in the header with the following in its Click event procedure: Me.cboStates = Null Me.FilterOn = False The first line is just to clear the combo box. You could use a text box instead, the code would be the same, but a combo box is a better bet. Strictly speaking you should not need to use a SELECT DISTINCT query to list the states as you should really have a States table with one row per State which the prosmembers table references in an enforced many-to-one relationship. That way an invalid state value can't be entered into prosmembers. The RowSource for the combo box would then be: SELECT State FROM States ORDER BY State; Ken Sheridan Stafford, England > Hmm, let me approach it differently. I tried that sql, and it did indeed give > me individual states in the combo box, but I coudn't make it filter the [quoted text clipped - 12 lines] > > Mark Thank you for the reyply, Ken. No, it doesn't have to be a subform, that just seemed to be the easiest way to do it. I'll try your way tomorrow. You're right about needing a separate states table, but I imported all of this data from an excel spreadsheet and so the data is not normalized. do you think I should attempt to normalize it and separate the duplicate fields? there would only be 2 or 3 duplicated fields, 'State' , 'Location ', and 'zip code'. Mark >Mark: > [quoted text clipped - 36 lines] >> >> Mark Mark: Normalizing a table is always worthwhile. With location data which is hierarchical only the bottom layer of the hierarchy should be in the referencing table as a foreign key, e.g. you might have a foreign key CityID which references a Cities table which then has a foreign key StateID referencing a States table. This eliminates the possibility of update anomalies, so San Francisco cannot be out in California in one row and Arizona in another! In your case your ‘locations’ probably roughly equate to City in the above. I’m afraid I don’t know enough about how US Zip codes work to say how they’d be handled. Here in the UK our post codes can span County boundaries (we don’t have States of course) so the relationship is not a simple one-to-many one. If in your case each location can only have one Zip code but each Zip code can cover more than one location (but not span a State boundary) then there would be one-to-many relationships from locations to Zip Codes to States. For entering this type of hierarchical location data many people like to select from the top down, first selecting a State from a combo box’s list then selecting a City from a combo box showing just the cities in the selected State. I’ve produced a little demo of ways in which this can be done, using the English administrative units of Parish, District and County, which you can find at: http://community.netscape.com/n/pfx/forum.aspx?nav=libraryMessages&tsn=1&tid=236 26&webtag=ws-msdevapps Ken Sheridan Stafford, England > Thank you for the reyply, Ken. > No, it doesn't have to be a subform, that just seemed to be the easiest way [quoted text clipped - 7 lines] > > Mark |
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.