subforms and searches

[ragsman via AccessMonster.com]

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

 

[John Spencer]

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]

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.

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

 

[Guest]

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

 

[ragsman via AccessMonster.com]

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:

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

 

[Guest]

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...yMessages&tsn=1&tid=23626&webtag=ws-msdevapps

Ken Sheridan
Stafford, England