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. ๐ŸŒ

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

ย 

Video 1ย (5:16)

Nifty Access YouTube Thumb Nail
Play Video

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];
				
			

… …

More Useful Stuff HERE:-

Leave a comment

This website uses third-party software - WordPress Add-Ins to be exact. I don't know what any individual add-in does, but I'm sure that many of them collect information about you. So be aware, if you continue using this site, then you are likely to be sharing your information. I don't know how to disable this sharing for any, or all of the plugins for individual users. So I can't stop the sharing of information. If this worries you then please do not use this site... If you continue to use this site I will assume that you are happy with it.

Do you need a hand in the right direction?

You are in the right place.