Search or filter form and subform

[Jinny]

Hello,

I am using Access 2007 with requirements that the file be compatible with
2003. I am looking to text search or filter data in my form and subform (if
a record matches from the subform table, the parent form containing that
record appears). This is how my database is set up:

Three tables:
Clients -- records of companies, Primary key: ClientID
Contacts -- records of individual people, Primary key: ContactID, linked to
Clients by ClientID
History -- records of instances of communication, no primary key, linked to
Contacts by ContactID

Three Forms:
Home - shows company information by individual record
Contacts - a subform datasheet, listing each person who is linked to the
current company record
History - a subsubform datasheet, listing each instance of communication
linked to the current contact record

The main function of the database is to be able to find a company or contact
and see how I have communicated with them and add new communications. I want
to search by "TargetClient" (the company name), LastName, or FirstName. The
search cannot be a combo box--there are too many records and common names.

Do you think I should construct my database in a different way? Is this
even possible? I have spent almost a full week on this problem using trial
and error and searching resources on the internet. I realize that similar
questions have been asked before, but I am not very adept with visual basic,
and I have been unsuccessful at trying to tailor other solutions to my own
forms.

Thank you so much for your patience and help.

 

[Allen Browne]

So you want to provide a text box where the user can enter part of a name,
and the filter the main form so it contains only those companies whose name
matches, or those companies who have a related contact whose name matches.

You could use a subquery in the form's filter. Something like this:

Dim strWhere As String
strWhere = "(CompanyName Like ""*" & Me.txtName & "*"") OR (CompanyID IN
(SELECT CompanyID FROM Contacts WHERE Contacts.Surname Like ""*" &
Me.txtName & "*"" OR Contacts.FirstName Like ""*" & Me.txtName & "*""))"
If Me.Dirty Then Me.Dirty = False 'save any edits
Me.filter strWhere
Me.filteron = true

If subqueries are new, here's an introduction:
http://allenbrowne.com/subquery-01.html

The structure you have makes sense if all your clients are companies, i.e.
you never sell to sole traders or individual persons. If you might need to
respond to persons in their own right (not as part of a company), you could
get away with a null foreign key (ClientID is null.) But you may find that
you need a foreign key that could relate to either a company or a contact
(e.g. taking an order or making a payment.)

For those cases, you would be better off with a structure where the persons
and companies are all in the one table. For an example of how to do that,
see:
People in households and companies - Modeling human relationships
at:
http://allenbrowne.com/AppHuman.html

 

[Jinny]

Allen,

Thank you so much. The subquery worked very well--brilliant. I really
appreciate that you took the time to answer!

For folks with the same question, here is the code (the name of the text box
is "Search"):

Dim strWhere As String

strWhere = "(CompanyName Like ""*" & Me.Search & "*"") OR (CompanyID IN
(SELECT CompanyID FROM Contacts WHERE Contacts.LastName Like ""*" & Me.Search
& "*"" OR Contacts.FirstName Like ""*" & Me.Search & "*""))"

If Me.Dirty Then Me.Dirty = False 'save any edits

Me.Filter = strWhere

Me.FilterOn = True