Cbo to do the same look-up but at different times

[stefan]

What I have is a database which stores all information for
research projects which we manage. Within our company, the
idea for a research project is generated from an
individual - the project sponsor.

The project is then subcontracted by a tendering process.
All research work completed by the chosen subcontractor
through to completion is stored on the database. When the
subcontractor's final report is approved, usually by the
project sponsor but not always, then the project is
completed.

I have a form (based on tblProject) with a cbo which looks-
up a table of individuals (tblsponsors) from within our
company. The user chooses from this table and the info is
recorded on the form.

I have another form for project completion (based on
tblProject) with a cbo which looks-up the same individuals
on tblsponsors - but I want to call these individuals the
project endorsers.

What I want to be able to do is include the info about the
sponsor when the project starts. When the project is
completed, I then want to include the info about the
project endorser.

Currently, when I enter the project sponsor, the project
endorser is also included, even though the project is not
completed and the endorser may be different to the
sponsor.

Maybe there is a better way of undertaking this task.
I'd be grateful for any advice
thanks

 

[John Vinson]

I have another form for project completion (based on
tblProject) with a cbo which looks-up the same individuals
on tblsponsors - but I want to call these individuals the
project endorsers.

You'll need another Table for these people. If you have a Many
(projects) to Many (endorsers) relationship - each project can have
multiple endorsers, and each employee can endorse multiple projects -
create a new Endorsers table with (at least) two fields: the ProjectID
and the EmployeeID.

This can't be handled using the very limited table lookup feature, but
it's easy on a Form; on your Projects form include a Subform based on
Endorsers. This subform would contain a Combo Box based on the
employee table to select the endorser for each row.