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.

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


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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question