INSERT INTO increment control field

Question

I'm using MS-ACCESS database. From the prject I use and made some other questions the table NOEUDS and INFRA (that should be updated):

Table INFRA:

RECNO   -   NOEUD   -   SECURISE    
00000008    C002         F    
00000005    C009         F    
00000001    C035         F    
00000002    C001         F    
00000003    C036         F    
00000006    C012         F    
00000007    C013         F

TABLE NOEUDS:

NOEUD   TYPE_MAT  N_AMONT       
C021     COF       100          
C022     COF       229          
C023     COF       130          
C002     COF       111

I want to create a query that checks on NOEUDS the nodes C* that are missing inside INFRA table, if not should be inserted a new one. The problem is the RECNO field that works as a control and can not be duplicated (not primary key because all the DB is only a repositoty for the program that controls it). All the fields are text so RECNO is a consecutive counting using HEX numbers as shown.

I used the query to select:

SELECT (SELECT MAX(CINT(INFRA.RECNO))+1 AS N FROM INFRA), 
       NOEUDS.NOEUD, "F" AS Expr2
FROM NOEUDS
WHERE (((NOEUDS.NOEUD) Like "C*" 
       And (NOEUDS.NOEUD) Not In (SELECT NOEUD FROM INFRA)));

The result was:

9   C021   F    
9   C022   F    
9   C023   F

SHOULD BE:

9   C021   F    
A   C022   F    
B   C023   F

I need some help on this one so I can insert the correct RECNO in hexadecimal counting after 00000019 passes to 0000001A and so on.

thanks in advance

UPDATE 1:

The program we use uses a Access database as storage. When I add a noeud using the program I have to insert some more info using the menus needed for the maps and as built information. The problem is that a lot info is redundant and the program can not handle it automatically. I am trying to work lees and insert the possible information using querys.

Every time I insert a noeud in noeuds table, is needed to insert a line in INFRA table only with RECNO (sequential counting from the last one), the NOEUD and some other info (to complete the autocad table tag). Since I have hundreds of Cxxx, Bxxx, Pxxx, Gxxx equipments I sabe for each project some hour of boring work.

I need help on counting a sequential way of adding RECNO for each NOEUD found in NOEUDS table that will be inserted in INFRA table.

UPDATE 2:

I'm inserting each noeud by hand. Is it possible to join in a way that it takes the list from the noeuds that I want to insert and insead of doing 1 by 1 it takes the list and does in a sequence?

the 2 queries are these:

Equipes I want to add at table INFRA:

SELECT NOEUDS.NOEUD FROM NOEUDS WHERE (((NOEUDS.NOEUD) Like "C*" And (NOEUDS.NOEUD) Not In (SELECT NOEUD FROM INFRA)));

Insertion by hand:

INSERT INTO INFRA ( recno, NOEUD, SECURISE ) SELECT (SELECT Right(String(8, "0") & Hex(Max(Val("&H" & RECNO)) + 1), 8) AS N FROM INFRA), NOEUDS.NOEUD, "F" AS Expr2 FROM NOEUDS WHERE (NOEUDS.NOEUD=[INSERT CHAMBRE?]);

Answer 1

I think a VBA solution should be better than trying to do what you want with only SQL. If you don't have much VBA experience, it could still be achievable because the required VBA should be fairly basic. See if this code outline is enough to get you started.

Public Sub AddToInfra()
    Const cstrQuery As String = "qryUnmatchedNoeuds" ' Note 1 '
    Dim db As DAO.Database ' Note 2 '
    Dim fld As DAO.Field
    Dim rsFrom As DAO.Recordset
    Dim rsTo As DAO.Recordset

    Set db = CurrentDb
    Set rsFrom = db.OpenRecordset(cstrQuery, dbOpenSnapshot)
    Set rsTo = db.OpenRecordset("infra", dbOpenTable, dbAppendOnly)

    Do While Not rsFrom.EOF
        rsTo.AddNew
        For Each fld In rsFrom.Fields ' Note 3 '
            If Not fld.Name = "RECNO" Then
                rsTo.Fields(fld.Name).Value = fld.Value
            End If
        Next fld
        rsTo!RECNO = Next_InfraRecno ' Note 4 '
        rsTo!SECURISE = "F" ' Note 5 '
        rsTo.Update
        rsFrom.MoveNext
    Loop

    rsTo.Close
    rsFrom.Close
    Set fld = Nothing
    Set rsFrom = Nothing
    Set rsTo = Nothing
    Set db = Nothing
End Sub

Notes:

1. I used a saved query based on my best guess as to what you want. See the SQL below.
2. DAO.Database requires a reference to Microsoft DAO Object Library. If your Access version is 2000 or maybe Access XP, you may need to set that reference (from VBE main menu, Tools->References).
3. I decided the destination table would include fields which match the name and data type of the fields in the source recordset. If that doesn't work for you, substitute something like this for each of the common fields: rsTo!YourFieldNameHere.Value = rsTo!YourFieldNameHere.Value (And drop .Value if you prefer.)
4. Create a Next_InfraRecno() function to return the next RECNO value. Translate the approach we used earlier into a function. Post a new question if you run into trouble ... show us your code, error message and line which triggers the error (if any), and anything else we need to know. :-)
5. I got the impression you want SECURISE = "F" for each of the inserted rows.

In a comment you mentioned "Use field ANCIEN for storage of counting". I don't know what's involved for that and hope, whatever it is, you can integrate it into this code outline. If not, sorry. :-(

Here is the SQL for my qryUnmatchedNoeuds query:

SELECT n.DELETED, n.NOEUD
FROM
    noeuds AS n
    LEFT JOIN infra AS i
    ON n.NOEUD = i.NOEUD
WHERE
        (((n.NOEUD) Like "c*")
    AND ((i.NOEUD) Is Null));