Autonumbering problem!!! Help!!!

[Vandelay]

I have a database and I recently ran the 'compact and repair' and now one table (form) when it tries to auto number... it starts at 1, creatinga duplicate record instead of the next number 726...

what can I do?

 

[mph1]

In your table design view, the field name whose data type is autonumber should have the field properties of that type set to NO DUPLICATES. If this is the case, then you will not have any autonumber duplicates. You might want to look at the following FAQ702-5106 where there is a description of what the autonumber's purpose is. If you are attempting to use something such as an invoice, check etc number, then the autonumber is NOT what you want to use.

An investment in knowledge always pays the best dividends.
by Benjamin Franklin
Autonumber Description - FAQ702-5106

 

[Vandelay]

Okay, I understand now, after having read that FAQ... that the autonumbers purpose is not being used correctly by me..... how would I dig myself out of this hole when every table is currently using the auto numbering system that way? I can't just start over...

I guess I'm in big trouble this time, aren't I?

Any suggestions?

 

[Vandelay]

I was reading over the FAQ again... how do I create a sequenced number, that cannot be duplicated, but has to be manipulated by me?


A button maybe that would 'add' the sequenced number after the user has finished the record?

I'm a little unclear on this one.

 

[BobJacksonNCI]

If you made a backup before compact/repair, you should restore that...

It's not clear what you're using the autonumber for.
I.E., Access makes sure each is unique so you don't need to know what it is. And it's simple to write a count query.

So, unless you're creating numbered documents (e.g., invoices) why do you care what the numbers are?



HTH,
Bob

 

[mph1]

Try the following (concept)--

Nz(DMax("[FieldName]", "[TableName]") + 1, 1) --- this could be put into a module so all forms/tables would have this available.

This is the concept. Nz will convert all null fields. Look up Nz and DMax in help for a more detailed explanation.


An investment in knowledge always pays the best dividends.
by Benjamin Franklin
Autonumber Description - FAQ702-5106

 

[Vandelay]

I am using the autonumber (incorrectly, as it were) as my invoice or "unique number"....

 

[Vandelay]

mph1....

I tried the Nz(DMax("[FieldName]", "[TableName]") + 1, 1), it says there is a "=" missing...

I'm not very familiar with modules, so please be patient...
I don't know what to do with the mod.

 

[Vandelay]

Okay, So I figured that the best workaround solution is to create a new table with a new numbering system. Have the autonumbering still work in the background, but then at the same time have a manual numbering system. I thought of going with a programmable (previous record +1) but the problem there is that what if they delete a record or start a record and then discover they don't need that one... how would the " + 1 " system work then?

In the end I think it would be easier to have the user manual reference the previous records' number and then manually put in the next record number... this way if someone accidentally starts a new record, it's okay, because you can 'throw that one away' and use the next number in sequence..... no 'accidentally' deleted numbers.

What do you think mph1? Is this viable... easy enough...?

Of course you keep autonumber in back ground for primary key... the only downfall is user error... they forget to reference previous number.

 

[mph1]

Sorry I wasn't able to get back sooner, but, something came up.
It seems as though you have been using your db for some time and have data entered. To say what you should have done, at this point, is somewhat unessary. I don't have time right now to help you out, but will try and work on a solution this evening (EST).
Before you do anything ------

BACKUP

Once you have a copy, you can then work on it. Can you give me an idea of how many records you currently have??

Maurie

An investment in knowledge always pays the best dividends.
by Benjamin Franklin
Autonumber Description - FAQ702-5106

 

[Lightning]

Here is some code to generate the Next number in a sequence. Thanks to Gordon Hubbell

[COLOR=green]'Incremented Number field NOT using AutoNumber
'Tek-Tips Forum 5/3/2001
' from GHubbell

'Run a table called tblDocType.  Includes fields called
'RefType: AutoNumber  Description: Text   Counter: Number  UseCounter: True/False
'(Set "True" for form required)

'On Forms ADDNEWRECORD procedure, add this code

'DoCmd.GoToRecord,,acNewRec
'Me![Name of Field] = NextNumber(1)     'where 1 is the doctype number for this type of document
[/color]

Public Function NextNumber(RefType As Variant) As Variant
On Error GoTo ErrNN
Dim SQL1 As String
Dim Rs As Recordset
Dim DB As Database

    Set DB = CurrentDb()
    SQL1 = "SELECT tblDocType.* FROM tblDocType WHERE "
    SQL1 = SQL1 & "(((tblDocType.RefType)= " & RefType & ") AND "
    SQL1 = SQL1 & "((tblDocType.UseCounter)=True))"
    Set Rs = DB.OpenRecordset(SQL1, dbOpenDynaset)
    If Rs.RecordCount = 0 Then
        Rs.Close
        GoTo ErrNN
    End If
    
    NextNumber = Rs!Counter + 1
    Rs.Edit
    Rs!Counter = NextNumber
    Rs.Update
    Rs.Close
    
ExitNN:
    Exit Function
    
ErrNN:
    MsgBox Err.Number & " " & Err.Description, vbInformation, "Next Document Number Generation Error!"
    Resume ExitNN
    
End Function

I can't remember the thread number, but a search on Autonumber should find it.

HTH
Lightning

 

[Vandelay]

Maurie,

I have about 8 different tables. The big one currently has 35,000 records, but that one doesn't matter because the issue of missing records, deleted records is not that important.

The other ones are. They currently have about 730 in one, 512 in another, 179 in another, and 120 in the other. The first two will grow to 1,000 and 2,000 respectively.. but this database will end at the end of the year and we'll start over with a new one in 2005.

I actually backed it up yesterday, but this was, unfortunately, after I ran the compact and repair which I think caused most of this problem. One other thing. It was working fine yesterday afternoon, until apparently two users were trying to edit the exact same record and then the system crashed. This is when I was first alerted to any problem.

Thanks.

If you want, I can contact you via phone or e-mail.

Thanks a lot mph1.... Vandy.

 

[Vandelay]

As for now, I am going to change the table and add a field for a manual invoice number. To do this I will simply create a new database and import all the old data, then add the field.. .I'll have to modify the form a little for the manual input.. and then train the staff, but I think it'll work out.

I'll just make sure to index the new field with no duplicates.

 

[mph1]

Hi Vandy,

Can you post your email address. Mine is lmaj12@comcast.net. I have something typed up for you, but it is rather lengthly and don't want to take up too much space here.

Maurie

An investment in knowledge always pays the best dividends.
by Benjamin Franklin
Autonumber Description - FAQ702-5106