Create Multiple records based on a table

[aromano789]

I have a given table with (StartNumber, EndNumber and StreetName)

Example of a single record in a table

StartNumber = 1
EndNumber = 50
StreetName = Main Street

Looking to get an end result of 50 records from that one example above.

1 Main Street
2 Main Street
.
.
.
50 Main Street


The original table has about 50 records. The end results from the original 50 records will be 10s of thousand records in a new table

I have no clue how to accomplish this and hope someone can help this novice

Thanks in advance

Tony

 

[pbaldy]

Basically:

1) Open a recordset on source table
2) Open another recordset on destination table
3) Start a Do While Not EOF loop on the source recordset
4) Start a For/Next loop using the start & end values from the current source record
5) Inside the For/Next loop, append a record to the destination table, using the street value from the main loop and the current value from the For/next loop

 

[aromano789]

Thanks Paul for the quick response

This is my first module ever. It works and does the job with no errors. Im looking to get it critiqued. Does anyone see any holes in the program or is there a better way?

Thanks

Option Compare Database
Function DontKnowWhatImDoing()
Dim rstInput As DAO.Recordset
Dim rstOutput As DAO.Recordset
Dim i As Integer
Set rstInput = CurrentDb.OpenRecordset("tblInput")
Set rstOutput = CurrentDb.OpenRecordset("tblOutput")

Do Until rstInput.EOF
For i = rstInput!StartNum To rstInput!endNum
rstOutput.AddNew
rstOutput!StreetNumber = i
rstOutput!Streetname = rstInput!Streetname
rstOutput.Update
Next i

rstInput.MoveNext
Loop

rstInput.Close
rstOutput.Close
CurrentDb.Close
End Function

 

[pbaldy]

I was going to say that was pretty good for a first effort, but frankly it's pretty good period. Some general thoughts. Every module (standard, form report...) should have Option Explicit at the top (IMHO). That can be preset in Tools/Options by checking Require Variable Declaration.

Many of us would use a variable for CurrentDb for use in opening the recordsets. That variable would be set to nothing (or closed) at the end, but I wouldn't do this:

CurrentDb.Close

Depending on how many records the target table has, you may see a performance increase by opening that recordset for append only, so it's not bringing back all the existing records.

Good job!

 

[aromano789]

Thanks again Paul

I will Implement your remarks from yoru last post. What is the code to open a record set for append only

Tony

 

[pbaldy]

I don't have Access in front of me, but look in help for OpenRecordset at the options. IIRC the option you want is dbAppendOnly. I think you can also open the recordset on an SQL statement that returns no records, but don't quote me on that.