Insert Sequential Long Integers from VBA 1

[ThomasLafferty]

I have a table called CNIDMaster with a single field called CNID whose data type is Long Integer (not auto increment the numbers are hand entered.)

How can I use VBA to insert a sequential series of Long Integers into the table based upon a start and end integer that I specify on a form? Here's what I have for code so far, and I have an error in my SQL string someplace, but I'm stumped.

Private Sub btn_Search_Click()
'*****VARIABLE DECLARATION*****
    Dim lngIncrementCNID As Long, strSQL As String
    
'Initialize variable
    lngIncrementCNID = Me.CNIDStart.Value
    
                While lngIncrementCNID <= Me.CNIDEnd.Value
                    strSQL = "INSERT INTO [CNIDMaster]," _
                    & "SELECT [CNID] FROM [CNIDMASTER],"
                    [!]'Probably need line here to refer to lngIncrementID[/!]
                    DoCmd.SetWarnings False
                    'Error occurs here
                    [highlight]DoCmd.RunSQL strSQL[/highlight]
                    DoCmd.SetWarnings True
                    lngIncrementCNID = lngIncrementCNID + 1
                Wend                    'CNIDs successfully added
End Sub

Any ideas?

Born once die twice; born twice die once.

 

[PHV]

strSQL = "INSERT INTO CNIDMaster (CNID) VALUES (" _
& lngIncrementCNID & ")"

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[ThomasLafferty]

PHV - you are always on the money. Worked first time! Have a star.



Tom

Born once die twice; born twice die once.