Results 1 to 9 of 9
  1. #1
    archzealot is offline Novice
    Windows Vista Access 2007
    Join Date
    Apr 2010
    Posts
    3

    creating records from integer range in a form


    Hello, Suppose I have a table called Tickets and fields are:
    ticketNumber, integer
    name, Text

    And I want a form where the user enters 3 things:
    1. a start number
    2. an end number
    3. a name

    And when he pressed "OK", it should perform an INSERT INTO statement where it generates 1 record for each number in the range between [start] and [end] with the given name.

    So for example if the user enters 4, 6, "John", then it should append the following 3 records:
    4, John
    5, John
    6, John

    How can I achieve this effect with a clean solution? I've heard about the technique called "auxiliary number table" and combining it with a certain select statement, one can get the desired effect. But the number field can get quite large, so I was wondering if there is another solution that perhaps uses loops of some kind. It's too bad the SQL editor doesn't allow WHILE.

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    21,909
    One way would be a For/Next loop using your start and end numbers, and the AddNew method of a recordset inside it, using the name and the current value of the loop.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    maximus's Avatar
    maximus is offline Expert
    Windows 7 Access 2010 (version 14.0)
    Join Date
    Aug 2009
    Location
    India
    Posts
    931
    This is What I have Done I have a Form range appears as Startup. Type starting and Ending Number and the name and Click Insert Button. Below is the Code attached to the button. Its how pbaldy had put in using For/Next I have used a INSERT INTO to insert the record on to my table. Attaching a sample mdb.

    Private Sub Command6_Click()
    On Error GoTo Err_Command6_Click
    Dim int1stLimit As Integer
    Dim int2ndLimit As Integer
    Dim intNumber As Integer
    Dim strSQL As String

    int1stLimit = Me.Text0
    int2ndLimit = Me.Text2

    For intNumber = int1stLimit To int2ndLimit

    strSQL = "INSERT INTO Ticket(P_Name,Ticket) Values('" & Me.Text4 & "'," & intNumber & ");"
    DoCmd.SetWarnings False
    DoCmd.RunSQL strSQL
    DoCmd.SetWarnings True
    Next intNumber


    Exit_Command6_Click:
    Exit Sub

    Err_Command6_Click:
    MsgBox Err.Description
    Resume Exit_Command6_Click

    End Sub

  4. #4
    maximus's Avatar
    maximus is offline Expert
    Windows 7 Access 2010 (version 14.0)
    Join Date
    Aug 2009
    Location
    India
    Posts
    931
    if this solves your problem mark the thread solved.

  5. #5
    archzealot is offline Novice
    Windows Vista Access 2007
    Join Date
    Apr 2010
    Posts
    3
    Thanks for the replies everone...

  6. #6
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    21,909
    As an FYI, in my experience, the recordset will insert records faster than executing SQL every pass (in a single-record insert situation like this).
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  7. #7
    maximus's Avatar
    maximus is offline Expert
    Windows 7 Access 2010 (version 14.0)
    Join Date
    Aug 2009
    Location
    India
    Posts
    931

    Recordset

    I absolutely agree with you Sir on this that using Recordset will insert data much faster.

    I just used SQL as archzealot had mentioned Insert Into in his First Thread. I have now done the same thing using RecordSet and here is the code as follows:

    Private Sub Command7_Click()
    On Error GoTo Err_Command7_Click
    Dim int1stLimit As Integer
    Dim int2ndLimit As Integer
    Dim intNumber As Integer

    int1stLimit = Me.Text0
    int2ndLimit = Me.Text2

    For intNumber = int1stLimit To int2ndLimit '------ loop started
    Set rst = CurrentDb.OpenRecordset("Ticket") '--- Opening a Recordset
    rst.AddNew '---- Add a New Record
    rst!Ticket = intNumber '----- Assigning value to rst
    rst!P_Name = Me.Text4 '----- Assigning value to rst
    rst.Update '--- save the record
    rst.Close '--- close the recordset
    Set rst = Nothing '--- reclaim the memory the recordset was using
    Next intNumber
    Exit_Command7_Click:
    Exit Sub

    Err_Command7_Click:
    MsgBox Err.Description
    Resume Exit_Command7_Click

    End Sub


    Attaching a sample DB

    Thanx
    Maximus

  8. #8
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    21,909
    If you think the recordset is faster, watch how fast it goes if you put the open and close outside the loop.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  9. #9
    maximus's Avatar
    maximus is offline Expert
    Windows 7 Access 2010 (version 14.0)
    Join Date
    Aug 2009
    Location
    India
    Posts
    931
    I just tried it out. I will certainly keep this in mind thanks.
    You r Simply G8

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 4
    Last Post: 01-25-2010, 04:14 PM
  2. Search form with a date range
    By mantro174 in forum Forms
    Replies: 1
    Last Post: 02-11-2009, 10:45 PM
  3. Creating top values with two records
    By avesamuel in forum Queries
    Replies: 0
    Last Post: 10-15-2007, 10:45 PM
  4. Continuous Range of Records
    By tigers in forum Access
    Replies: 0
    Last Post: 10-10-2007, 08:36 AM
  5. Creating a string from all records in a table
    By ocordero in forum Programming
    Replies: 2
    Last Post: 08-07-2006, 09:21 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums