Combo box used to update other fields in form

[What Name??]

Hi all

I have a form which is based on a table called Investor. It has a combo box with a list of companies filled from the field InvestorCompany. I would like the user to be able to select one of the companies and when they do so for their details such as AddrLine1, etc to be automatically filled from the Investor table . If they do not select an existing company they should be able to input new details into a blank form.

I think this should be done by code written into the After Update Event. But does anyone know where I can find the formula or examples for bringing the record contents for a particular record over and auto filling several fields of a form based on one field?

Thanks in advance for any help.

 

[HAMMAMABUARQOUB]

Hello "What name?"
try this,,,
1- in the after update event of the combo write this code "example"

Me.Adres=Me.Company.Column(1)
Me.Manager=Me.Company.Column(2)

and so on...
notes...
adre and Manger are the fields to be filled or updated..
company is the name of the combo box containing companies..
Column(1) and (2) are properties that can bring the value in specific fields, from the combo box data sourse matching the selected company...
Regarding the new data form.. try one of the following,,
1-Put a value on the jhead of the combo data .. let it be "AddNew"
create an If statement asking about the value selcted combo box
if it's "AddNew" then trigger the code to open the new form
2-Add a command button in the same form.. just clickin on it opens the data form..
3-or in the OnExit() event of the combo if the values is null "no value selected" write a code to open the form...
goodLuck
HAMMAM

 

[David R]

Also, please be aware that you are duplicating data, perhaps needlessly. Ask yourself these questions:

• Do my users need to be able to customize the address bar beyond what is given by the combobox?
• Do I need to have the ability to update addresses for companies easily and flawlessly?
• Is it important to me to have old address information kept in the records, even if I update the company info later?

If 1 is NO and 2 is YES, I strongly suggest you use the current Company table as a lookup and just display the address information. If 1 is YES, then you're probably doing it right (though that might be solvable by nested comboboxes, if the set of possible 'right' addresses is finite). 3 is solvable no matter what the answer, it just means archiving old Company entries when they change their address.