Displaying related records in a subform ...

[Mikey C]

Hi all

Hopefully I can explain this in enough detail to make sense!

I am creating a contact database and am just starting to create the
main form. On the main form it shows a particular contacts details (eg
name, phone, email etc), then I have a number of subforms which
display their company's contact address, notice address, and the name
of the technical manager in charge of that particular client.

Now ... what i would like to do is create another subform which will
display the details of all other contacts (for the particular company
I am currently viewing) and even better, the ability to link to the
other contacts full details via a hyperlink (if possible).

Any suggestions?

 

[kingston via AccessMonster.com]

Create the second subform the same way, except specify company as the
master/child link. However, this will show all contacts, including the one
in the main form.

To do exactly what you describe, you'll have to do a little more work:
Create a query on the contacts with the conditions (Company=[Forms]![MainForm]
![Company] and Contact<>[Forms]![MainForm]![Contact]). Then use this query
as the record source for the second subform. Use the VB Requery command to
update this second subform whenever the user navigates to a different record
(OnCurrent event) and whenever the user changes the Company value
(AfterUpdate event). Now, to create "hyperlinks", use the second subform's
contact control to go to a specific record using its OnClick event. It's not
clear whether you wanted to go to the contact via the main form or a new form.
hth

 

[Mikey C]

Brilliant - thanks for the help!

 

[Mikey C]

Hi again

Just been having a go but I'm stumbling already! For starters I don't
understand how to set the GoToRecord setting take me to the correct
record (clicking on the contact name to show that particular record)
and I would like the full details of this contact to display in the
main form.

Thanks again

Mike

 

[kingston via AccessMonster.com]

Try something like this to get to the record on the main form:

DoCmd.OpenForm "MainForm"
DoCmd.GoToControl "FieldName"
DoCmd.FindRecord Me.ContactName