Need help making search form to open a form with many subforms

[briandh]

Hi,
I have been working through this problem for a while and I have been looking through these forums for an answer and couldn't find one, so I thought I would post it:

I have a database with a list of people in it. In the main form, I have a subform for previous names (A woman who gets married 5 times will have 6 different last names and I don't want 6 different fields on my main form, so I use a subform for previous last names).
I currently have a separate form to search my main form using a filter (here is the code):

DoCmd.OpenForm "People_List",acNormal,"",[LastName] like '*" & Search_Last_Name & "*'"

Now this works great if I am searching the main form for a person's last name. I can type in whatever I want into the Search_Last_Name field, click search, and it brings up everyone with a CURRENT last name matching my text. However - I want to be able to search the subform "Other_Last_Names" at the same time, and show the main form.
For example: 2 records:
1. Jane Smith (Former last name Henry)
2. Jill Henry (No former last names)
So when I search for Henry, it should bring up Jane Smith's record, as well as Jill Henry's record.

Does anyone have any suggestions? I am really stumped on this one. I know how to do a search box on a form to accomplish this with the following code:

Me.RecordSource = "SELECT [People_List].* FROM [People_List] INNER JOIN [Other_Last_Names] ON [People_List].[ID] = [Other_Last_Names].[ID] WHERE [Other_Last_Names].[FormerName] LIKE '*" & txtFind & "*' OR [People_List].[LastName] like '*" & txtFind & "*'"

This will work if I have a textBox and search button on my main form, but the project specifies using a separate search form to search for records.

Can this be accomplished? Does anyone have advice? Any help would be much appreciated!! Thank you so much in advance!


Brian

 

[thornmastr]

It can be accomplished but before I go off on a methodology which will do you no good at all, please post the table structures for People_list and Other_Last_Names.

Robert Berman

 

[briandh]

I am brand new, so I am not to sure what you mean by table structures, so I will give you this, and if you need different, please let me know....
--------------Tables------------
Table People_List
ID
First Name
Last Name
Address
City
State
Zip Code

Table Other_Last_Names
ID (Exact same ID as in People_List)
Former Name
Date Changed
-------------Relationships----------
ID fields are linked as 1 to many
---------------Form-----------------
In my form, all of the main info from People_List are included as fields, and at the bottom there is a subform that contains the Other_Last_Names table. It works perfectly - You can keep adding to the former names in the subform and it will link the record up with the ID corresponding to it's owner in People_List

Hope this helps!

By the way - I am using Access 2000

Thank you very much!!

Brian

 

[thornmastr]

This is exactly what I need. Now I need some time to puzzle out a good explanation for you. If you don't get it tonight, you will tomorrow,

Robert Berman

 

[briandh]

Thank you so much Robert - Like I said, I've struggled with this one for a while and I am stumped. Any extra tips or help would be awesome!

Thank you very much!

Brian

 

[thornmastr]

What you are attempting to do should be very easy, and with a correctly designed database it is. I am not saying your design is bad, I am saying you have to extend your table designs further. The basic rule of thumb is your table design is not complete until it satisfies all possible searches of which you can conceive. If you have the interest pick up a copy of Database Design for mere Mortals by Michael Hernandez which will give you a very good grasp of relational databases without inundating you with the mathematical intricacies of set theory.

The first thing I did was to get rid of your ID columns and substitute autonumbers for primary keys. I don’t want to have to be worried about relating given data to a primary key. Primary keys should have nothing to do with your data. We need them to get things. They don’t have to be based on reality. I ran all this through my ER program and I have, at the least an ER diagram which will show you the relationships between the entities of your tables and it might help you understand this paper a bit better. If you want the information, give me your email address and I will send it to you.

I prefix primary keys with “pk”. Your table structures now look like this:

PeopleList

pkPrimary autonumber
FirstName text
LastName text
Address text
City text
State text
Zipcode text

OtherLastNames

pkOther autonumber
FormerName text
DateChanged date

What about relationships between these two tables. There is a one to many relationship from PeopleList to OtherLastName . Not true, it is one to many. Consider Mary Stevens whose name changed from Jones in 2000, but Mary Stevens was Mary Smith until 1993.

OtherLastNames has a many to one relationship with People List until you take into account Mary Jones from Little Rock Arkansas and Mary Jones from San Antonio, Texas.
So you could be moving many to many. Tables do not lend well to many to many relationships. This is the type of relationship which causes questions like yours, which, by the way, is a very good question and, without further ado, the answer is yes.

But how do you form a worthwhile easy relationship between these two tables. We create what is called a juncture table. People tend to gag when they see juncture tables. A juncture table has an autonumber primary key only because database applications like primary keys. You will almost never use the primary key of a juncture table. All juncture tables have an equivalent structure. Ours looks like this:

tblJuncture

pkJunc autonumber
fkpkprimary long
fkpkOther

fk stands for foreign key. It is a key that has nothing to do with the record. It points to the primary key of another table. In this case, fkpkPrimary points to a record on the PeopleList table, and fkpkOther points to the primary key of the record on otherLastName which is related to the record pointed to by fkpkPrimary. Confused yet?
Take for example Susan Margolan who is on her first marriage, and whose other name is her maiden name, She has one and only one record on the juncture table with one forward key pointing to the one record on the people table and one forward key pointing to the one record on the other name table. That’s an easy but often happening example.
Now consider Francine Warpnaggle who has been married 5 times and has four name changes. So we have only one entry on the People table for Francine and four entries on the other name table, so therefore the juncture table will have four records for Francine.
All four records will have identical values for the fkpkPrimary because there is only one entry on the People table. And every value for fkpkother will be different reflecting each corresponding record on the Other table.

Now, how do we tie this spider web into your application? I an assuming your initial search form pops a combo box consisting of client names. You need to carry two other columns on that combo box, you need to include city to distinguish been Francine Warpnaggle in Toledo and Francine Warpnaggle in Akron. You also want to include pkPrimary in the combo query but you want to hide it from view. I assume you know how, if not, you have the seed of yet another post.

You will use that key to directly get to that record for editing if needed. If you need to get all previous names Francine has had. You take that pkPrimary and scan for all matches on tbl Juncture where pkPrimary = fkpkprimary on tblJuncture. For each match you get the other record where fkpkOther = pkOther on the OtherLastName table, and you have them all neatly displayed in a continuous subform (hint hint).

Now, you have to find everyone on the People table who had a name of Smith. This is the question you asked. You scan the Other Last Name table for the first Smith. You then use the pkother to find all matches to fkpkOther on tblJuncture. You then get the current name by using the juncture tables pkPrimary to read the people list record. Then you traverse the Other last name table until you are at the end and their are no more Smiths.

Whew. We’re done. Me, I am going to get a cigarette, and my dog, and we are going in the backyard so I can blow smoke and watch the dog chase squirrels from tree to tree.

Robert Berman
thornmstr@yahoo.com

 

[briandh]

Thank you very much Robert - this looks great - I am going to try it out right now!