Use a lookup field to influence values in another field

[nathanrt]

Hello,



Firstly, apologies if this isnt the right forum sub section...

I've been out of practise with using Access for a while, so this may seem a rookie question!

Basically, I'm looking to create a table that holds event information. One of the fields I've successfully included is a Lookup function to pull info from a contacts table. This table also has statistics in it.

What I'd like to do on the events table is lookup a contact name in one field, and then in another field within the events table, pull over the relevant statistics based on my selection.

Now, I've been able to get the lookup to pull across all of this already, but as far as I can tell, you're only able have one column displayed (bound) in the lookup - is this right?

If you've any suggestions I'd love to hear them. Please ask if you need any more info.

Thanks in advance!

[ConneXionLost]

Hi nathanrt,

Yes. The reason being, you don't want to store additional/duplicated information in the "table" because that would go against the rules of normalizing.

What you want is to show the statistical information in a "form", or better yet, a "report". These items are meant for displaying data far better than a table.

Cheers,

[nathanrt]

Hi ConneXionLost
Thanks for the response.

I knew I forgot something off that post and that was that I wanted this all to happen on a form (so when data is looked up in one box another box is populated from related data in another table).

Having done a quick Google, am I right in saying I need to use the 'After Update' function (Can't remember if that's the right term or not) combined with another lookup or query in order to select and display the related data?

[SteveF]

Hi ConneXionLost
Thanks for the response.

I knew I forgot something off that post and that was that I wanted this all to happen on a form (so when data is looked up in one box another box is populated from related data in another table).

Having done a quick Google, am I right in saying I need to use the 'After Update' function (Can't remember if that's the right term or not) combined with another lookup or query in order to select and display the related data?

Yes, the AfterUpdate event is usually used for this kind of thing.

However, I want to reinforce something from the first reply: DON'T USE LOOKUP FIELDS! IMO they were a horrible idea, never should have been introduced in the first place, cause much more trouble than they're worth in the long run.

If you need to do lookups (and I do them all the time), use a list box or combo box on a form to look for something in one table & populate a column elsewhere. When you need to show the looked-up value as a static item on a form or report, use a query to join the master & lookup tables together so that you have the looked-up value available without corrupting your underlying table structure.

Steve