Weird issue with an insert query

[DevilDawg]

I have a table that is set to hold temporary values. It is built as:


Field Name :CID
Data Type :Autonumber
Field Size :Long Integer
Format :blank
Caption :blank
Indexed :Yes(No Duplicates)


Text Align :General
________________________________________________
Field Name :OID
Data Type :Number
Field Size :Long Integer
Format :blank
Decimal Places :Auto
Input Mask :blank
Caption :blank
Default Value :0
Validation Rule :blank
Validation Text :blank
Required :No
Indexed :Yes (Duplicates OK)
Text Align :General
________________________________________________
Field Name :CATID
Data Type :Number
Data Type :Number
Field Size :Long Integer
Format :blank
Decimal Places :Auto
Input Mask :blank
Caption :blank
Default Value :0
Validation Rule :blank
Validation Text :blank
Required :No
Indexed :Yes (Duplicates OK)
Text Align :General
________________________________________________
Field Name :CN
Data Type :Short Text
Field Size :255
Format :blank
Input Mask :blank
Caption :blank
Default Value :blank
Validation Rule :blank
Validation Text :blank
Required :No
Allow Zero Length :Yes
Indexed :Yes (Duplicates OK)
Unicode Compression :Yes
IME Mode :No Control
IME Sentence Mode :None
Text Align :General
________________________________________________
Field Name :Rating
Data Type :Number
Field Size :Long Integer
Format :blank
Decimal Places :Auto
Input Mask :blank
Caption :blank
Default Value :0
Validation Rule :blank
Validation Text :blank
Required :No
Indexed :Yes (Duplicates OK)
Text Align :General
________________________________________________
Field Name :Sbol1
Data Type :Yes/No
Format :Yes/No
Caption :blank
Default Value :No
Validation Rule :blank
Validation Text :blank
Indexed :No
Text Align :General
________________________________________________
Field Name :Sbol2
Data Type :Yes/No
Format :Yes/No
Caption :blank
Default Value :No
Validation Rule :blank
Validation Text :blank
Indexed :No
Text Align :General
________________________________________________
Field Name :Sbol3
Data Type :Yes/No
Format :Yes/No
Caption :blank
Default Value :No
Validation Rule :blank
Validation Text :blank
Indexed :No
Text Align :General
________________________________________________
Field Name :Sbol4
Data Type :Yes/No
Format :Yes/No
Caption :blank
Default Value :No
Validation Rule :blank
Validation Text :blank
Indexed :No
Text Align :General
________________________________________________
Field Name :Sbol5
Data Type :Yes/No
Format :Yes/No
Caption :blank
Default Value :No
Validation Rule :blank
Validation Text :blank
Indexed :No
Text Align :General

I have a function :

Public Sub AddCategoryToOrder(ByVal intOID, ByVal inCID)
  Dim varSQL As String
 On Error GoTo Err_Handler
   
  varSQL = "INSERT INTO tbltmpCategoryRatings (OID, CATID) SELECT " & intOID & ", " & inCID


  Debug.Print varSQL


  DoCmd.SetWarnings False
    DoCmd.RunSQL (varSQL)
  DoCmd.SetWarnings True


Exit_Handler:
  Exit Sub
  
Err_Handler:
       Select Case Err.Number


        Case Else
          MsgBox "The following error has occurred." & vbCrLf & vbCrLf & _
                 "Error Number: " & Err.Number & vbCrLf & _
                 "Error Source: modDatabaseInteraction:AddCategoryToOrder" & vbCrLf & _
                 "Error Description: " & Err.Description, _
                 vbCritical, "An Error has Occurred!"
          Call LogError(Err.Number, Err.Description, "modDatabaseInteraction:AddCategoryToOrder")
          Resume Exit_Handler
        End Select
End Sub

I can add the first with no issue. When I attempt to add a second by this means, I get the error that it cannot add it due to "and it didn't add 1 record(s) to the table due to key violation ". Upon additional messing, I found that even when I manually go into the table and add one, it duplicated the CID value, a value that is supposed to be auto incremented. I deleted the existing table and made another and it still does the same thing. Is this some bug (Using Access 2016). I have also compacted and repaired the database and it continues to do the same. I have never encountered this before and just wondering if I am the only one or if someone sees something I am doing incorrectly, have I lost my mind (rhetorical question; I know I have). Anyone have an insight? The values are being passed correctly too.

[mike60smart]

Can you upload a copy of the database?

[CarlettoFed]

You need to change the data type in the temporary table:

Field Name :CID
Data Type :Number

[moke123]

I would add the datatypes to your procedure arguments.
Use "Values" rather than "Select"
Use the .execute method as it will roll back changes if an error is encountered.

Public Sub AddCategoryToOrder(ByVal intOID as Long, ByVal inCID as long)
  Dim varSQL As String
 On Error GoTo Err_Handler
   
  varSQL = "INSERT INTO tbltmpCategoryRatings (OID, CATID) Values(" & intOID & ", " & inCID &")"


  Debug.Print varSQL


CurrentDb.Execute  varSQL, dbFailOnError