# Solved: Access 2007 Create Search Box in Form for Subform



## melonhead (May 6, 2002)

Access 2007

I have a form and a subform. I would like to create a text box in the form that I can search in the subform.

Form Name: FamilyNameForm
Subform Name: PatientNameForm
Field I want to search in Subform: LastNameFM

Any ideas how to create this?


----------



## OBP (Mar 8, 2005)

There are a number of ways of searching, but the best way is to use a Combo box unless you want to list a lot of similar names. The combo avoids typing and spelling errors that can prevent someone finding the person they are looking for.
If you really need the free text box version though you use Visual Basic to set the Sub form's Filter to the name entered in the search field. 
I can't help you with 2007 as I only have access 2002, but the databases that I have posted on here already can easily be converted to 2007.


----------



## melonhead (May 6, 2002)

Thanks for the reply. I've tried a combo box - but it doesn't go to that record in the main form when it finds the value in the subform. 

This is how I'm using it:

The main form: FamilyNameForm - includes all the shared info about a family, ie. address, home phone, etc.

The subform: PatientNameForm - includes all the personal individual about each family member, ie cell phone, insurance, etc. However, sometimes the wife may have kept her maiden name or the children may have a different last name due to divorce, so searching on the main form for the last name doesn't bring up their record.

The combo box will search for the last name in the subform and it will bring up the correct information, but it will not move the main form (familynameform) to the record that the combo box has found.

Thanks in advance.


----------



## OBP (Mar 8, 2005)

What links the 2 Tables and Forms the Family name ID Field?
If so you can add some VBA to the Combo that will find the matching record on the main form.


----------



## melonhead (May 6, 2002)

The forms are linked by FamilyID and PatientID. I originally based the combo box on the PatientName table. However, it would always place the PatientID in the far left corner since it is the primary key. (I tried moving it to another column and it kept defaulting back.) That way you can't type in the first couple of letters of the last name and move the combo box to that letter so it wasn't desirable.

I tried making a query so that I could have both these ID numbers included. That combo box still did not pull up the present record. It "refreshes" the form and brings it the first record in the alphabet.

I saw one post that this occurred because they had all the fields in the form header. The fields in my form are in the details section.

Any other suggestions?


----------



## OBP (Mar 8, 2005)

Why do you have 2 links?
What is the Relationship between the 2 Tables?
Can you take a copy of the database, delete all the records in the copy and then save it as an Access 2000 database?


----------



## melonhead (May 6, 2002)

Here it is.


----------



## OBP (Mar 8, 2005)

Afraid not?
There is no attachment.


----------



## melonhead (May 6, 2002)

For some reason it keeps giving me upload errors. States it is not a valid file. I did convert it to 2003 format


----------



## OBP (Mar 8, 2005)

The site only accepts zipped access databases.


----------



## melonhead (May 6, 2002)

No wonder! Thanks for that info. Hopefully it is now attached!


----------



## OBP (Mar 8, 2005)

melonhead, you have some very neat code in your database, the naming of the Tabs to the Patient's name is particularly good.
I have the Patient's Combo selecting the correct Family and subform, but I do not know how to get it to select the correct tab to go with the patient's first name.

I deleted the non existent sub/sub forms from the sub form to get rid of all the error messages.


----------



## melonhead (May 6, 2002)

Thank you so much! You're awesome! 

It doesn't have to open to the individual - as long as it pulls up the family, that is great!

I didn't include the forms and tables that you got error messages for. Sorry about that!

Thanks for the input! I'm not very experienced and have learned all by trial and error. I am putting this database together for my husband's office. 

I may be contacting you again! I'm moving on to billing and that is always a challenge.

Thanks! Margie


----------



## OBP (Mar 8, 2005)

Margie, where to you get the VBA code to set the Tab names, that is something that I have never seen done before, is it an Access 2007 feature?
Are you actually going to use the Search form as that is also quite advanced VBA, that one have seen before.


----------



## melonhead (May 6, 2002)

I'm not exactly sure where I got the tab names. I did that for another database that I made about a year ago and then used the same thing. As I remember it is kind of a combination of some forms that I experimented with. I do a lot of hit and miss. 

The search form I downloaded off the internet. When I was frustrated with the original problem of the search from the form/subform, I was researching options and came across that. Even though I did not want to use it for the form/subform search, I thought it was something I might definitely want to use. 

Thanks again for your help!


----------

