Results 1 to 3 of 3
  1. #1
    Star Lounger
    Join Date
    Sep 2002
    Posts
    81
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Autonumber problem (Access 2000)

    I have a 10 identical tables called TblOffers for 10 different branches. The first row in the table is called OfferID. I need to change the autonumber of each table,and i use the following function

    Public Function InitializeAutonumber(strTableName As String, strAutonumberField As String, lngStartNumber As Long)
    'Usage example:
    'InitializeAutonumber "tblCustomers", "CustomerID", 5000


    Dim rst As DAO.Recordset
    Dim lngCurrentMax As Long
    Dim i As Long

    Set rst = CurrentDb.OpenRecordset(strTableName)
    lngCurrentMax = Nz(DMax(strAutonumberField, strTableName), 0)

    ' insert records into table to start autonumber field
    For i = lngCurrentMax + 1 To lngStartNumber - 1
    rst.AddNew
    rst.Update
    Next

    ' now delete inserted records
    CurrentDb.Execute "DELETE FROM " & strTableName & _
    " WHERE " & strAutonumberField & " > " & _
    CStr(lngCurrentMax)

    Set rst = Nothing
    'If lngCurrentMax < lngStartNumber Then
    'MsgBox "Autonumbering will start from " & lngStartNumber
    'Else
    'MsgBox "Last used number greater than " & lngStartNumber
    'End If

    End Function


    I give the folloiwing command on an OnClick event:

    InitializeAutonumber "tblOffersSo", "Offerid", 1
    InitializeAutonumber "tblOffersVa", "Offerid", 2000
    InitializeAutonumber "tblOffersBl", "Offerid", 4000
    InitializeAutonumber "tblOffersHa", "Offerid", 6000
    InitializeAutonumber "tblOffersPl", "Offerid", 8000
    InitializeAutonumber "tblOffersTa", "Offerid", 10000
    InitializeAutonumber "tblOffersTr", "Offerid", 12000
    InitializeAutonumber "tblOffersSz", "Offerid", 14000
    InitializeAutonumber "tblOffersRs", "Offerid", 16000
    InitializeAutonumber "tblOffersBs", "Offerid", 18000



    I do obtain the desired results,however when i export the table into another database, then the initliaized number disappears.Therefore i am not satisfied
    with my function and would like to ask is there a better solution to my problem?

  2. #2
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Autonumber problem (Access 2000)

    You have probably got your reasons for that design, but you don't need 10 different tables. An offers table can have a branchID field to say which branch it is.
    In general, you use autonumber when you don't care what the number is. In your case, I would just make the field a long integer, and write a custom function that found the current largest number (or even stored it in another table) then incremented it. This would avoid the problem with exporting. If you merge to one table, then the function would have to find the largest number for that branch.
    How do you export the data? Why?
    If you just want a copy of the current data then you could import the whole table into another db without losing your numbers. If instead you are appending records to a table in another db then you woud lose the autonumber. But if you are not going to add new records in this other db, then use a Long Integer field instead of autonumber and the existing numbers will come across.
    Regards
    John



  3. #3
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Autonumber problem (Access 2000)

    Don't try to control autonumbers. Their purpose is strictly to provide a unique key for a record, nothing else. And having 10 tables is a very bad design idea that will cause you problems down the road. Create a single table with the same field structure but add a branch identifier field to the table. Then append the records from each of the other tables, one at a time, and populate the branch id for that branch. Now you don't have to worry about autonumbers starting at any particular value and you won't go nuts trying to do queries to compare branches based on 10 different tables. You also won't run into problems with numbers overlapping at some point.
    Charlotte

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •