Autonumber Value 1

[rareswan]

How can I tie the value of one field to the value of the autonumber field?

Access is not an area of my expertise, but then no one is sure if that area actually exists.

Thanks for any help

Randy

 

[RickSpr]

What do you mean by "tie the value of one field to the value of the autonumber field"? Just having values in the same row of a table ties them together. Please provide more information. Rick Sprague

 

[rareswan]

I want a field labeled WorkOrderNumber to increment along with the autonumber.

Thanks
Randy

 

[RickSpr]

I'm guessing you want another autonumber-like field, rather than one whose value is linked to the existing autonumber field. (If you wanted that, you could just name the existing autonumber field 'WorkOrderNumber' and be done.)

I'd suggest you create a simple table called LastKeys with two columns:
Usage Number(Long) Unique key for this serial number
LastKey Number(Long) Last serial number assigned
If you want, you can add a third column:
Descr Text(50) Description of the use of this serial number

At this point, you need only one row in the table, which will hold the next work order number in LastKey. But by adding a Usage column as a key, you can use the same table for other assigned numbers in the future if you should need one.

You should create a standard module something like this:

    ' Usage codes (keys for the LastKey table)
    Public Const gnkWorkOrder = 1    ' work order numbers
    ' Public Const gnkXxxxNumber = 2 ' may add more in future

    Public Function GetNextKey(Usage As Long) As Long
        Const SQL = "SELECT LastKey FROM LastKeys " _
            & "WHERE Usage="
        Dim db As DAO.Database, rst As DAO.RecordSet

        Set db = CurrentDb()
        Set rst = db.OpenRecordset(SQL & Usage, _
            dbOpenDynaset, dbDenyRead, dbPessimistic)
        If rst.EOF Then
            ' Handle error: Invalid Usage argument
        Else
            db.Workspaces(0).BeginTrans
            On Error GoTo GNK_Error
            rst.Edit
            GetNextKey = rst!LastKey + 1
            rst!LastKey = nextKey
            rst.Update
            db.Workspaces(0).CommitTrans
        End If
        rst.Close
    GNK_Exit:
        Set rst = Nothing
        Set db = Nothing
        Exit Function
    GNK_Error:
        Select Case Err.Number
            Case 3260, 3262  ' lock conflict:
                DoEvents     ' let the system run a moment
                Resume       ' try again
            Case Else
                Err.Raise Err.Number
        End Select
    End Function

Now, how to use this. At the point where you want to insert the next number in the series, simply call GetNextKey to retrieve it. For example:
txtWorkOrderNumber = GetNextKey(gnkWorkOrder)
The argument passed is one of the constants defined in the module added above. The value of the constant is the unique key for the row in the LastKeys table.

Note that this function allocates the next number for good before it returns. If you fail to use the number, or if your user aborts the update, the number will simply be skipped. If it's important that your work order numbers be assigned without any gaps, check out the FAQ named "Why AutoNumber shouldn't be used in MultiUser databases (And How to ge the Unique Number)", in the Access Tables and Relationships forum, for an alternative method. Rick Sprague

 

[rareswan]

Rick

Thanks for the help it was much more than I expected. I tried it just as written and I'm not worried about making it work except when I try to run it I get a compile error "Method or data member not found" when it comes to the '.workspaces' object. I've checked the references to make sure DAO 3.6 was checked and it was. I don't want you to go to any trouble just wondering if you had a quick answer. If not I'll sort it out myself. Again thanks for help.

Randy

 

[RickSpr]

Oops! My mistake. I must have been sleepy when I wrote that.

Everywhere I had "db.Workspaces" change it to "DBEngine.Workspaces" and it should work.

Sorry about that. Hope you get it working now. Rick Sprague