Linking result from lookup colum in a form to the values form

[premis]

I'm going to see if I can explain this very well.



I am creating a customer database for an independent insurance agency. Within that customer database, we have a lookup field that references another table "Carriers". This is where we select the insurance carrier we have placed the clients insurance with (ex. Geico, progressive, etc). This field allows multiple entries since clients can have policies with different companies.

We also have contact detail forms for each of our insurance carriers where we store information like web logins, contact phone numbers, and other notes. These forms are based on the same table as the lookup field.

I would like to create a hyperlink so, if we are looking at a clients form with all of their contact info we can click the insurance company in the carrier field and it will bring us to the contact details form of that carrier. Is this possible. An image is attached to try to explain it visually.

Any help would be greatly appreciated.

[pbaldy]

Was my answer on your other thread not applicable?

[premis]

Was my answer on your other thread not applicable?

I guess I'm unsure if it would work in this situation. Again, I'm an amateur, so I may just not be picking up on how I can adapt it to this. I'm guessing I could do an "on click" event and have the macro open the form referencing the field I'm clicking...? But then what do I do when the field has multiple values?

[pbaldy]

Have you tried it? Why speculate on whether or not it will work when it's so easy to try?

[premis]

Have you tried it? Why speculate on whether or not it will work when it's so easy to try?

I tried the code for this too, I couldn't get it to work. I've decided to just concentrate on my other issues and wait on this for another day. It's not nearly as important.

Thanks,

[premis]

Hey pbaldy

After you did an awesome job teaching me on my other problem, I felt I could now tackle this again. I am SO close, I hope you can help me.

I used the macro builder, and your code tips, to create a double click event for the field on my contact details form (called "Client Details") so when clicked, it opens the form "Carriers Details" and shows the record of the insurance carrier listed on the contact details. I used the following

Action:OpenForm
Arguments: Carriers Details, Form, , "[Carriers].[Company] = '" & [Me].[Carrier] & "'", , Normal

It almost seems to work. When I open the form that contains the code I get a warning box that says "Procedure delaration does not match description of event or procedure having the same name" I click "ok" and the form opens. Then, when I double click the field I placed that macro in a dialog box comes up with the title "Enter Parameters" on the heading. In the body it says "Me.Carrier" and then a input box I can put something in. If I type an insurance company, for example "Allied" in that box the macro will complete and do what I wanted the code to do!

So, is there a fix I can make to this that will make it run with out the prompt boxes?

Thanks!

[pbaldy]

I don't use macros, but "Me" is only valid in VBA code, so you need the full form reference there. I don't think you have to do the concatenation in macros, but check in help and make sure of the syntax.

[premis]

I don't use macros, but "Me" is only valid in VBA code, so you need the full form reference there. I don't think you have to do the concatenation in macros, but check in help and make sure of the syntax.

Well, I can't seem to get it. I think I'm going to pass on that function for now.

Thanks again for the help!