Key Violation

[Robert Ott]

I'm creating an Append Query, to pull data from two
outside linked tables. My problem is that I keep getting
an error message that says we have a Key Violation.

The Key Violation apparently shows up because the key
field in the destination table has not yet been filled. So
here's the problem:

When we start a new Project I want to draw from the
Proposals database (with Client and Proposal tables) and
place what was once a Proposal into our active Projects
table. In order to do this the Project Number (key field)
must be assigned before the data comes over, or I get a
key violation.

Have tried several solutions, but still having problems.
Basically I need a prompt so we can assign a Project
Number, then import all the info from the Proposal.

Any suggestions?

 

[Robert]

Our Project Numbers, by convention, include year and
number, eg. 03-123. To allow this entry we have set up
this field as text.

While we could perhaps change this field to an autonumber
that generates incrementally off of the last in, I'm not
certain how to accomplish that given the format, and
moreover am not certain how that will impact
relationships, old data, etc. Needless to say, I hesitate
to take on those uncertainties.

I've tried setting up a dialog form that filters to the
select record and allows entry of the next project number,
but that is really not very sophisticated. This is also
making it difficult to do the whole operation without a
cumbersome series of macros.

It just seems that we should be able to:

.. Make up an append query with underlying Client and
Proposals tables (linked tables)

.. Have that query apply criteria to return the desired
Proposal record

.. (Here's the rub) Somehow run the append query while
requesting an entry for the destination key field

Not quite there yet.

Thanks:
Robert