Quick from question

[morlan]

Could somebody help me with my new form. I have completed most of it but just one bit I’m having trouble with.

Please view attached Access file for reference.

When a user selects a ‘SalesExec’ ie. ‘Julie Davenport’ the form should look at the ‘SalesExec’ table and see the Julie Devenport is from the ‘Teesside’ Venue and the ‘Land side’ Pitch.
So when the form is completed and the user clicks ‘Add record’ the form will add the following the ‘MAIN’ table:

SalesExec: Julie Davenport
Venue: Teesside
Pitch: Landside
..along with all the other info.

Does this make any sense?

 

[Fizzio]

It does make sense but you may be storing duplicate data unneccesarily.
In your main table, you only really need to store the ID of the SalesExec as you can 'lookup' the rest of the information from the SalesExec Table.

If however, the sales rep can have many venues and many pitches then you will need to alter your data structure to reflect this but on first glance, the initial answer seems to apply.

Search the forums here for Normalisation or normalization or for posts by Pat Hartman for more info on how to 'normalise' your data

 

[jtvcs]

Morlan: Fizzio is correct relating to normalization of your database tables, but had a few minutes and decided to give you an example of how you could do what you want. The attached zip has two files (db4 is Access2000, while db3 is Access97 --- wasn't sure which version you had attached). A few things are different, look at the SalesExec combo box's properties including AfterUpdate. Added the two fields to your form but they could be 'hidden'. Also had to change the DataType in your MainTable for the two fields Venue and Pitch from Number to Text. Hope this helps and/or shows you something you can use in the future. JT

 

[morlan]

If I select 'Julie Davenport' then the database should automatically enter 'Teesside' and 'Land side' to the 'Venue' and 'Pitch' field into the 'MAIN' table for that record.

Storing duplicate data is not a concern.

Please, does anyone know how to do this?

 

[morlan]

Morlan: Fizzio is correct relating to normalization of your database tables, but had a few minutes and decided to give you an example of how you could do what you want. The attached zip has two files (db4 is Access2000, while db3 is Access97 --- wasn't sure which version you had attached). A few things are different, look at the SalesExec combo box's properties including AfterUpdate. Added the two fields to your form but they could be 'hidden'. Also had to change the DataType in your MainTable for the two fields Venue and Pitch from Number to Text. Hope this helps and/or shows you something you can use in the future. JT

Ill have a look at that now