How do I add consecutively numbered records into a table?

[lilp32]

I am trying to add records into an Access 2010 table. Each time, I need to add 10 records (numbered 1-10). All other fields are the same for all 10 records.

The fields I am using are: SampleNumber (autonumber), SampleSite (always numbered 1-10), CollectionDate (date), StudyDay (number), ID (number).

For example, on CollectionDate 3/14/13, we collect 10 samples for ID 20. All samples have the same CollectionDate, StudyDay and ID but are numbered 1-10.

Ideally I would enter each static field plus 1 for SampleSite once and the program would loop through adding 1 to SampleSite until 10 records are created.

I've tried the INSERT INTO operation and have figured out how to copy the rows exactly but need help with the consecutive numbering. I imagine this would be done with a loop?

[Seth Schrock]

There are two ways you could do this, both involving a loop. Here is the basic framework.

Dim i as integer

For i = 1 to 10


    'Removed bad code
Next

This will loop through the code 10 times, each time adding 1 to the current value. Now you could either run an INSERT query inside the loop using the value of i to give you your 1 to 10 values. The second method would be to use a recordset and its .AddNew method. I'm not totally sure which one is better, but I would probably go with the second option. Here is a link that tells how to use it: MSDN Recordset.AddNe w Method. Let us know if you need help with it.

[Rabbit]

@Seth, you would not want line 6 in there. The for loop will automatically increment the variable.

[lilp32]

Thanks, but I think I am missing how to insert the variables through the SQL query.

Dim i As Integer
Dim CollectionDate As Date
Dim StudyDay As Integer
Dim ID As Integer

CollectionDate = InputBox("Enter date", "Date", Date)
StudyDay = InputBox("Enter study day")
ID = InputBox("Patient Number")
         
For i = 1 To 10
   
DoCmd.RunSQL "INSERT INTO tblSamples (SampleSite,CollectionDate,StudyDay,ID) VALUES (i, CollectionDate, StudyDay, ID)"

Next

[lilp32]

I got it to work!!!

Dim i As Integer
Dim CollectionDate As Date
Dim StudyDay As Integer
Dim ID As Integer
 
CollectionDate = InputBox("Enter date", "Date", Date)
StudyDay = InputBox("Enter study day")
ID = InputBox("Patient Number")
 
For i = 1 To 10

DoCmd.SetWarnings false
DoCmd.RunSQL "INSERT INTO tblSamples (SampleSite,CollectionDate,StudyDay,ID) VALUES ("& i &","& CollectionDate &","& StudyDay &","& ID &")"
 
Next

[Seth Schrock]

Duh. Can't believe I did that. I guess I'm too used to working with Do While loops.

[lilp32]

Now my problem is that the date is not being inserted correctly. I've tried changing the default date to Format(Now(), "m/d/yyyy") or removing it altogether but the date is still being inserted as 12/30/1899 or 12:00:09 AM.

[Seth Schrock]

Try using the Date() function instead of the Now(). If you continue to have issues with this, you should start a new thread.