Results 1 to 2 of 2
  1. #1
    Join Date
    Jul 2001

    Unanswered: What is going on with Autonumber!? (

    I have already posted this to the MS SQL section but thought I would give it a go here...

    I am using Access 2002 as a front end to SQL server and I am trying to add a new record to several related tables.

    The form is bound to my Publications table so when the user starts typing it gets assigned a new ID number by the autonumber mechanism. I read this value by using me!PublicationID and then I try and create related records in 2 other tables using VBA/ADO. PublicationID is the foreign key in these 2 tables.

    The trouble is that the initial autonumber, that gets assigned, changes once the new record is saved to my Publications table...


    User starts entering data. The last publication has an ID of 14 so this one gets an ID of 15. Once the record is saved the actual value stored in the ID field is higher! I assume this higher ID number takes into account all the records that I have added and then deleted after number 14.

    Is there any way to close up these phantom autonumber gaps? I want the database to use the next available value but it insists on remembering all the numbers assigned to records tat never even got saved.

    Alternatively would it be best to save the new record and then retrieve the number that really gets assigned and use this for the related foreign keys?

  2. #2
    Join Date
    Jul 2001
    I ended up solving it the following way:

    1. Saved the data in Advertisers. It has now been given a final ID number that won't change

    2. I used a stored procedure (a query would have done the trick if I knew how to pass parameters to it or used an sql statement constructed on the client) that found the publication with the name that was just entered and then max(publicationID). Here it is:

    ALTER Procedure procFindLastPublicationID
    @MyParam varchar
    SELECT max(PublicationID)
    FROM tblPublications
    WHERE (PublicationName LIKE @MyParam + '%')

    For some reason I only got matches when the WHERE clause was a 'LIKE' rather than an equals. Does anyone know why?

    The VBA function to call this:

    Function fnFindLastPublicationID(strPublicationName As String) As Integer

    Dim rst1 As ADODB.Recordset
    Dim cmd1 As ADODB.Command
    Dim prm1 As ADODB.Parameter
    Dim ary1 As Variant

    If strPublicationName = "" Then
    ReDim ary1(1, 1)
    ary1(0, 0) = -1
    GoTo EndMe
    End If

    ' Initialize the stored procedure
    Set cmd1 = New ADODB.Command
    cmd1.ActiveConnection = CurrentProject.Connection
    cmd1.CommandType = adCmdStoredProc
    ' Which stored procedure?
    cmd1.CommandText = "procFindLastPublicationID"
    ' Set the parameters
    Set prm1=cmd1.CreateParameter("@MyParam", adVarChar, _
    adParamInput, Len(strPublicationName))
    cmd1.Parameters.Append prm1
    ' Pass the criterion
    prm1.Value = strPublicationName
    ' Execute
    Set rst1 = cmd1.Execute
    ' Return the result
    ary1 = rst1.GetRows
    ' Close the recordset
    Set rst1 = Nothing


    fnFindLastPublicationID = ary1(0, 0)

    End Function

    (The proc that calls this converts NULLs to empty strings before passing PublicationName)

    So this function takes a Publication name and returns the most recent record that matches it...

    If anyone can suggest a better way?

Posting Permissions

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