Results 1 to 4 of 4
  1. #1
    Star Lounger
    Join Date
    Sep 2002
    Location
    Indianapolis, Indiana, USA
    Posts
    80
    Thanks
    1
    Thanked 0 Times in 0 Posts

    insert into range of values (2002)

    I would like to enter a range of new values (ie new records) into a table using SQL. Do I have to build up the value range prior to executing the SQL statement or is there an "insert into...between structure" I can use. These values can be from 1 value to thousands of values and each value would be inserted as a new record. Essentially, the singe value or range of values will be entered into a couple of unbound text boxes on a form and vb will do the entry.

    This needs to be done in SQL since it will be migrated to an SQL database.

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: insert into range of values (2002)

    You could use some code like this :
    <pre>Sub InsertRecords()
    Dim x As Long
    Dim strSQL As String
    DoCmd.SetWarnings False
    For x = 1 To 100
    strSQL = "Insert Into Table1 (YourField) Values (" & x & ")"
    DoCmd.RunSQL strSQL
    Next x
    DoCmd.SetWarnings True
    End Sub</pre>

    You can replace the 100 with something like:
    For x = 1 to Forms.MyForm.MyTextbox
    Francois

  3. #3
    Star Lounger
    Join Date
    Sep 2002
    Location
    Indianapolis, Indiana, USA
    Posts
    80
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: insert into range of values (2002)

    Thanks, I had arrived at the same conclusion, that is, I was delusional and really needed to put it inside a loop. One caution when I tried your version, one has to determine what information the unbound text boxes are going to contain. Are they going to contain the starting & ending values or the number of records you are going to add. If you are going to add a range of ids, such as, ids 1000-1003, you have to use a formula to derive the number of records you want to add, ie...1000-1003+1 or you will start with 1000 and add 1003 records. If one is going to enter the number of records to add then you only need 1 text box, but you will have to keep track of your ids or use an autonumber field.

  4. #4
    Star Lounger
    Join Date
    Sep 2002
    Location
    Indianapolis, Indiana, USA
    Posts
    80
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: insert into range of values (2002)

    This code has been updated since the original post. Some string variables have been changed to variants, since they can have null values.
    I thought I could do this without using a loop, alas, no. Here is code that works. This runs off of a button with two unbound text boxes feeding the vb code.

    Private Sub SelectIDs_Click()
    'insert new ID or range of IDs into ID table

    On Error GoTo Err_SelectIDs_Click

    Dim strDKstart, strDKend, As Variant
    Dim strcount, strSelect As String

    Dim counter As Integer

    Dim qdf As DAO.QueryDef
    Dim dbs As DAO.Database

    Set dbs = CurrentDb

    strDKstart = [Text0]
    strDKend = [Text2]


    strcount = strDKstart

    If IsNull(strDKend) Then
    MsgBox "Second Value is NULL " & strDKend
    strSelect = "INSERT INTO TEST(sampleID) values (" & strDKstart & ");"
    Set qdf = dbs.CreateQueryDef("", strSelect)
    qdf.Execute
    ELSE
    For counter = 1 To (strDKend - strDKstart + 1)
    strSelect = "INSERT INTO TEST(sampleID) values (" & strcount & ");"
    MsgBox strSelect
    Set qdf = dbs.CreateQueryDef("", strSelect)
    qdf.Execute
    strcount = strcount + 1
    Next
    End If

    Set qdf = Nothing
    Set dbs = Nothing

    Exit_SelectIDs_Click:
    Exit Sub

    Err_SelectIDs_Click:
    MsgBox Err.Description
    Resume Exit_SelectIDs_Click

    End Sub

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •