Split a Large MS Access Datasheet in to 3 Smaller Datasheets
You’ve got a large “VERTICAL” List of Data in datasheet form, you might prefer to have it spread out across the screen instead of constantly having to page up and down the list. In this set of videos I demonstrate two ways of implementing this. The first way is by adding several forms, the second way is much more much satisfying, in that you utilise one form three times, by changing its record source with VBA code. NOW DON’T be put off by the mention of VBA! You don’t need to use VBA, but I’m sure once you see the videos you will wonder why you’ve been avoiding VBA! VBA makes MS Access Development much easier and much more rewarding. And once you get VBA under your belt, a few little tricks like this one, just think of the kudos at work, you’ll be well on the way to being the go-to person in your organisation for anything to do with MS Access.
One Form in Three Places
One Form in Three Places
Video 1 (3:10)
This Video demonstrates the problem which is a long list of 25 items. The user wanted the list split up into 3 parts with 8 items in each section. The first step was to create three subforms, using the original subform. Then the subforms record source was changed so that it only showed 8 records. You can also see in the video the child/parent links between the main form and the subforms. Basically the child/parent links are created automatically when you create a new subform. If you’re interested in finding out more information about some forms and what you can do with them follow this link here Sub-Forms….
Video 1 (3:10)
Video 1 - Index
00:10 this is the first video 1 of 4 which demonstrates the enhancement of this form that was created for collecting case file names for storage in boxes
00:13 the vertical layout of the sub-form takes up quite a lot of space
00:29 OP wanted to display these records in three
separate sections across the form instead of 1 long unmanageable list
00:45 select the forms design view and then make two more copies of the sub form. you
now have 3 subform/subreport controls
00:58 we now have three lists, but we have all of the records in each list, not exactly what we were trying to achieve
01:22 the records shown in the subform/subreport controls are limited by the connection to the master form through the child-master (Parent) Link, Properties of the subform/subreport control
01:48 the newly created subform/subreport controls need their default names changing to something more akin to the functions they perform
01:52 each section will also require some way of limiting the display of the records
02:02 the best way of controlling the records displayed is to change the
record source of the form contained within the subform/subreport control
02:06 open subform Data "sfrmData" and look at its record source
02:13 the forms record source is currently set at table data, "tblData" however if we want to restrict the records shown we will need to change the criteria by applying a query
02:16 we can do this by invoking the query Builder
02:24 instead of having the record source as a table we can change the record
source to a query by creating the query in the designer grid
02:26 dragging all the fields in from the table and running the query is exactly gives
us the same results as using the table directly
02:34 however we only want to show records 1 to 8
02:44 return to design view and in the column data items "dataItems" add criteria to restrict the records Returned to "less than 9"
02:48 and items 1 to 8 are correctly listed
02:56 if we go back to the form master "frmMaster" notice that each copy of
the form displayed records 1 to 8
… …
3 Forms with 3 Record Sources
3 Forms with 3 Record Sources
Video 2 (3:10)
In this video I demonstrate creating the extra forms required. Total number of subforms now 3. These three subforms each need a unique record source so that they can show a unique set of records. The first subform shows records 1 to 8, and the creation of this query was covered in the previous video. In this video I demonstrate how to make the other two queries, one to show records 9 to 16 records and the last subform, to return turn the records over and above 16 (up to 25 in this particular case)
Video 2 (3:10)
Video 2 - Index
00:20 recap of previous video in which which which one form duplicated 3 times was used with exactly the same record source producing exactly the same list of data not exactly what we want!
00:23 here I describe how I anticipate how the new record sources should appear
00:50
the record source of subform data 2 "sfrmData2" to is incorrect, you need to open the query builder grid
01:11 how to change the query so that it
only shows a record 9 to 16
01:30 so it wants to be bigger than 8 and less than 17 ….
01:41 the correct set of Records is Returned
01:48 the process is almost the same for the third form
02:02 now we just need to display all of the records from 17 and up which is to say, greater than 16
02:28 now all we need to do is change the Form displayed in the two
new subform/subreport controls. Cgange from "sfrmData" to the form with the correct record source to display the records we need in both sections..
02:50
running the form master "frmMaster"… now it displays the correct records in the in each of the subform/subreport controls
03:05 let's take it up
a level!
… …
Utilising an "Instance" of a Form
Utilising an "Instance" of a Form
Video 3 (4:50)
Here, I demonstrate how you can make significant improvements to your MS Access database. The reason you’ve got “Three Forms” (as created in the previous video) is so that you can utilise the three separate record sources which you need to display three separate list of Records. However with a bit of “Nifty VBA” you can utilise one form three times! You make use a special copy of the Form called an “instance”. Using an instance isn’t quite the same as using the original form, you can change the “RecordSource” of the “instance” without changing the original form in any way. They are totally separate entities. This means you can have one original form and two instances of it, and have a different record source in each! This gives you your 3 columns across the page, the three columns displaying the records without having three separate forms. A much more satisfying way of going about it. And it’s not difficult, there’s a little bit of VBA code, but if you get this VBA under your belt then you’re well on the way to becoming a professional Microsoft Access developer.
Video 3 (4:50)
Video 3 - Index
00:06 we have three separate forms with three separate record sources to display the data correctly
00:20 this is the RecordSource of the form
00:30 however doing it this way suffers from the problem is that you end up with lots of very similar forms the only difference being the record source
00:50 at this point I mentioned using VBA code!
01:00 to use VBA code we just want one form. Here I reset back to using one "Single"
form (sfrmData) in ALL the sub form/subreport controls.
01:12 notice the subform/subreport control shown as a yellow line around the form that is contained within
it
01:30 I explain the technique here which relies on each "subform window" subform/subreport control containing the same form and then we update each
of the separate forms record source to display the correct data
02:20 unfortunately Microsoft Access names the subform/subreport control. MS Access gives it the
same name as the form that is contained within it. This can be misleading to the uninitiated developer
02:44 in the case of this subform/subreport control it hasn't
been named after the form that is contained within it because a subform/subreport control with that name already exists. MS Access defaults to a naming convention when there is a name clash and names the new subform subreport control "Child26"
03:22 The MS Access naming convention isn't quite a silly as it might sound. Are you sure?
03:42 I often use the term "window" to describe the
subform/subreport control because it better describes what it does
03:50 here I use my personal naming convention and I named the subform subreport control
"subFrmWinFORM_NAME"
… …
VBA code to set up the three "Instances"
VBA code to set up the three "Instances"
Video 4 (7:20)
Visual basic for applications (VBA) is used in this example to set up the three “instances” of the subform. With VBA code, each subform (that is a form within a subform/subreport control) each of these forms can be passed an SQL String through into the forms Record Source. This is a very handy and clever technique for using one form multiple times. By using a form multiple times “multiple instances” you can significantly reduce your development time and also time spent on subsequent maintenance operations.
Video 4 (7:20)
Video 4 - Index
00:16 add a message box in the form load event to demonstrate how this event occurs when the form loads
00:22 note how selecting "Form" from the "General ComboBox" automatically creates the form load event for you
00:40 adding a message box statement
to the form load event
00:44 when you open the form the form load event is triggered which opens the message box
LINK to VBA Beginner on Nifty Access
01:20 all three subform/subreport controls contain the same form "sfrmData"
01:45 although the subform/subreport controls point at the form "sfrmData" effectively each subform/subreport control contains a separate distinct form called an "Instance"
02:30 to be able to change the RecordSource of the instance of the form, you need to be able to find it with VBA code.
02:35 the subform/subreport control property sheet has a property named "Source Object" it is important to note that this can refer to a "Form" or "Report" … When you need to reference it
from VBA, it will either be listed as a Form or Report in the "intellisense" Listing.
03:00 replace the message box with a reference to the subform/subreport control
03:24 enter a dot to trigger intellisense and choose
"form" from the list
03:50 enter a dot to trigger intellisense and choose RecordSource from the list
04:10 debug often! Debug until you think you've got OCD
04:30 grab the record source from the second form
04:45 for your own peace of mind, paste the RecordSource SQL String into the query designer grid SQL view to confirm that it returns the expected records
05:15 paste the SQL String, in effect, the text version of a query into the VBA code you have written in the form load event
05:25 load the form and observe the results!
06:00 repeat what you did earlier to create the VBA code that will modify the RecordSource of form 3
06:35 each panel loads with a separate set of data – job
done!
06:53 tidy up by clearing out the redundant forms
07:05 see
if you can update the VBA code to modify the 1st Forms RecordSource in the Main Forms Load Event
… …
Pagination of the Three Pages
Pagination of the Three Pages
Video 5 (12:02)
By placing the three subforms on a Tab control, we get a form of pagination, you can click through the 3 Tab Pages by clicking on the Tab pages.
In this video I demonstrate how to create a Tab control, how to enter controls onto the Tab control pages, without the controls falling through. It is difficult to get controls to appear on a Tab page, if you don’t know the trick! The Best Trick is to have the the property sheet open, now when you click from one control to another, you can immediately see the Type of control you are clicking on.
In particular you are looking for a Subform/Subreport Control. Every time you click on a tab of the Tab control it activates the Tab control change event. And every time you click on tab on a Tab control the ordinal number (The Page Index) for that tab page is sent through into the Tab control value. You can use the the Tab control change event to interrogate this number in a a select case statement and make decisions about what you want to do when a particular tab page is pressed.
In this example the major change is to change the record sources of the subforms displayed on the Tab pages.
… …
