Insert New Records with Range of Values

Friday

Friday

Registered User.
Local time
Today, 11:23
Joined
Apr 11, 2003
Messages
542
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?
 
I realize I could use two field, start_range and end_range, but I'm looking for the harder answer. :p
 
Yes, I have; a For...Next loop and RunSQL statement should do the trick:
Code: 
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.
 
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;
 
Won't both of these just add the same number to the table however many times the iteration runs?
 
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.
 
MrTibbs said:
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.
Click to expand...

Solly, Mr. Tibbs. I used your code and it works like a charm. Thanks mucho!
 
If you're only inserting a small number of rows at a time, the efficiency of the method isn't really relevant. However, if you need to insert a large number of rows of calculated data, you are better off using the DAO or ADO .AddNew Method to add the rows to a recordset. Remember that when you create a query as an SQL string, every time you run the query, Jet needs to parse it, validate it, and calculate an execution plan. So if you insert 100 records by running an SQL string 100 times, all that "compiling" needs to be done 100 times. This also contributes to database bloat.

Code: 
    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset
    Dim qd As DAO.QueryDef
    Dim intSeal As Integer
    
    Set dbs = CurrentDb
    Set qd = dbs.QueryDefs!YourQuery
    Set rst = qd.OpenRecordset
    For intSeal = Me.StartNum to Me.EndNum
        rst.AddNew
        rst!intSeal = intSeal
        rst!SomeField = Me.SomeField
        rst!SeriesDate = Me.txtToday
        rst.Update
    Next 
    Set rst = Nothing
    Set qd = Nothing

code has not been tested
 
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
 
I should have done this in my example. You might get even more speed if you create integer variables to hold the start and end values. The milliseconds add up in loops and it is faster to reference a local variable with a specific data type than a form control which is a variant.

Code: 
    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset
    Dim qd As DAO.QueryDef
    Dim intSeal As Integer
    Dim intSealStart As Integer
    Dim intSealEnd As Integer
    Dim strSomeField As String
    Dim dtSomeDate As Date
    
    Set dbs = CurrentDb
    Set qd = dbs.QueryDefs!YourQuery
    Set rst = qd.OpenRecordset
    intSealStart = Me.StartNum
    intSealEnd = Me.EndNum
    strSomeField = Me.SomeField
    dtSomeDate = Me.txtToday
    For intSeal = intSealStart  to intSealEnd 
        rst.AddNew
        rst!intSeal = intSeal
        rst!SomeField = strSomeField 
        rst!SeriesDate = dtSomeDate 
        rst.Update
    Next 
    Set rst = Nothing
    Set qd = Nothing
 
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.
 
You must log in or register to reply here.

Similar threads

C
Cant Update Record
Replies
19
Views
775
Pat Hartman
P
C
Checking if records exist
Replies
12
Views
896
cheekybuddha
cheekybuddha
Q
Solved Filtering range of records
Replies
19
Views
586
quest
Q
C
Getting a write conflict
Replies
6
Views
300
ChrisLeicester
C
N
How to deal with dynamic IP address in MS Access to MYSQL DB Maria
Replies
5
Views
410
MadPiet
M

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom