update 2 fields with 1 combo box

[staines]

Hi,
Hope someone can help. What I’m trying to do is have a combo box look up values from one table and add them to another. I have used the combo box wizard to look up a value from table1 and add it to table2 but is there a way that it can lookup 2 values and add them to the second table. For instance if I had to tables like :-

Table1
First Name
Last Name

Table2
First Name
Last Name

Can a combo box lookup and update both fields for example if I have a combo looking up the first name can it also lookup the last name and add them to the corresponding fields in the second table but with the user only selecting the first name in the combo box.

I’m sorry if I don’t make much sense I’m new to access and fairly new to forums

Thanks for your help

 

[ScottGem]

Whether you can do this is not the issue, its whether you should do this and the answer is no.

The idea of using a relation database is to reduce or eliminate redundant data. Storing the first and last names of a person in 2 tables creates redundant data. What you should be have is one table with with info about people including their names. Each person would be identified by a unique ID (preferably an autonumber) known as a primary key. In any other table that you want to associate this person with you would store that value and only that value. This is known as a foreign key.

When you use the wizard to create a combobox, it will include the primary key value by default and set it so that's what's stored.

To get the name info, you just join the tables on the key field in a query.

For names, I generally use a RowSource like the following:

SELECT PersonID, LastName & ", " & Firstname AS Fullname FROM table ORDER BY Lastname, Firstname,

I then set the Cound Column to 1, the Coloumn Count to 2 and the Column widths to 0";2". This allows you to select from and display the names in the combo but store the ID for use in joins.