Rank with VBA

2024-09-092018-07-09 by Ui1cna78fckJ5aFJ5UOzV Ui1cna78fckJ5aFJ5UOzV

Rank with VBA

With the Google presentation and a video below, I demonstrate how to change the rank of an item in a table. This is particularly useful if you have a lookup table and you want to offer your user the ability to move an item up or down the list. To add this feature you need to add a new field which stores a value you can change. Sort the list ascending so that the preferred option selected by the user comes to the top. See the presentation and video together for further information.

I developed this code for the Nifty Switchboard Builder. The page data is controlled by an index which prevents duplicate values. This causes a problem when you want to move one of the switchboard page rows up or down. Let’s say you want to move record number 5 to record number 4, you can’t because you can’t change 5 to 4 because this will trigger the duplication prevention provided by the unique index. The solution is to move it to a row number not within the set of row numbers. You could invent an arbitrary figure saying 1000, however you must be sure that this 1000 will not be used by some other process.

Rank with VBA

Video 1 (7:06)

This 1st video follows along from the presentation below. The presentation goes over the basic idea, then the video goes into details about how to construct the queries required to effect the ranking. In this video I show the next step, which is to create the queries which move people up and down in a list with VBA… In the second video of (not published yet) I will show how to convert the queries into SQL string and run them with VBA.

Video 1 (7:06)

Presentation as PDF Here:-

… …

More Useful Stuff HERE:-

Sign Up to Get Nifty News

Value

2024-09-092018-06-29 by Ui1cna78fckJ5aFJ5UOzV Ui1cna78fckJ5aFJ5UOzV

Use "Value" or Not?

I’ve noticed a couple of minor glitches caused by the default of a ‘textbox’, the value; not being assigned explicitly. It’s not a big problem and I don’t think it will affect many people, possibly nobody! However I think there’s a good blog in it for someone, possibly me, when I’ve got the time, or someone else might pick it up and carry it…

Not done any research on this. I’m just recording instances where I notice a problem. Hopefully I, or someone else will have time to investigate more thoroughly. The first time I noticed the problem was when converting a macro into VBA code. See my Blog about it HERE:-

TempVars Value Error

I think it was the macro from the sample Northwind database, and in particular it was in the login form. If my recollection is correct, the conversion assigned a textbox value to a TempVar. The problem I noticed was that it tried to assign the textbox as control, therefore it did not automatically extract the value from the textbox it tried to assign the textbox as a control which caused an error.

I considered this a minor glitch and would very seldom cause anyone a problem and put it to the back of my mind.

If you’re not familiar with the Northwind Sample Database, then I’ve done a quick video demonstrating how to install and use it here:- Setup the Northwind dB

However whilst researching

“DoCmd.RunCommand acCmdSaveAsOutlookContact”

which I also found in the Sample Northwind Database.

I took a particular interest in it because it was a command I hadn’t come across before.

Whilst researching it, I ran across this thread:- Save as Outlook Contact Problem

Which harkens back to a similar error with the value of a textbox. Hence I’m putting two and two together and possibly making Five> I note some similarity between my two observations and thought it’s worth recording…

My thinking is if there is any possibility of your textbox value being assigned to a control variable, then you must use value explicitly. In some ways this sentence doesn’t make much sense! I think it’s just my paranoia…. possibly… who said that?

… …

More Useful Stuff HERE:-

Sign Up to Get Nifty News

Subform – Time Management Matrix Example

2024-09-092018-06-02 by Ui1cna78fckJ5aFJ5UOzV Ui1cna78fckJ5aFJ5UOzV

Time Management Matrix

This example demonstrates how you can can use “Instances” of a Form multiple times. You place the copies on the main form as “Sub-Forms” It’s an interesting way of programming, and with this technique you can quickly develop Forms with exceptional properties and uses with very little coding…

Subform Example

Video 1 (3:50)

Regarding multiple copies of the same form, I do understand this method of building databases, that is creating duplicates of the same form with minor alterations to serve a slightly different functions. And it is probably an acceptable process when you are designing, however you should endeavour to use the same form many times. Instead of creating multiple duplicates with slightly different functionality, what you do is add the different functionality with VBA code. This is demonstrated in the video on Subforms. The video shows a main form that appears to have 4 subforms on it, however it only has one subform modified with VBA code to perform four slightly different functions. This idea is demonstrated in the form of a Time Management Matrix. I give a brief explanation of the Time Management Matrix. I show how to add a new task. I also demonstrate how to move tasks between the different sections. I give a quick review of the VBA code in the module behind the form.

The “Time Management Matrix” sample dB used in this Video is available for Download Here:-

Video 1 (3:50)

… …

More Useful Stuff HERE:-

Sign Up to Get Nifty News

Customer Products List in Northwind

2024-09-092018-05-30 by Ui1cna78fckJ5aFJ5UOzV Ui1cna78fckJ5aFJ5UOzV

Combo Box - Products by Customer?

Jada Docs – commented this YouTube Video:-

Northwind Orders Combo Box 3 – Nifty Access

Thank you for the awesome video. Do you have any videos that show how the create a combobox which shows the products purchased by each customer? If so, please send me the link.

It’s a very common requirement to have a list of items filtered and displayed within your MS Access database. Most people go about this by creating a query, however the problem with a query is that it’s a very awkward thing to display. It’s not interactive, or usable. Queries are a very good for supplying information to forms and reports but for displaying information they are lacking.

Customer Products List

Northwind - Customer Products List

Video 1 (12:27)

The solution is to use your query as the record source of a form, have that form showing the data Returned in datasheet view. Now place that form on top of another form as a subform. Now you can add controls to the form you have placed your query on and easily control the records Returned. You can also select one of the Returned records and open a form displaying more information about the selected record.

I have produced the video – Video 1 (12:27) in answer to Jada’s question. I show how to create a query, how to control the results Returned by the query, how to create a form to display the results returned by the query. I show how to place that form on another form, often called the main form “frmMain”. I demonstrate how to link a combobox to the query so that you can control the records Returned. I demonstrate how to refresh the data shown in the subform window. I explain how the subform is not actually on your main form but contained within a subform/subreport control which I referred to as a subFrmWin (subform window). I recommend saving a text version of the query somewhere within your form so that if a user happens to damage, or lose the query, it’s quite an easy process for you to repair your database.

Video 1 (12:27)

More Info on th Northwind Sample dB:-

Find more Northwind Blogs in the Menu Below:-

… …

Northwind Customer Phone List

Video 2 (2:46)

Your question reminded me of a Form I know about from an early version of the Microsoft Northwind Sample database. (DOWNLOAD HERE) The Northwind database is a database provided by Microsoft to demonstrate the functionality available in Microsoft Access. It’s for demonstration purposes only, and you shouldn’t use it for doing accounts, not unless you change some things as described on my website here:- Northwind Problem and Solution In the YouTube Video I show the Form, and I point out a feature that I think might be useful to you and that is an Option Group, but not a normal Option Group, it’s an option group containing letters (Toggle Buttons). A very clever idea. as many of the features demonstrated in the Northwind Sample database are. They are well worth exploring to see what you can do with Microsoft Access and I recommend everyone should download your own copy to peruse.

Find more Northwind Blogs in the Menu Below:-

Video 2 (2:46)

… …

More Useful Stuff HERE:-

Sign Up to Get Nifty News

Search Criteria YouTube Bullet Points

2024-09-092018-01-03 by Ui1cna78fckJ5aFJ5UOzV Ui1cna78fckJ5aFJ5UOzV

YouTube Bullet Points

Total watch time now is about 25 minutes if you (Run the Video’s at Double Speed) Now that’s not too much of your precious TIME to assimilate the information that these excellent videos provide. And a Bonus! I’m currently available to help you with any enquiries, just press the email button on this Webpage and I will get back to you, usually within 24 hours…

Find Your Level!

1	Search Criteria 1 Nifty Access	sXkIKpMpuWs	00:03:48
2	Search Criteria 2 Nifty Access	GOAOXvMAX00	00:05:29
3	Search Criteria 3 Nifty Access	Cjzj1HWfFR0	00:07:42
4	Search Criteria 4 Nifty Access	i8Xg9hoonrM	00:06:46
5	Search Criteria 5 Nifty Access	uZd7oxAqO6o	00:01:37
6	Search Criteria 6 Nifty Access	ERbIkuI_6ws	00:03:38
7	Search Criteria 7 Nifty Access	EmjuUp8GM08	00:02:00
8	Search Criteria 8 Nifty Access	WfKPa0AMZYk	00:05:56
9	Search Criteria 9 Nifty Access	P_oGYihkq7k	00:06:06
10	Search Criteria 10 Nifty Access	-Ll3nxtpjOk	00:00:53
11	Search Criteria 11 Nifty Access	Gg5xppUNkLo	00:04:35
	Total Time	(Approx)	47 mins

Search Criteria 1 Nifty Access

00:28 Basic command button calling a message box
00:36 Always have the messagebox transfer something so you can see it’s working
00:50 Have a module containing the constants, things like project name, your telephone number
01:22 Make sample text available by adding it to your Module, and commenting it out
01:27 Shorten the SQL Statement by removing the superfluous Table Name
01:40 Select the table name and the Dot (tblMain. NOT tblMain)
02:05 Demo of how to insert a “Function Template”
02:55 Shows the use of the constant “conAppName”
03:15 Always bring your latest code to the top of the module
03:30 Append the Function “fSQLX” to the message box statement with an ampersand

Search Criteria 2 Nifty Access

01:15 simplify the look of the code
01:25 make patterns where you can
03:10 build an SQL Statement (a string)
03:20 An SQL Statement is a string version of a Query

Search Criteria 3 Nifty Access

00:22 How to insert a template file
00:40 Bring your most recent code to the top of the Module
00:53 Don’t add them at the bottom
00:57 The code window gets stuck forcing you to Type right at the bottom
01:20 keeping the code at the top allows you to quickly identify where you got to with your work
04:04 Too many spaces is not necessarily a bad thing in SQL Statements
04:30 Use an ampersand “&” for concatenation of strings
04:47 Type Mismatch Error Number 13
05:08 Adding a breakpoint to your code
05:14 Code execution stops at the breakpoint
05:20 Code execution position highlighted in yellow
05:28 Press F8 to allow the Code to pick one more step
06:04 Line where the error occurs highlighted in yellow
06:07 The error causes the code to skip to the Error_Handler:
06:15 The error code is checked against the Error_Handler: Select Case for a match
06:19 There is no predefined error handler to catch the error so it goes to Case Else
06:23 Message box displays the error description and the error number
06:46 Clear the breakpoint
06:50 Cycle through the code and find where it breaks
06:52 Reason for error identified, trying to multiply two strings together
07:18 These damn “Quotation Marks”

Search Criteria 4 Nifty Access

00:15 Remove the multiplication sign and make it into a string
00:18 Make your code as simple as possible right from the beginning
03:00 I’m breaking the naming convention Here!
03:15 Use character 34 Chr(34) to replace the speech mark – quote mark
03:40 explanation of how quotes and Chr(34) will appear correctly in your “text” string
04:20 Display the string variable in a message box see see what it actually contains
06:05 how to rename a function with “Replace”

Search Criteria 5 Nifty Access

00:27 Make a copy of a function
00:40 Rename it to avoid clashes
00:46 Note Replace set to “Current Procedure”

Search Criteria 6 Nifty Access

01:42 If you make a mistake, you can go back a step With “Undo”
02:10 Don’t forget to break things up into logical components

Search Criteria 7 Nifty Access

00:26 Find and Replace
00:30 Make sure you’ve got “Current Procedure” selected
00:40 Don’t forget to break your Code into smaller units
01:10 Demonstration on a “Compile Error”

Search Criteria 8 Nifty Access

00:30 Build a SQL Statement by concatenating the functions together
01:15 We’ve got an “AND” on the end that needs removing
01:57 Get the length of a string
02:22 Get the leftmost characters of a string
02:40 Now we have removed the “AND”
02:54 Grab the context contents of the message box with “Ctrl Copy”
03:00 Place the message box contents in a Query
03:15 Create a real Query
03:20 Create a Query in the Query Designer Grid
03:29 MS Access adds “Like” automatically
03:38 How to get the text version of a Query
04:03 Look at the SQL view again, (the text version of the Query)
04:40 Compare the VBA generated SQL Statement
04:55 Against the SQL Statement produced with the Query Builder Grid
05:04 Identify the errors and correct them in the VBA Code

Search Criteria 9 Nifty Access

00:09 General adjustments to the SQL string
00:15 Add the bracket to the WHERE Clause
00:25 Add a space to the “SELECT FROM” Statement
00:52 Need to remove the space from the Surname String
01:17 Remove one of the speech marks from the Star
01:28 Do this by removing the ASCII code Chr(34)
01:57 Change the star “strSTAR” to a left star “strSTARL”
02:05 Create a right star “strSTARR”
02:25 Don’t forget to amend your code to the new String Variables
02:28 Update the Default Value of the text boxes to reflect the table contents
03:30 Copy the contents of the message box with the key combination “Ctrl C”
03:43 Compare the VBA generated SQL Statement against the Actual SQL Statement
04:10 Tidy up a bit by removing redundant comments
04:35 Compile error due to missing Ampersand “&”
04:42 Add the missing Ending Bracket
05:00 Copy the contents of the message box with the key combination “Ctrl C”
05:02 Put the VBA created SQL Statement straight in a Query and test it
05:10 There you go! The query runs a treat, returning the expected Records
05:12 Extract the text version of the Query
05:18 Paste it into the form module and compare it with the sample SQL Statement
05:26 A couple of minor problems with extra Spaces – easily resolved!
05:36 MS Access SQL Statements usually run OK with Superfluous Spaces
05:44 Solved the extra space on the End by removing 5 characters instead of 4!

Search Criteria 10 Nifty Access

NO Bullet Points!!!

Search Criteria 11 Nifty Access

00:24 How to create a subform – Select the Table you want to base the Form on
00:26 How to create a form based on a table
00:26 Select the Create Tab
00:27 Click on the button marked “Form” in the Forms section
00:29 Open the newly created Form in design view
00:30 How to change the form to datasheet view
00:32 Open the Forms property sheet
00:34 Select the “Format” tab and change the Forms “Default View” to Datasheet
00:45 Save the Form with a name identifying it as a Subform “sFrmMain”
00:50 Open the original Form “Form1” in “design view”
00:55 How to Drag a subform on to a Main Form
01:00 Drag the Subform “sFrmMain” onto the Form “Form1”
01:11 The subform displays all the Records
01:17 Now we need to modify the newly added subforms “RecordSource” with VBA code
01:19 Select the subform/subreport Control which houses the subform
01:23 This is a Subform/Subreport Control (Subform Window)
01:30 the source object of the subform subreport control is the Subform “sFrmMain”
01:48 This is it’s RecordSource
01:50 Subform “sFrmMain” has a RecordSource
01:54 Open Subform “sFrmMain” in design view
01:56 Open the Subforms property sheet
01:57 The Subform “sFrmMain” RecordSource is set to “tblMain”
02:05 We need to modify the subform RecordSource with VBA code
02:15 The subform is housed in the subform/subreport Control I call it a “subFrmWinFORMNAME”
02:18 This is the Sub Form Window (Subform/Subreport Control)
02:21 The default name for the subform/subreport Control is the name of the Form it houses
02:22 Access gives the Subform/Subreport Control a Confusing Name
02:25 The default name in this case is:- Subform “sFrmMain”
02:37 I like to think of it as a window, and name it thus:- “subFrmWinFORMNAME”
02:38 Immediately change its name to something sensible!
02:43 I named it:- “subFrmWinFrmMain” I should have named it “subFrmWinSfrmMain”
03:11 VBA code to modify the subform RecordSource
03:15 Set the subform RecordSource with VBA code
03:25 Me.subFrmWinFrmMain.Form.RecordSource
03:50 Demo of the RecordSource of the sub-form being set with VBA code
04:00 Demo testing different search criteria
04:10 Test to see what happens when there are no matching records
04:26 Possible improvement! Add a reset button

… …

More Useful Stuff HERE:-

Sign Up to Get Nifty News

Build an Advanced Search Form

2024-09-092017-12-29 by Ui1cna78fckJ5aFJ5UOzV Ui1cna78fckJ5aFJ5UOzV

Why Build a Search Form?

I answered This question:- Multi field search-box on Access World Forums (AWF) where the OP asked about using a list box to search a table.

I didn’t think a LISTBOX was the right solution, so I demonstrated this “Nifty Search Form” by adapting it to the OP’s Database. All done in a Few minutes! You can download your own copy of the search form here:- https://gum.co/NiftySearchForm — alternatively, follow the video instructions and you can build your own!

My Reply:- (The Key take away from my Reply — I found it an exceptionally rewarding way of learning Microsoft Access VBA)

I did it as a project to help me learn how to program Microsoft Access VBA… My first attempts at a Search Form were very basic, but they pointed the way to making improvements. As I made each improvement, there were 3 levels in all, my skill and understanding of programming in Microsoft Access VBA improved immensely. The result was this Search Form, which I am very proud of!

If you’re interested in learning Microsoft Access VBA then I can think of no better way than to adopt a project. Indeed, I realised the value of what I had learnt and I have tried to to explain it and to convey what I learnt in three collections of YouTube videos demonstrating how to build your own Search Form.

There are three sets of YouTube videos because they cater for people with different levels of expertise in MS Access VBA. Some might start at the first set of videos and some may start at the last set of videos.

The “Nifty Search Form” evolved out of many months of experimentation and learning.

I’ve condensed my experience down into these 3 sets of YouTube videos:-

VBA Beginner

Want to build yourself a Search Form?

Building Advanced Search Criteria

If you are interested in learning VBA then I can’t think of a better way than following the sets of YouTube videos.

Following them will give you a good “leg up” in this learning process. You’ve also got the back up that you can contact me if there’s anything you need help with.

Build an Advanced Search Form

Advanced Search Form - Full instructions on how to Build it Yourself!

If you are interested in building a “Nifty Search Form” for your database, then you should find the following YouTube Playlists helpful.

I have composed 3 YouTube playlists which go through a process of building the Search Form — Building Search Criteria – Nifty Access, Advanced Search Criteria – Nifty Access and Easy Search Criteria – Nifty Access

You will need some experience in using the query builder grid and VBA. If you need help with the VBA I also did another set the videos showing you how to start out with VBA…

VBA Beginner – Nifty Access

https://niftyaccess.com/vba-beginner/

Building Search Criteria

https://niftyaccess.com/building-search-criteria/

Building Advanced Search Criteria

https://niftyaccess.com/building-advanced-search-criteria/

Easy Search Criteria – Nifty Access

https://www.youtube.com/playlist?list=PLhf4YcS5Ajdq0vg0GgDZv8pVb9e9miLYI

If you have any problems, email me a time indexed link, to the place in the video that you need help with. Also provide an Explanation of the problem you are having, and/or what you don’t understand.

… …

More Useful Stuff HERE:-

Sign Up to Get Nifty News

Acronyms – MS Access

2024-09-092017-12-09 by Ui1cna78fckJ5aFJ5UOzV Ui1cna78fckJ5aFJ5UOzV

MS Access - Acronyms

Easy MS Access Checklist

2024-09-092017-12-08 by Ui1cna78fckJ5aFJ5UOzV Ui1cna78fckJ5aFJ5UOzV

You need an Auto Fill Checklist

I added loads of Checklists to a Project. I noticed that it was painful for the user’s because they had to select each combo-box, choose the question, then choose the answer. This was a painfully slow process, and worse, it resulted in errors and missing information! These are the biggest NO NO’S for any database developer! The Video below demonstrates the problem, and below that, there are links to to my solution which is a set of Videos. The purpose of the videos is not to supply you with an easy drop in answer, it’s to get you to learn VBA! However if you want a drop-in answer, with no work, contact me. I have some samples that I can share.

Checklist Required!

Video 1 (1:27)

I offer a solution to a problem which might, sort of, sneak up on you when you’re developing your database. Imagine this scenario. Your organisation helps disadvantaged families and received support from government and other organisations. One of the requirements of this support is an annual report on how many people have been helped by your organisation. You’ve been using Excel for this however it’s becoming apparent that Excel doesn’t have all the features you require. You turn to MS Access to emulate a form provided by the government, and it has lots of checklists on it. The main form has a section for collects information on the families, family name, address, etc… under this section you have set up a subform which offers the user a set of combo boxes where they can choose a question and provide an answer. In other words the user interface is awful! A user interface requiring the operator to take several steps on each and every entry, unnecessary steps, something crying out for automation! Instead of the operator manually choosing the question and providing the answer, you would like all the combo boxes to be filled automatically! Hence my Checklist solution… See how to automatically create a Checklist on my website HERE:-

How to add a Checklist to MS Access

This Advanced but simple to use Checklist is easy to set-up. You Just need 3 Tables, a main form and a Subform. Follow the comprehensive Video Instructions, which demonstrate how to add this incredible utility to your database. Establish yourself as the “Go To” person in your organisation for database improvements! “Nifty Access” will quickly elevate you to “Power User Level!”

Video 1 (1:27)

… …

More Useful Stuff HERE:-

Sign Up to Get Nifty News

Recordset Loops How To

2022-06-302017-11-29 by Ui1cna78fckJ5aFJ5UOzV Ui1cna78fckJ5aFJ5UOzV

Recordset Loops

I recently answered a question on my Facebook site… Link:- Full Text of my Answer:- basically it would be best to use a record set loop to solve the OP’s problem.

I agree with Sherry, you need to use a Recordset Loop… I did a set of video instructions on how to create a Checklist in an MS Access database.

One part of the video covers the record set loop. If you have a look at this video:- 1 – Adding a Checklist to MS Access at this time index:- https://youtu.be/r1y_Zl3uLkM?t=1m12s

The three videos below are actually three separate time indexes of the same video, time indexes to each place in the video relevant to the question.

The Two Functions

Video 1 (Time Index 72 to 95)

This Video clip gives you an overview. The video discusses the function on the left, which is something similar to What You Want. Move on through the video to this time index:- https://youtu.be/r1y_Zl3uLkM?t=1m49s

Video 1 (Time Index 72 to 95)

… …

The Function "fCopyListToData"

Video 2 (Time Index 109 to 150)

In this video clip, I discuss the operation of that function which is generally termed a “Recordset Loop” If you go to the video at this time index https://youtu.be/r1y_Zl3uLkM?t=3m20s

Video 2 (Time Index 109 to 150)

… …

Extracting the Row Information

Video 3 (Time Index 200 to 250)

That’s the bit you need to change, listSets and listID refer to fields in the record set passed into the record set loop. You need to provide your own SQL statement based on your own tables and then change listSets and listID to the particular fields in the tables from your project you have passed in then check the contents of those variables which represent the fields past in. With regard to deleting a row, off the cuff so to speak without attempting to do it myself, I think I would add an extra field to your table, a boolean field, and write the code to flag the field(s) you want to delete.

Video 3 (Time Index 200 to 250)

Complete set of videos along with a download of the sample database are available on my website here:- Add a Check List to your MS Access Database

… …

More Useful Stuff HERE:-

Sign Up to Get Nifty News

Create Many Records

2023-11-012017-11-28 by Ui1cna78fckJ5aFJ5UOzV Ui1cna78fckJ5aFJ5UOzV

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

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

… …

Rank with VBA

Rank with VBA

Rank with VBA

Video 1 (7:06)

Video 1 (7:06)

Presentation as PDF Here:-

More Useful Stuff HERE:-

Use "Value" or Not?

More Useful Stuff HERE:-

Time Management Matrix

Subform Example

Subform Example

Video 1 (3:50)

Video 1 (3:50)

More Useful Stuff HERE:-

Combo Box - Products by Customer?

Customer Products List

Northwind - Customer Products List

Video 1 (12:27)

Video 1 (12:27)

More Info on th Northwind Sample dB:-

Find more Northwind Blogs in the Menu Below:-

Northwind Customer Phone List

Northwind Customer Phone List

Video 2 (2:46)

Find more Northwind Blogs in the Menu Below:-

Video 2 (2:46)

More Useful Stuff HERE:-

YouTube Bullet Points

Find Your Level!

More Useful Stuff HERE:-

Why Build a Search Form?

Build an Advanced Search Form

Advanced Search Form - Full instructions on how to Build it Yourself!

More Useful Stuff HERE:-

MS Access - Acronyms

ActiveX Data Objects

Data Access Objects

MD-Executable

User Level Security

Recordset

Structured Query Language

Visual Basic for Applications

Zero Length String

GIGOGarbage In Garbage Out

Place Holder

You need an Auto Fill Checklist

Checklist Required!

Checklist Required!

Video 1 (1:27)

Video 1 (1:27)

More Useful Stuff HERE:-

Recordset Loops

The Two Functions

The Two Functions

Video 1 (Time Index 72 to 95)

Video 1 (Time Index 72 to 95)

The Function "fCopyListToData"

The Function "fCopyListToData"

Video 2 (Time Index 109 to 150)

Video 2 (Time Index 109 to 150)

Extracting the Row Information

Extracting the Row Information

Video 3 (Time Index 200 to 250)

Video 3 (Time Index 200 to 250)

More Useful Stuff HERE:-

Recordset Loop - Create Records

Create Many Records

Create Many Records

Video 1 (5:16)

Video 1 (5:16)

Leave List as per Date Range

More Useful Stuff HERE:-

Do you need a hand in the right direction?

You are in the right place.

GIGO
Garbage In Garbage Out