Out of Sync Autonumber

I have several tables that use Autonumber as the PK

ContractNum Table
Autonumber PK
ContractNum
ContractTitle
ContractComments

VendorNameTable
Autonumber PK
CompanyName
CompanyAddress
CompanyCity

VendorFeeTable
VendorID PK
FeeNum PK
FeeAmount
FeeAddAmount

FeeNumTable
FeeNum PK
FeePhrase
FeeDiscription

when I delete a contractnum or Vendor
the next new contractnum or new vendor
is out of sync and retreives information
from another contractnum or vendor

The relationship is 1:Many for Contractnum & Vendor
and 1:Many for FeeAmount & FeeNum tables

The VendorFeeTable is the junction to the Many:Many

On Wed, 30 Jun 2004 07:00:09 -0700, "Linda"
wrote:


when I delete a contractnum or Vendor
the next new contractnum or new vendor
is out of sync and retreives information
from another contractnum or vendor

The relationship is 1:Many for Contractnum & Vendor
and 1:Many for FeeAmount & FeeNum tables

The VendorFeeTable is the junction to the Many:Many

You're misunderstanding how autonumbers work. An Autonumber should
NEVER be used as a foreign key (a linking field in a related table);
*only* as a primary key. If you have an autonumber VendorID in
Vendors, your related tables (VendorFeeTable) should have a Long
Integer VendorID, *NOT* an autonumber. It would be filled in using a
form/subform arrangement.


John W. Vinson[MVP]
Come for live chats every Tuesday and Thursday
http://go.compuserve.com/msdevapps?loc=us&access=public