Referencing another table via drop down

[dubmunkey]

Hi all,

have used the wizard to take values for a table dropdown field from another table

the table has

surname forename and class all of which reside in another table... ive tried to set up the bound columns so when you select a surname from the drop down the other two fileds also referencing that 6able are automatically filled in with their corresponding values.

so if the ref table had

smith john 1m

by selecting smith in the new tables drop down, the forename and class fields automatically select the corresponding values from the ref table..

im sure this is possible but cant get it to work and cant find anything on the web (although this is probably down to not knoing the right search string)

any ideas?

dubs

 

[Fifty2One]

you can do that with an update or append query

 

[dubmunkey]

ive seen it done in the past but dont have an example- it didnt involve and update query...

dubs

 

[KjWhal]

ive seen it done in the past but dont have an example- it didnt involve and update query...

dubs

Are we talking fields in a table being updated or fields on a form?

If its the former the other guy is correct in that you need an update query.

If its the latter you can use the OnClick event of the combobox to trigger an event that runs a query that selects the value you want from tableRef where <your condition is met>

 

[Bat17]

you don't want to do it anyway
look up 'normalization'
your lookup should just return the ID for the person, when you need the other details you just join the two table in a query.
That way when miss Smith get married and becomes Mrs Jones you don't have to find her in every table, just change her name in the main table and it will automatically be reflected everywhere she is listed.

Hope that makes sense

Peter