Insert New Records with Range of Values

[Friday]

I searched on this and found a few posts close but not close enough. Our company purchases container seals. These seals are numbered, and always delivered within a range. What I want to be able to do is allow the user to enter a beginning and ending seal number and have VBA insert that many new records, with one field reflecting the seal number for each seal. The user will also enter the date purchased and what stations these seals were shipped to, so we can later track where each series is being applied at. I know how to inert the new record, and how to determine how many records to insert, but I can't figure out how to do the range. Anybody out there done something similar?

 

[Friday]

I realize I could use two field, start_range and end_range, but I'm looking for the harder answer.

 

[Dugantrain]

Yes, I have; a For...Next loop and RunSQL statement should do the trick:

Public Function fInsert_Nums(numStart As Long, numEnd As Long)
DoCmd.SetWarnings False
Dim i As Long
For i = numStart To numEnd
DoCmd.RunSQL "INSERT INTO tblEXAMPLE (Num_Field) VALUES (" & i & ")"
Next i
DoCmd.SetWarnings True
End Function

Of course, figuring out how to pass in the number arguments from your form, validating the numbers, etc. will be necessary.

 

[MrTibbs]

Try a vba loop ... hopefully close enough

Option Compare Database
Option Explicit

Public Sub AddSealRange()
Dim intSeal, intStart, intEnd As Integer
Dim strDesc, strSQL As String

intSeal = 0
strDesc = "demo for my purposes"
intStart = 0
intEnd = 400

For intSeal = intStart To intEnd
DoCmd.SetWarnings False
strSQL = "INSERT INTO qrySeal " & _
" VALUES ( " & _
intSeal & ", " & _
Chr(34) & strDesc & Chr(34) & ");"
DoCmd.RunSQL (strSQL)
DoCmd.SetWarnings True


Next

End Sub

' SQL of query to hide table behind ....
' SELECT tblSeal.intSeal, tblSeal.chrDesc FROM tblSeal;

 

[Friday]

Won't both of these just add the same number to the table however many times the iteration runs?

 

[MrTibbs]

No. intseal increments

The example runs an SQL Insert for each value of intSeal in the range specified. Try it. I know it works. I built an Access '97 database to prove it before posting.
If you are really desperate I can email you the database and you can try it. Send me a message with your email address.
You get 401 records showing integers between 0 and 400 inclusive.
The string description does stay the same in the example supplied.

 

[Friday]

The example runs an SQL Insert for each value of intSeal in the range specified. Try it. I know it works. I built an Access '97 database to prove it before posting. If you are really desperate I can email you the database and you can try it. Send me a message with your email address.
You get 401 records showing integers between 0 and 400 inclusive.
The string description does stay the same in the example supplied.

Solly, Mr. Tibbs. I used your code and it works like a charm. Thanks mucho!

 

[MrTibbs]

Thanks. DAO vs SQL

Pat,

Thank you for posting the DAO example. I needed to do more than 100 line by line appends last week [for the first time in several years] and the SQL code walked, rather than ran. I will recode using DAO then post a comparison.
...
Comparison complete. DAO ran in 14 seconds, SQL ran in 25.
[started with identical databases, replaced only the SQL code. Imported the same 1122 records to both databases].
Database size was not affected in my examples.
...
I am using Access '97

 

[MrTibbs]

How to recreate my example

As sent to Brian Kitka off-line when my attempts to email the database were not successful (my default db security is too good .
Using these notes anyone can re-create the example database and prove looping increments work.
They can then add Pats notes above to show that DAO is faster than SQL inserts.

Try the following:

Create an empty database
Create a table
tblSeal

Containing 2 fields
intSeal long integer
chrDesc Character 255 char

Create a new Query (in SQL view )
----
SELECT tblSeal.intSeal, tblSeal.chrDesc
FROM tblSeal;
----

Create a new module
Copy the code snippet below into that module

---
Public Sub AddSealRange()
Dim intSeal, intStart, intEnd As Integer
Dim strDesc, strSQL As String

intSeal = 0
strDesc = "demo for my purposes"
intStart = 0
intEnd = 400

For intSeal = intStart To intEnd
DoCmd.SetWarnings False
strSQL = "INSERT INTO qrySeal " & _
" VALUES ( " & _
intSeal & ", " & _
Chr(34) & strDesc & Chr(34) & ");"
DoCmd.RunSQL (strSQL)
DoCmd.SetWarnings True


Next

End Sub
---

Run the module
To see the table grow.

Congratulations: You have just re-created my test database in your own
version of Access and with your own security workgroup.