Create Many Records

Create Many Records

Create Many Records

Create Many Records

Video 1 (5:16)

At last an opportunity to demonstrate Record Set Loops!!! I can see that this question “Generate in between numbers” (See below) is a good candidate for a demonstration, it has two simple tables. It’s very easy to understand what the OP wants to happen. It fits very well with my knowledge of Record-set Loops and For Next Loops. Question Originally Posted HERE:- Generate in between numbers

In this recent post “Generate in between numbers” the OP wanted to generate a varying number of Records depending on entries in a table. One table the Control Table, defines the first and last record of the set of Records required for each row in the control table. There is also additional information (UserName) which needs adding to the table.
 
This is the actual question:-
 
Generate in between numbers
I have ms-access table (A) as below, and I want a macro/VBA code that allow me to generate the in between serial number as shown in table (B).
Regards
 

Table (A)

IDNameSizeFromTo
1Sarah Umer313
2Tallia Noor245
 
Table (B)
 
IDNameSize
1Sarah Umer1
1Sarah Umer2
1Sarah Umer3
2Tallia Noor4
2Tallia Noor5
Table (A) is the control table and provides from/to record numbers these could be serial numbers for example. It also provides a name to add to the new table “Table (B)” and for some reason it states the size, which I guess is the number of records that will be generated.
 
If you look at the structure of Table (A) in the accompanying MS Access sample database I created to answer this question, you will see that I use a calculated field to generate this size number automatically.

 

Once you start converting queries into SQL Statements, in other words a string version of the Query, you can take that string and manipulate it, extract things from it, do all sorts of things with it! One of your best “leg-ups” one of your first moves to becoming an MS Access “Power User” should be to start writing simple VBA to Leverage your ability to construct SQL Statements in the query builder grid. The video and the accompanying zip file should give you a good idea of what can be done simply, quickly and easily by following a few Simple Rules.

The for next loop is one of the basic programming constructs and there is much information about them on the Internet. The record set Loop is a little bit more specific to MS Access although it can be used throughout the VBA environment and also other languages use something very similar if not the same….

Video 1 (5:16)

..             ….                ….                     ..                  ..                 . .                  …                   ….              ..               .               …             .. .          .. .. .                 ….

… …

Leave a Comment