Enter data in multiple fields based on lookup?

  • Thread starter Thread starter Jim Dudley
  • Start date Start date
J

Jim Dudley

I would like to know how to find information that would allow multiple fields
to be automatically filled in based on the value input into a field, when
creating a new record.
e.g. I enter a Acct Number into the Account Field of a new record and the
subsequent Name, address, phone number etc. automatically fills in based on
data stored in another table. (Account Holder Data). Any suggestions would be
appreciated.
 
You will need dlookup

ie on after update
me!name=dlookup("[name]","account holder
data","accountnumber=forms!nameofform!accountnumber")

me!address=dlookup("[address]","account holder
data","accountnumber=forms!nameofform!accountnumber")

Not sure if you know but try and make sure controls and tables have no
spaces in them when using VBA.

Hope that is of some help
 
Thanks BlackHarry,

I would assume that the subsequent DLookup expr would go in the blank fields
you want filled in.
e.g. in the [Last_Name] Property Sheet under Event, "After Update", you
select "Event Proceedute" and in the "expression builder" enter your DLookup
expr.
You would repeat this process for each field you want filled in.

--
Thanks in advance...

Jim


BlackHarry said:
You will need dlookup

ie on after update
me!name=dlookup("[name]","account holder
data","accountnumber=forms!nameofform!accountnumber")

me!address=dlookup("[address]","account holder
data","accountnumber=forms!nameofform!accountnumber")

Not sure if you know but try and make sure controls and tables have no
spaces in them when using VBA.

Hope that is of some help

Jim Dudley said:
I would like to know how to find information that would allow multiple fields
to be automatically filled in based on the value input into a field, when
creating a new record.
e.g. I enter a Acct Number into the Account Field of a new record and the
subsequent Name, address, phone number etc. automatically fills in based on
data stored in another table. (Account Holder Data). Any suggestions would be
appreciated.
Click to expand...
Click to expand...
 
<<I enter a Acct Number into the Account Field of a new record and the
subsequent Name, address, phone number etc. automatically fills in based on
data stored in another table.
Are you wanting to store the same data in two tables? Wrong thing to do.
In your query interfacing to the new record table, left join to table the
has Name, address, phone number etc.
 
Thank you Karl, I will give this a try. I understand that I do not want data
stored in more than one place. Causes things to get out of hand eventually
and makes maintenance a nightmare.
JD
 
Back
Top