Create Many Records

Recordset Loop - Create Records

Becoming an MS Access Power User 💪
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, and do all sorts of things with it! One of the best stepping stones towards 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 accomplished simply, quickly, and easily by following a few simple rules. 📚🎓

Learning about Loops in Programming 🔄
The ‘for-next’ loop is one of the basic programming constructs, and there’s much information about it on the internet. The ‘recordset loop’ is a bit more specific to MS Access, although it can be used throughout the VBA environment, and other languages use something very similar, if not the same. 🌐

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)

ID	Name	Size	From	To
1	Sarah Umer	3	1	3
2	Tallia Noor	2	4	5

Table (B)

ID	Name	Size
1	Sarah Umer	1
1	Sarah Umer	2
1	Sarah Umer	3
2	Tallia Noor	4
2	Tallia Noor	5

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. Download the File:- Create Many Records – Nifty Access

Video 1 (5:16)

Leave List as per Date Range

A similar question was posted on Access World Forums — Leave List as per Date Range recently and Jack came up with a SQL method of manipulating the data. I thought it was very interesting and might provide some insight into doing it with this or a similar example at least! Hence I have copied the code and posted it here!

				
					SELECT Leave.LeaveAppliedOn
, Leave.LeaveAppliedTill
, Leave.EmployeeName
FROM Leave
ORDER BY Leave.LeaveAppliedOn;

More Info from the Access World Forums THREAD — Leave List as per Date Range Below is the SQL from June’s post, she indicates that the SQL method might be slower than the VBA method. I refer you to the AWF thread for further information.

				
					PARAMETERS [StartDate] DateTime, [EndDate] DateTime;
SELECT DISTINCT Format([StartDate] - 1+ 100*Abs([Hundreds].[id] Mod 10) + 10*Abs([Tens].[id] Mod 10)+Abs([Ones].[id] Mod 10)+1, "mm/dd/yyyy") AS MDY, Format([StartDate] - 1+ 100*Abs([Hundreds].[id] Mod 10) + 10*Abs([Tens].[id] Mod 10)+Abs([Ones].[id] Mod 10)+1, "yyyy q") AS YearQtr, 1 AS Data
FROM MSysObjects AS Ones, MSysObjects AS Tens, MSysObjects AS Hundreds
WHERE [StartDate] - 1+ 100*Abs([Hundreds].[id] Mod 10) + 10*Abs([Tens].[id] Mod 10)+Abs([Ones].[id] Mod 10)+1 Between [StartDate]-1 And [EndDate];

… …

Recordset Loop - Create Records