Add X number of rows to table

[btappan]

What is a quick and easy way to add X number of rows to a table? I'd like to make a query that opens a prompt asking how many rows I need to add, and then it adds the rows numbered as 1 through whatever value I put in the prompt. My table just has 2 fields, pNAME and ID. The ID is autonumbered. pNAME will be populated with the numbers. This is so I can add say 10 rows numbered 1-10, then come back and paste in the actual 10 product names over those numbers. Not ideal I know, but what I need to do for the moment.

[Micron]

To keep it simple I'd write a small procedure (sub) that will append the values 1 to n. The sub would simply execute the sql statement n times but increment the value being appended each time. You could run the sub from the immediate window of the vb editor or perhaps a form.

I'm just answering what you've asked for since it seems to be temporary. I expect you'll get questions as to why you are doing things this way.

[Welshgasman]

I was thinking just as quick to add them manually, as edit inserted records?

[Micron]

I have had a code window open all day so I'll post what I was playing with then shut it down.
Version 1 - has error handler, uses append sql statement to append 1 to n as OP asked for:

Sub AppendRange(n As Integer)

Dim db As DAO.Database
Dim sql As String
Dim cnt As Integer

On Error GoTo errHandler
Set db = CurrentDb
Do Until cnt = n
  sql = "INSERT INTO table1 (field1) VALUES(" & cnt + 1 & ")"
  db.Execute (sql)
  cnt = cnt + 1
Loop

exitHere:
Set db = Nothing
Exit Sub

errHandler:
MsgBox "Error " & Err.Number & ": " & Err.Description
Resume exitHere

End Sub

Version 2 - no error handler, adds to a recordset but does so from x to y

Sub AppendRange2(lngStart As Long, lngEnd As Long)

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim intX As Integer

Set db = CurrentDb
Set rs = db.OpenRecordset("SELECT * FROM Table1 WHERE 1 = 0")

For intX = lngStart To lngEnd
   With rs
      .AddNew
      .Fields("Field1") = intX
      .Update
   End With
Next
rs.Close

Set db = Nothing
Set rs = Nothing

End Sub

Version 2 could also be used with 1 to n as in the first example.

Certainly there are other ways which might be forthcoming.

@WGM I wondered the same thing but only briefly. I once had a situation where a certain amount of info had to be entered to start something but the final Indexed no-dupes value could not be entered until Purchasing provided it. So having to go back to finish something is sometimes necessary; maybe it is so here, maybe not.

[Welshgasman]

@WGM I wondered the same thing but only briefly. I once had a situation where a certain amount of info had to be entered to start something but the final Indexed no-dupes value could not be entered until Purchasing provided it. So having to go back to finish something is sometimes necessary; maybe it is so here, maybe not.

I could understand that, but OP said only two fields.?
Having entered 4000 Crew names into my first DB, a tab was all it took?