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
|
|||
|
|||
subforms 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 -- Message posted via http://www.accessmonster.com |
#2
|
|||
|
|||
subforms and searches
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] "ragsman via AccessMonster.com" u18335@uwe wrote in message news:5c8fc270df736@uwe... 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 -- Message posted via http://www.accessmonster.com |
#3
|
|||
|
|||
subforms and searches
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/AlbertKal...rch/index.html Thanks, Mark John Spencer wrote: 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 -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...arted/200602/1 |
#4
|
|||
|
|||
subforms and searches
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 procedu 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 "ragsman via AccessMonster.com" wrote: 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/AlbertKal...rch/index.html Thanks, Mark |
#5
|
|||
|
|||
subforms and searches
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 Ken Sheridan wrote: 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 procedu 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 -- Message posted via http://www.accessmonster.com |
#6
|
|||
|
|||
subforms and searches
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/...g=ws-msdevapps Ken Sheridan Stafford, England "ragsman via AccessMonster.com" wrote: 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 |
Thread Tools | |
Display Modes | |
|
|