Connections between forms and tables

[lqangel]

Hi Everyone,

I'm going to do my best to explain my situation. What I may be requesting might not be possible exactly, and if not, that's okay.

First off, here is the basic set up:
Form A
Form B
Table 1
Table 2
Field: Link

I have Form A that has text boxes with sources from Table 1. One of those sources is called Link. The Link field is a text box that you can enter a hyperlink in, and when you click on it your browser opens and takes you to the webpage entered.



This is all fine and works great, now what we WANT to do is have a button, or even just the same text box pulling into Form B from Form A.

The issue: Form B has text boxes with sources from Table 2.

Is there some way to create a Macro, or code, or button of some kind that when selected/clicked in Form B with locate the correct record and open the website entered in the text box in Form A?



We want to have them connected, in a way that we only have to edit in one place if there are changes. There are matching fields I can use in both to compare and make sure I am finding a matching record.
Form A lists companies, and Form B lists employees of the companies. To give you an idea of what I'm working with.



Thank you in advance for any help you can provide,
Ashley

[pbaldy]

You can certainly use DLookup() or a recordset to find the link field in one table presuming the key value is available. If you use a button, you can use FollowHyperlink and the result of either.

[lqangel]

I believe FollowHyperlink would only allow me to use one link, and it varies per record. Can you explain how to create the DLookup() formula? That sounds promising.

Sample Data:
Client: TomorrowLand Inc.
Contact: JM
Link: www.testing.com

I would need to match TWO sets of criteria, Client and Contact.

Would it read something like this?: DLookup(If [FormA]![Client]=[FormB]![Client] AND [FormA]![Contact]=[FormB]![Contact], [FormA]![Link],)

[pbaldy]

Here's more on DLookup() syntax:

DLookup Usage Samples

FollowHyperlink can use a variable address, so it could use the result of the DLookup()

[ssanfu]

You shouldn't need to use Dlookup() if your table structure is correct.

Form A lists companies, and Form B lists employees of the companies.

So that should mean that Table1 has the companies and Table2 has the employees.
1 company can have many employees and
1 employee works at 1 company.

There is a 1 to many relationship between companies and employees.

FormA displays companies so a little code can open the web page link.

FormB displays employees. FormB should have a query as the record source that joins companies and employees. So a little code can open the web page link.