Need to have a unique number across several different tables

[RCurtin]

Ok,
Bacically I have 2 different lists (well there will be 7 in total):
tblValves
ValveID
KKS_no
Description
List - specifies thats its in the valves list
TOP_Code

tblSpools (pipe sections)
SpoolID
SpoolTagNo
Description
List - specifies thats its in the spools list
TOP_Code

The reason that I have kept these two in different tables rather than combining them is because the spools table will relate to a few tables that will have nothing to do with valves. For instance the spools table will relate to a table called tblPipeSupports.

The thing is this: I would like if there was a unique number across both tables. Collectively the items in coth tables are called components. Some of the components from either table will have drawing numbers for instance so I would like if I could have a table:
tblDrawings
DrawingID
DrawingTitle
ComponentID (FK to valveID or spoolID)

However this won't work because valveID or SpoolID could have the same numbers. I could use a composit key like SpoolID + List but I think I'm probally making it more complicated than necessary. Also I'll have several tables like the drawing table e.g. some of the components will belong in the testPack table... And I'l have to repeat that List field in each..

So what do I do?

 

[ByteMyzer]

What you need is a place to track a single incremental number which can be used for both tables.

To do this, you can create a table called tblNumber with a Number (Long Integer) field called UniqueID. Then create a Module with the following code:

Public Function NextID() As Long

    Dim rs As DAO.Recordset
    Const lIncrement As Long = 1 ' desired increment amount
    Const lStart As Long = 1 ' desired starting #

    Set rs = DBEngine(0)(0).OpenRecordset( _
        "SELECT * FROM tblNumber", dbOpenDynaset, 0, dbPessimistic)
    ' If this is the first time, create a new record
    If rs.EOF Then
        rs.AddNew
        rs!UniqueID = lStart + lIncrement
        rs.Update
        NextID = lStart
    ' Otherwise, increment the existing counter
    Else
        rs.Edit
        NextID = rs!UniqueID
        rs!UniqueID = NextID + lIncrement
        rs.Update
    End If
    rs.Close
    Set rs = Nothing

End Function

...then save the module as modNextID.

Now, you can execute an append query with SQL something like:
INSERT INTO tblValves (ValveID, KKS_no, Description, List, TOP_Code)
SELECT NextID(), 123, 'My Description',True,1234;

When run, this statement will create a record in the table tblValves with a KKS_no of "123", a Description of "My Description", a List indicator of "True" and a TOP_Code of "1234".

If you then execute an append query with SQL something like:
INSERT INTO tblSpools (SpoolID, SpoolTagNo, Description, List, TOP_Code)
SELECT NextID(), 456, 'My Description 2', False, 5678;

...this will create a record in the table tblSpools with a SpoolTagNo of "456", a Description of "My Description 2", a List indicator of "False" and a TOP_Code of "5678".

Note that in the records created in the above example, the SpoolID for record you created in tblSpools is one increment greater than the ValveID for the record you created in tblValves.

This solution works quite beautifully in a single-user database application. If, however, you wish to utilize this functionality in a multi-user environment, then you will also need to trap record-locking errors that would otherwise cause the function to fail if two or more users simultaneously call this function.

A more detailed article on how to do this can be found here:
How To Implement Multi-user Custom Counters in DAO 3.5

 

[RCurtin]

Thanks so much for the detailed reply ByteMyzer.

I would need to use the multi user version all right - as I mentioned there will be 7 tables of components altogether (spools, valves, instruments, cables etc.) Users will be importing much of the information from excel sheets as well as entering them manually through forms.

I didn't think that it would be necessary to do this through code. (This is not a problem for me - I can understand the code.) However I just want to be sure that it is the best option and would love if you or anyone else could confirm my understanding of this..

Option 1:Combine all of the components into one table.
This is what Pat Hartman suggested to someone else with a similar setup in this thread:
http://www.access-programmers.co.uk/forums/showthread.php?t=42426&highlight=autonumber+union

However, I can't see a way that this would work for my database. Its not that some of the components will have a few uncommon fields - they will be completely different. The spools table would need to be related to other tables that will have nothing to do with the other components. Unless I use composit primary keys i.e. ValveID+List (In this case List = 'Valves'). It seems to me that using composit keys are best avoided though? Plus there will be a few thousand records from some of the 7 lists..

Option 2: Create a custom Autonumber
"V" & M_Valves!ValveID
So I would have the following:
ValveID (Autonumber) ComponentID
100 V100
101 V101
I have been reading about this a lot and most of the arguements are against creating custom autonumbers. Most of these reasons have to do with people creating Autonumbers with 'meaning' such as customer numbers or invoice numbers. That is not the case with this. No-one will ever see the componentID. Its purpose is just to give a unique number across all 7 tables. The valveID would be the primary key. Then componentID could be a unique index. That way I could create relationships between tblValves and other tables that related only to valves. And I could use componentID to link to tables that will be common to all 7 such as tblDrawings or tblTestPack.

However if the table was compacted is it possible that the Autonumber would change and I would have:
ValveID (Autonumber) ComponentID
90 V100
112 V101
Could this cause any issues? The fact that the two will be different shouldn't be an issue I think. It is possible that the ComponentID numbers could get very big tho. Could this cause problems? Could this cause any other problems?
I think that one the benefits of this approach is that it is very straightforward to implement and troubleshoot.

Option 3: Implement a single incremental number through DAO
Using the code kindly provided by ByteMyzer.