Adding Records to Table

[spectrolab]

Hope someone can help!
I was wondering if is possible to add a series of records that are in serquence to a table by just entering the first and last numbers. For example a string of numbers starting at TP11000 and ending at TP11100, the prefix TP doesn't change. Currently I have to enter every one manually, so any help would be much appreciated!

 

[Mile-O]

Const MyTable As String = "YourTableName"
Const MyField As String = "YourFieldName"
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim intCounter As Integer
Set db = CurrentDb
Set rs = db.OpenRecordset(MyTable)
    For intCounter = 11000 To 11100
        rs.AddNew
        rs.Fields(MyField) = "TP" & intCounter
        rs.Update
    Next intCounter
    rs.Close
    db.Close
Set rs = Nothing
Set db = Nothing

 

[spectrolab]

Thanks SJ, I'm not very good at VBA. How would i go about making it so that there was a pop up dialog to enter the first and last numbers when the module is run?
Let me be a little more specific. The db referred to is a Laboratory Information Management System that I have slowly built myself from scratch (bit of a Frankensteins monster) and I am by no means an access expert.
The table is to store the name and submission number of the samples we receive which then generates further paperwork for the sample analysis. We might receive a sample submission with 40 samples in it with sample names from TP18125 to TP18164 but all of these have the same submission number eg TP00664.
All of the reporting and the like is carried out by submission number as all queries and reports relate to that batch of samples. So basically what I need to do is to have a dialog that pops up and says something like "Enter Submission Number" which will be fixed for this job and then "Enter First Number in Job" (in this case TP18125) and then "Enter Last number in Job" (TP18164). Hopefully this will give me 40 lines in the table with the same submission number but the Sample Name will go from TP18125 to TP18164 in sequential order.
Hope that is specific enough, if not please let me know. I can post the db if that would be of help.

 

[sonny]

I have built something simular to what you are looking for. I have a popup form that uses a option group which drives a cbo. Which in your case can be your Submission Number to choose from. Then the user puts the start and ending numbers in 2 unbound txt boxes. The OK button code then creates a temp table to create every number between them along with what they entered. I then update a master tbl with that data.... I would post the db but its huge. I have every street name for an entire state and have a tbl with these numbers reaching 500k. But this is my entire code behind my popup form....

Option Compare Database
Option Explicit

Private Sub btnOK_Click()
Dim rst    As DAO.Recordset
Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim v As DAO.Field
Dim i As Index
Dim tNamePresent As Boolean
Dim TableExists As Boolean
Dim lngI As Long
Set db = Application.CurrentDb

If IsNull(Me.txtStart) Then
MsgBox "You need a Starting number!"
Exit Sub
End If
If IsNull(Me.txtEnd) Then
MsgBox "You need a Ending number!"
Exit Sub
End If

Const strTableName = "TemptblROEAssignments"
' delete table if already exists
For Each tdf In db.TableDefs
   If tdf.Name = strTableName Then
            db.TableDefs.Delete strTableName
   Exit For
   End If
Next
'make table
Set tdf = db.CreateTableDef(strTableName)

Set v = tdf.CreateField("ID", dbLong)            'create field
v.DefaultValue = Me.Combo6                       'get its value
tdf.Fields.Append v                              'append field to table
Set v = tdf.CreateField("ROE", dbText)
tdf.Fields.Append v
'append table to database
db.TableDefs.Append tdf                      'append table to db


Set rst = CurrentDb.OpenRecordset(strTableName, dbOpenDynaset)
    
For lngI = CLng(Me.txtStart) To CLng(Me.txtEnd)
    With rst
        .AddNew
        .Fields("ROE") = Format(lngI, "00000000") 'the generated number format
        .Update
    End With
Next lngI
Set rst = Nothing
RefreshDatabaseWindow
DoCmd.RunMacro "mcrAssignROEs"   'runs the update query
End Sub
Private Sub Combo6_AfterUpdate()

Me.Frame21.Visible = True
Me.txtEnd.Visible = True
Me.txtStart.Visible = True
Me.btnOK.Visible = True

End Sub
Private Sub cmdExit_Click()
On Error GoTo Err_cmdExit_Click

    DoCmd.Close

Exit_cmdExit_Click:
    Exit Sub

Err_cmdExit_Click:
    MsgBox Err.Description
    Resume Exit_cmdExit_Click
    
End Sub

Private Sub Form_Open(Cancel As Integer) 'hiding things until choices are made
Me.Frame16.Visible = False
Me.Frame21.Visible = False
Me.Combo6.Visible = False
Me.txtEnd.Visible = False
Me.txtStart.Visible = False
Me.btnOK.Visible = False

End Sub
Private Sub Frame8_AfterUpdate()
Dim strSQL As String
Dim strSQL2 As String
Dim strSQL3 As String

strSQL = "SELECT ContractorID, CompanyName FROM tblContractors " & _
                "ORDER BY [CompanyName]"
strSQL2 = "SELECT StandID, StandAddress FROM tblStands " & _
                "ORDER BY [StandAddress]"
strSQL3 = "SELECT TeamID, TeamLeaderlName,  TeamLeaderfName FROM tblTeamLeaders " & _
                "ORDER BY [TeamLeaderlName]"

Select Case Me.Frame8
    Case 1
        Me.Combo6.RowSource = strSQL
    Case 2
        Me.Combo6.RowSource = strSQL2
    Case 3
        Me.Combo6.RowSource = strSQL3
    End Select
Debug.Print Me.Frame8

Me.Frame16.Visible = True
Me.Combo6.Visible = True
End Sub

Hope this helps and perhaps someone can help from here....

 

[spectrolab]

Thanks Sonny, much appreciated. It might take me a while to work through it, but atleast it gives me something to start on. Will post how it turns out!