Adding Records to bottom of Database incrementing Id by 1

[NBVC]

Hi,

I am trying to update my ERP database by appending records.

The records I want to append must start at a certain number (Part Id)...let's say 20101 and end at 20200. I need them to increment by 1.

I also need other fields to populate with a specific text string

I think I need to use Append Query, but can't figure out how to do the Part ID autopopulations.

Thanks for any advice.

 

[NBVC]

No,

I am trying to add a group of records all with exactly the same data except the part number which would be in column 1 (field 1). That part number must start at a specific number and end at a specific number and increment in between by 1.

 

[georgedwilkinson]

In an auto parts store, you might get a new line of, let's say, screws. You need to input them into the system but don't want to type in the details of each and every screw, potentially several thousand part numbers. So you "bulk" load them to save data entry time/money. Same principle applies to many industries. Later, you could use the UPC on the boxes to "fix" or customize some of the data.

 

[georgedwilkinson]

It works a little differently in a retail auto parts store (not many bins sitting around). And my screw example was just an example.

If the company got a new line of belts, for instance. The part numbers are very uniform but the system administrator (usually the store or warehouse manager) doesn't want to type each and every belt in the new line into the database. As employees touch each "belt", they could type in the dimensions or other custom data. But initially, you just gotta get a place holder so you can take shipment (typically all the more popular items) and start selling, recording lost sales, re-ordering, etc.

 

[NBVC]

In my case the parts are being "reserved".. This way as engineers design machines, they can take a group of part numbers and use them (change the particulars). The part numbers will be consecutive and therefore tidy when they create their bills of material.

 

[raskew]

Hi -

This query, modified to your table and field names, will add 100 records numbered 20101 to 20200 to your table.

In this example, tempTest7 is the working table. TempTest8 is a copy of tempTest7, populated with just
one record containing the data you want repeated in each of the 100 records. PlayerNo is your number field,
to be populated with 20101 to 20200.

CAUTION: Do not attempt to copy from your working table to your working
table. It will create a monster! Trust me, I know.

Public Sub DupRecord()
Dim db       As Database
Dim strSQL As String
Dim n        As Long

Set db = CurrentDb
For n = 20101 To 20200

strSQL = "INSERT INTO tempTest7 (PlayerNo, LastName, Initials, DOB) " _
       & "SELECT " & n & ", LastName, Initials, DOB " _
       & "FROM tempTest8;"

db.Execute strSQL

Next n
End Sub

Please post back.

Bob