Search forms - searching for a contact and viewing all related dat

[Nicola]

Hi

I am pulling my hair out with what I am sure is a simple task, creating a
search form that, when criteria are entered, finds a record and displays
related data/results from 3 tables. I have tried every forum and web post I
can find but I think there must be something fundamental I'm neglecting to
grasp.

Quick background:
- My database has 4 tables, Firm, Contacts, Mailout and FileNote
- The database is contact-centric (ie, everything is linked to a contact
record, multiple contacts are held against firms, mutiple file notes are held
against a contact, contacts can be attached to multiple mailouts)
- I need to search for a contact record based on multiple criteria (keeping
it simple, lets say a combination of first_name & last_name OR first-name &
firm_name OR their specific contact ID)
- I need a 'results form' (not just a datasheet view) that displays all
information related to that contact (i.e. all files notes + ability to create
new ones, firm contact information, and mailout history)

I've created a Main form with Firm, Mailout, Contact & Filenote subforms
embedded that enables me to scroll through every contact and view all related
detail (as above) and add file notes very nicely. My problem is I can't
search, I can only move through each record sequentially. Very handy when we
have 4000 contacts!

I realise I'm probably a complete wally but could someone please explain to
me the vital steps I'm missing? I've tried unbound fields, I've built SQL
queries & command buttons (but results are dumped into a datasheet)....I'm
lost.

Many many thanks.

Nicola

 

[melwester]

Put in a command button to do a find record. When you run the form you
click on the find button type in what you are looking for.

 

[FA]

You will need the following:

Main form with all Unbound COntrols

You will need a form (for example Contact Info) which has all the
fields that you want to search and that form will be based on a Query
such as

SELECT Contact.FirstName,Contact.LastName ,,,,, so on, IIf(Not
IsNull([ContactID]),"Edit","") AS Edit, * FROM Contact;
Note: Edit can be a hyperlink which can lead you to any form where user
can edit the data. you just need to set a macro in Edit OnClick
properties to open a form where ="[Contact_ID]=" & [Contact_ID]

On you Main Form do the following
Make an Unbound Textbox and name it FirstName

Make a Command button name it "Search" on Main Form and go in the
properties and in the Event Procedure try the following codes:


Private Sub Search_Click()


Dim strWhere As String
Dim strError As String
Dim ContactInfo As [Form_Contact Info]

strWhere = "1=1"

If Nz(Me.FirstName) <> "" Then
' Add it to the predicate - match on leading characters
strWhere = strWhere & " AND " & "Contact.FirstName Like '%*" &
Me.FirstName & "*'"


If strError <> "" Then
MsgBox strError
Else
DoCmd.OpenForm "Contact Info", acNormal, strWhere, acFormEdit,
acWindowNormal

[Form_Contact Info].Form.Filter = strWhere
[Form_Contact Info].Form.FilterOn = True
End If
End If
End Sub

Good Luck