Populating Textboxes from Combobox selection 2

[Sloaner]

I have a form called MainSys which will have Name, Company & Address. The form's record source is a table with CompanyId(a number), LocationID(a number) and Contact(a text). I have Name as a combobox and would like to know how to get Company & address to be populated as Company Name and Address Location rather than ID when the user makes a selection in the combobox for Name.
The Company table contains CompanyID(primary key) and CompanyName.The Location table contains PersonID (primary key), CompanyId, LocationID and Name.
Can anyone help with the SQL needed to do that.

 

[formerTexan]

Sloaner,

To make your future life easier, now is a good time to do some archive searches under table design and normalization. Much has previously been said on the subjects, so I won't reiterate.

Your company table looks OK, but PersonID, CompanyId and Name don't seem to have any place in the Location table (should be LocationID and Location). Presumably you are trying to maintain Person information, so you should have a Person table. Ditto for Addresses.

Once your tables are in order SQL statements will develop in a much easier fashion.

Cheers,
Bill

 

[willir]

Two things about combo boxes...

The first is fairly standard.
Suppose You have a combo box for the LocationID. The RowSource is[tt]
SELECT LocationID, Addr1, Addr2, City FROM Location

LocationID is the bound field, and matches the ControlSource.

With the form open in design mode and the property window open (View -> Properties), select the combo box, and then select the "Format" tab. Set some of the fields to...
ColumnCount = 4 (LocationID, Addr1 & 2, City)
ColumnWidth = 0";1.5";0";1"
ListWidth = 2.5"

What happens...
0" column widths results in the column not being displayed.
You would only see the first address line and the city.

The next tip is that all four columns are available to you in the form...
Me.YourLocationComboBox.Column(0) = LocationID
Me.YourLocationComboBox.Column(1) = Addr1
Me.YourLocationComboBox.Column(2) = Addr2
Me.YourLocationComboBox.Column(3) = City

You can take advantage of this by displaying these varaibles to unbound text boxes on the form...

Me.YourUnboundField1 = Me.YourLocationComboBox.Column(3)
...to display the city.

Richard

 

[Sloaner]

Thank you former Texan... I'll be sure to check out the archives as I'm not done with this project.

Thanks Willir.. This was just what I needed for my example and applied them with ease.Again I thank you all.