Combo auto fill in 1

[Bob500]

Hi,

I have a field on my main table that gets data from a lookup on another table. There are 3 fields in the lookup name, address and telephone number. How can I get the corresponding fields in my main table to automatically fill in with the related data, e.g I select the name and the address and telephone are filled in.


Thanks

 

[KenReay]

Hi

Your question sounds like you are entering data directly into your tables, this is not a good idea, you should use a form to do this. If you use a form to update your table, you can put code in the after update event of the name combo box to save the 'other' columns into the main table, using syntax like [Address] = cboName.Column(1)

Secondly, if the 'other columns' can be retrieved from the look up table via the name, then why do you want to store then in the 'main' table again?, you could just make a query and join the two tables, ( on name) and be able to 'see' all of the data you are talking of. There are circumstances under which you would want to save the address etc again, but not always.

Third, the choice of name as a key is not good, there is a high probability of duplicate names occuring in most situations.

Finally (for now) have you called you column within the table 'name' ?, this is not a good idea, Name is an Access reserved word, and while you can get away with using Reserved words as column names, it almost always ends with problems further down the line.

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[Bob500]

Hi,

I have put the 'select distinctrow' that includes all the fields I need, and put the '[Address] = Combo289.Column(3)' on the after update event. But it doesn't do anything.

I have also seen this:

Me!Address = Combo289.Column(3)

Does this relate to the textbox called address and [address] to the field address on the table?

Thanks

 

[KenReay]

Hi

Assuming [Address] is a bound control on the form, then Me!Address = Combo289.Column(3) will populate that control, and hence the table with the Address, PROVIDED that the third column of the combo box IS the address.

Combo box coluns are numbered 0,1,2 etc so the firat column is column 0, the second column 1 and so on.

You say "[Address] = Combo289.Column(3)' on the after update event. But it doesn't do anything.", have you tries tepping through the code in debug mode and checking A) taht is gets executed and B) if it does what the value of Comb289.Column(3) is?

Any comment on the other points I raised?



Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[Bob500]

Hi,

I got it to work but I havd to use:

=[Combo289].[Column](1)

on the control source of the textbox.

I should have mentioned that I have an autonumber Primary Key, and not the name as I suggested before, sorry.

I have taken on board your suggestions and will not repeat the data into the main table.

Thanks for all your help