use vb to create new records based on number range

[tagteam]

I am looking for a way to use vb, or any other way, to allow my users to enter a railcar initial and then a number sequence and add new records to the end of the table.



For example in a form the user can enter the car initial and the number sequence
Railcar initial: GATX
Number sequence : 290001 - 290100

I would like a way to create a temp table that then has 100 records
GATX 290001
GATX 290002
GATX 290003
etc etc.

I can then use this temp table in an append query to add them to my main table.

[June7]

This is generating a custom unique identifier - common topic in forum. Search: unique identifier

Why bother with temp table, write records directly to main table.

For i = 1 to 100
CurrentDb.Execute "INSERT INTO maintable(ID) VALUES('" & Me.Initial & Me.Seq & Format(i, "0000") & "')"
Next

[ItsMe]

I would probably use DAO and the .AddNew method

for i = y to x -1
rs.addnew
rs![Field] = "CAIX" & i
rs.update
next

as an example

edit: I forgot the format thing...

[orange]

I see others have posted, but here is another.

'---------------------------------------------------------------------------------------
' Procedure : tagteam
' Author    : Jack
' Date      : 16/04/2014
' Purpose   : Routine to create table YourTemTable and to populate it
'with 100 records like
'GATX 290001
'GATX 290002
'GATX 290003
'---------------------------------------------------------------------------------------
'
Sub tagteam()
          Dim db As DAO.Database
          Dim rs As DAO.Recordset
          Dim iCnt As Integer
          Dim stxt As String
10        stxt = "GATX"
          Dim sql As String
20        On Error GoTo 0
30        sql = "Create TABLE YourTempTable (ID  autoincrement not null Primary Key, RailCarInitial varchar(4), RailCarNumber long);"
40        Set db = CurrentDb
45        db.Execute sql, dbFailOnError
50        On Error GoTo tagteam_Error
60        Set rs = db.OpenRecordset("YourTempTable")
70        For i = 290001 To 290100 Step 1
80            rs.AddNew
90            rs!RailCarInitial = stxt
100           rs!RailCarNumber = i
110           rs.Update
120       Next i
130       rs.Close
140       db.Close
150       On Error GoTo 0
160       Exit Sub

tagteam_Error:

170       MsgBox "Error " & Err.number & " (" & Err.Description & ") in procedure tagteam of Module UtterAccessRelated"
End Sub

[tagteam]

Thanks, can I use a variable that is specified in the form. The number and the car initial will not always be the same and the user will enter them in a form. The car initial will be the same for that use. For example it might be GATX 1-50 and next time it might be UTLX 22456-22480

Can i do:
Dim stxt AS string
stxt = [Forms]![Form]![CarInitial]
...

And then later
Dim Number as int
Number = [Forms]![Form]![BeginNumber]
for i in Number Step 1
...

[June7]

Still need the start and end for the For loop

Number is a reserved word. Use something like intNum as variable name.

For i = intNum To intNum + 200

1 is default step so don't really need the Step parameter.

[ItsMe]

Plus formatting like June7 mentioned earlier since the car numbers can have leading zeros

[tagteam]

Thanks, I used the code from orange and changed it to begincarnumber as June7 suggested and it is working like I want.
THanks