Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    Apr 2004
    Posts
    100

    Smile Unanswered: alternative of autonumber? - SOLVED -

    Hello,

    I am using MS Access 2003 and some forms for managing data.
    Since my database should be migrated to DB2 some time, I do not use the MS Access specific datatype autonumber. However it would be very usefull in handling my forms.
    So I am wondering if someone might already have code that given an primary key of the datatype number retruns a free number for a new entry? Or could at least someone provide me some instuctions how to create such a function myself. Thanks in advance.
    Regards,
    Proximus
    Last edited by Proximus; 05-21-04 at 04:43.

  2. #2
    Join Date
    Mar 2002
    Posts
    192
    GIVEN:
    Table Name: MyTable
    Field Name: MyAutonumberField
    Data Model: ADO (not DAO)

    Public Function GetNextAutoNumber()

    dim cn as new adodb.connection
    dim rs as new adodb.recordset

    set cn = currentProject.Connection
    rs.open "SELECT MAX(MyAutoNumberField) AS MaxValue FROM MyTable"
    if not rs.eof then GetNextAutoNumber = rs.fields("MaxValue") + 1 else 1
    rs.close
    cn.close
    set rs = nothing
    set cn = nothing

    end function

  3. #3
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    There is a simpler way, try the domain function DMax, ie

    NextID=DMax("MyKeyField","MyTable")+1

    I have used this a number of times and it seems to work (so fare!)


    MTB

  4. #4
    Join Date
    Apr 2004
    Posts
    100
    Thanks for your answers !!
    Given the function of access dude:

    Public Function GetNextAutoNumber()

    Dim cn As New ADODB.Connection
    Dim cm As New ADODB.Command
    Dim rs As New ADODB.Recordset

    Dim strConnect As String
    Dim strProvider As String
    Dim strDataSource As String
    Dim strDataBaseName As String

    strProvider = "Provider= Microsoft.Jet.OLEDB.3.51;"
    strDataSource = Application.CurrentProject.Path
    strDataBaseName = "\AXA-IOD_ordersystemV0.0.2.mdb;"
    strDataSource = "Data Source=" & strDataSource & strDataBaseName
    strConnect = strProvider & strDataSource

    Set cn = New ADODB.Connection
    'Set cn = CurrentProject.Connection
    ' cm.ActiveConnection = cn

    'rs.Open "SELECT MAX(Req_no) AS MaxValue FROM Request"
    rs.Open "Select Req_no from request"

    If Not rs.EOF Then
    GetNextAutoNumber = rs.Fields("MaxValue") + 1
    Else
    1
    End If

    rs.Close
    cn.Close
    Set rs = Nothing
    Set cn = Nothing

    End Function

    When executing this function I am getting an Run-TIme error '3709': The connection cannot be used to perform this operation . It is either closed or invalid in this context. I have added the Reference to MS ActiveX Date Objects 2.7 Library.

    Then. How can I accomplish that the function is only then called when a new record should be inserted?

    Regards,
    Proximus
    Last edited by Proximus; 05-19-04 at 07:16.

  5. #5
    Join Date
    Mar 2002
    Posts
    192
    A FYI the code that you wrote for you originially works as well. Anyhow no worries. ok

    you would want to make a call to this function from a form event.

    I.E.

    public sub Before_Insert ' Do not write this line yourself. get access to do this via creating an "event procedure" in the form's "properties"
    Me.MyCustomAutoNumberField = GetNextAutoNumber ' Add a line like this yourself. you'll have to change the name of the field to what your autonumber field is called (of course..)
    end sub

  6. #6
    Join Date
    Apr 2004
    Posts
    100
    Unfortunately the code doesn't work.
    I keep getting the error:
    run-time error '3709' The connection cannot be used to perform this operation. It is either closed or invalid in this context.
    Any ideas how I could solve that?

    And there is one more thing that I do not understand: In your code you wrote in the if statment after the else just a 1. Could you kindly correct that. Thanks for your help !!!
    Regards Proximus

  7. #7
    Join Date
    Mar 2002
    Posts
    192
    OK make some minor revisions. This definitely works. Also, because you're using access 2003 and i'm using access 2000 i've included a sample MDB file so that you can convert up to 2003. Hopefully that will address the differences between the two versions as well. Code snippet below.

    Public Function GetNextAutoNumber()

    Dim cn As New ADODB.Connection
    Dim rs As New ADODB.Recordset

    Set cn = CurrentProject.Connection
    rs.Open "SELECT Max(MyID) AS MaxValue FROM tblTest WHERE MyID Is Not Null;", cn, adOpenStatic, adLockReadOnly
    If Not rs.EOF Then
    If Trim(rs.Fields("MaxValue").Value) <> "" Then
    GetNextAutoNumber = rs.Fields("MaxValue") + 1
    Else
    GetNextAutoNumber = 1
    End If
    End If
    rs.Close
    cn.Close
    Set rs = Nothing
    Set cn = Nothing
    End Function
    Attached Files Attached Files

  8. #8
    Join Date
    Apr 2004
    Posts
    100

    Smile

    Problem solved. Thank you very much !!! The recent code that you posted works fine. Thanks again!!!

    Best Regards,
    Proximus

  9. #9
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    is there a risk of two users pulling the same "autonumber"?

    izy
    currently using SS 2008R2

  10. #10
    Join Date
    Mar 2002
    Posts
    192
    Yes it is true that two users could potentialy pull the same number. However, they would never BOTH be able to save. Why? Becuase this field is being used as the primary key. The first one to save wins and the other users gets a trappable error message (i.e. you can code for this scenerio)

  11. #11
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    you can code for this scenerio
    you MUST code for this scenerio

    izy
    currently using SS 2008R2

  12. #12
    Join Date
    Mar 2002
    Posts
    192
    What's with the large bold lettering? Are we arguing here?

  13. #13
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    we are not arguing at all - sorry if it looks that way: it was not meant to.

    in truth, this has been a general area of fascination for me for some time - not really the autonumber stuff cos my backend handles that, but more the general idea of "local" data keeping in sync with server data in a busy multi-user environment.

    my original "innocent" question is arguably more provocative than the big bold type later on.

    sorry for any upset. i'll try to be cooler when i'm cool - when i'm angry it will be obvious - promise.

    izy
    currently using SS 2008R2

  14. #14
    Join Date
    Mar 2002
    Posts
    192
    I do not understand your original angle on this. What kind of message is a person supposed to get from your all caps, big bold letters?

  15. #15
    Join Date
    Dec 2003
    Location
    Dallas, TX
    Posts
    1,004

    Smile

    Quote Originally Posted by access_dude
    I do not understand your original angle on this. What kind of message is a person supposed to get from your all caps, big bold letters?

    Hi access_dude,

    From watching izy a lot I think that was just meant as a means for more real EMPHASIS. Your phrase being,"You CAN code for this scenario" made it seem as though it should be a casual option. Izy seems to be saying, "You MUST code...." meaning it should not be optional. Kinda like saying after a bridge is all washed out, just as you round the corner and saying we COULD post a sign alerting all drivers, -vs- We WILL post a sign.....alerting the unknowing.

    have a nice one,
    Bud

Posting Permissions

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