Hi,
I am working on a database for registering our companie's stakeholders. It has a number of tables, and I want to make it as easy as possible for the user to enter information quickly.
When registering contacts, we naturally register their address. However, we also have a list of companies to which we also register the address. When creating a new vendor, I would like to add a button to which is tied a macro that automatically populates the HQ address of the selected company to the contact address. - This is because most, but not all, contacts are located at the company HQ address.
However, I cannot figure out how to get the macro to lookup the relevant information in another table. Can anybody help me out?
FYI: I have one table for the companies named "Companies" - with a unique key field called "Tier 2 CompanyID" - The field I am trying to copy from this table is the "Company Country" (There are others of course, but if I can crack this one, I can figure out the others as well).
The Form I am trying to fill out is called "New Contacts" (based on the table "Contacts") and the value I want to look up is entered in the field "Company" (it is a lookup field so the referential integrity is maintained). I want to populate the field in the form called "Country/Region".
Can anybody help me with the macro? Or should I even consider going down a completely different path?
Ps:
I have tried building a macro with a messageBox - just a warning - but then used:
SetLocalVar
Name Form![Country/Region]
Expression =DlookUp("[Companies]![Company Country]";"Companies";"[Tier 2 CompanyID] =Form![Company]")
I am working on a database for registering our companie's stakeholders. It has a number of tables, and I want to make it as easy as possible for the user to enter information quickly.
When registering contacts, we naturally register their address. However, we also have a list of companies to which we also register the address. When creating a new vendor, I would like to add a button to which is tied a macro that automatically populates the HQ address of the selected company to the contact address. - This is because most, but not all, contacts are located at the company HQ address.
However, I cannot figure out how to get the macro to lookup the relevant information in another table. Can anybody help me out?
FYI: I have one table for the companies named "Companies" - with a unique key field called "Tier 2 CompanyID" - The field I am trying to copy from this table is the "Company Country" (There are others of course, but if I can crack this one, I can figure out the others as well).
The Form I am trying to fill out is called "New Contacts" (based on the table "Contacts") and the value I want to look up is entered in the field "Company" (it is a lookup field so the referential integrity is maintained). I want to populate the field in the form called "Country/Region".
Can anybody help me with the macro? Or should I even consider going down a completely different path?
Ps:
I have tried building a macro with a messageBox - just a warning - but then used:
SetLocalVar
Name Form![Country/Region]
Expression =DlookUp("[Companies]![Company Country]";"Companies";"[Tier 2 CompanyID] =Form![Company]")
