Auto Number Index - Lost Records

  • Thread starter Thread starter Shadow
  • Start date Start date
S

Shadow

I have a table with 500 records and its primary key set to
auto number. Somehow, about 20 records got entered that
do no show up on the table so the records can not be
deleted. The blank records, with no primary key auto
number showing, show up on a query that ties several
tables together, but I can not edit the datasheet view of
this query.

When I try to enter a new record, the auto number field is
the next one shown on the table not the next one after all
the 'Hidden' ones on the query. When this new record is
saved I get the message this there is a duplicate primary
key number. This then puts another blank record in the
database.

I can't eliminate auto number because all the paper behind
the database is filed based on the auto number field.
deleting auto number and then re starting a new auto
number would probably foul up all the filing because over
100 records have been deleted from the database.

Help! How can I erase the invisible no data files.
 
I have a table with 500 records and its primary key set to
auto number. Somehow, about 20 records got entered that
do no show up on the table so the records can not be
deleted.
Click to expand...

You've got a very basic misconception on how autonumbers work.

An Autonumber will ALWAYS have gaps. When you delete a record, that
autonumber is left unused; if you even hit <Esc> in the course of
adding a record, no record is created but the autonumber that the
record would have gotten is skipped. Autonumbers are NOT suitable for
any use where the value has meaning!

The records cannot be deleted because the records DO NOT EXIST. They
never existed in the first place, or they have already been deleted.

I would suggest creating a new table with a Long Integer primary key,
and getting one of the many Custom Counter VBA routines to maintain
it.
 
Thanks for the prompt reply. I guess I didn't ask the
question too well. There weren't any 'lost records'. My
real problem was to get AutoNumber to restart in the
correct place. I did this using Help 'Change the starting
value of an incrementing AutoNumber Field'.

The records that were entered and didn't save were not in
the table but they were in the query. I couldnt' find
these records in any table referenced in the query. Just
where were they?

Thanks for the warning on AutoNumber. However, I really
don't care whether the numbers are continuous. I just
want an automatically generated unique number for the
primary key. The clerks can write the the AutoNumber on
the top of the original signed form to facilitate filing.
It would be nice if the numbers increase so there is some
general relevance between the number and the date of data
entry. I don't want the clerks to have to generate a
number and synchronize their activities as they are in two
buildings.

You suggested a 'Custom Counter VBA routine'. I didn't
find any good references in my Google search. Any
suggestion on where to get such a routine?.

Thanks Shadow
 
Shadow said:
Thanks for the prompt reply. I guess I didn't ask the
question too well. There weren't any 'lost records'. My
real problem was to get AutoNumber to restart in the
correct place. I did this using Help 'Change the starting
value of an incrementing AutoNumber Field'.

The records that were entered and didn't save were not in
the table but they were in the query. I couldnt' find
these records in any table referenced in the query. Just
where were they?
Click to expand...

Trust me, they are in the table. Queries cannot generate records out of thin air.
 
John
Here is the SQL for the query

SELECT tblRVData.Sequence, tblRVData.[RVLease#], tblRVData.
[Space Type], tblRVData.[Customer#], tblRVData.
[Space#_tRVD], [tblSpaceInfo{LU}].[Space#_tSI],
[tblSpaceInfo{LU}].SpaceSize_tSI, qryFindRent.AnnualRent,
tblRVData.FirstName, tblRVData.LastName, [tblSpaceInfo
{LU}].ElectServ_tSI, IIf(IsNumeric([Customer#]) Or
[FirstName]="PublicWorks","Yes","No") AS IsOwnerYN,
[tblSpaceInfo{LU}].SpaceLeased, tblRVData.[NO-MailAddr1],
tblRVData.[NO-MailAddr2], tblRVData.[NO-City], tblRVData.
[NO-State], tblRVData.[NO-Zip], tblRVData.[NO-
SunriverAddr], tblRVData.SunriverPhone,
tblRVData.OtherPhone, tblRVData.[E-Mail], tblRVData.
[DrivLics#], tblRVData.DrivLicsState, tblRVData.RVType,
tblRVData.RVMake, tblRVData.RVModel, tblRVData.[VehLics#],
tblRVData.VehLicsState, tblRVData.VehicleLength,
tblRVData.SpaceSizeReqt, tblRVData.ElectricReqt,
tblRVData.Lien, tblRVData.PermanantAssign,
tblRVData.LeasePaid, tblRVData.Notes,
tblRVData.WaitListDate, tblRVData.LeaseStartDate,
tblRVData.PropOwnerName, tblRVData.[PropOwner ID#],
[qryOwner+Property].tblArCust.Addr1 AS [LU-Addr1],
[qryOwner+Property].Addr2 AS [LU-Addr2],
[qryOwner+Property].[tblArCust.City] AS [LU-City],
[qryOwner+Property].tblArCust.Region AS [LU-State],
[qryOwner+Property].PostalCode AS [LU-Zip],
[qryOwner+Property].tblLotLanePropID.Addr1 AS [LU-
SRAddress], [qryOwner+Property].tblArShipTo.Region, DateAdd
("yyyy",Year(Date())-Year([LeaseStartDate])+1,
[LeaseStartDate]) AS RenewDate, tblRVData.LeaseCancelled,
tblRVData.LeaseCancelDate, tblRVData.[Credit$],
tblRVData.CreditPaid, tblRVData.FinalRent,
tblRVData.WarningFlag1
FROM ([tblSpaceInfo{LU}] LEFT JOIN qryFindRent ON
[tblSpaceInfo{LU}].[Space#_tSI] = qryFindRent.
[Space#_tSI]) LEFT JOIN (tblRVData LEFT JOIN
[qryOwner+Property] ON tblRVData.[Customer#] =
[qryOwner+Property].tblArCust.CustId) ON [tblSpaceInfo
{LU}].[Space#_tSI] = tblRVData.[Space#_tRVD]
ORDER BY tblRVData.[RVLease#] DESC;

There are 360 records in this query of which the first 29
do not have any of the tblRVData items except for default
values. When I open the table 'tblRVData' there are 331
records and no matter how I sort them I do not find
the 'blank' records.

After all of the above I have revised the query and
relationships and there are no longer the 'Lost' records
in the query. However, the RVLease#, which is the primary
key and is now a integer (not an AutoNumber), still isn't
working right as I continually get the error message that
the field can not contain a null value. I'll keep working
on it. Maybe in the morning I can sort all the changes
and problems out.

Thanks for the help

Shadow
 
Back
Top