Checklist Sample dB How to Check ALL

Demonstrate how to change all of the checkboxes from False to True and True to False. Mark all the Check Boxes as true or false with a Command Button. Add a Command Button. Create a query based on the the table tblData. Demonstrate how to create an Update Query and then extract the SQL of the query and use that in your VBA code. It's best to put the spaces at the end of the SQL strings. Show how to add a reference to a control on the form within your VBA code in particular a reference within the SQL statement. The reference to the text box should return the value by default however it's good practice implicitly reference the value. To uncheck them you would change the "True" to "False" in the SQL statement. Demonstrate how to pass the "true" or "false" through as a parameter in a function.

5 - Adding a Checklist to MS Access

Prevent the user selecting data in the combobox. Reverse enabled and locked. Demonstrate hiding unnecessary columns in the subform. Change the column title displayed in the subform. Quick walkthrough of the wedding ceremony Checklist.

4 - Adding a Checklist to MS Access

Creating a Checklist in MS Access Display subform in datasheet view Explain that although we term a subform as if it was something different, it's not any different, it's just a normal Form. Demonstrate that the subform is incorrectly showing a number instead of text Open the subform in Design view, select the text box that contains the data items. Change the Text box in to a Combo Box. Make sure you rename the textbox to reflect its a combobox by preceding it with cbo. The freshly converted combobox will not have a record source. Select the tblList and then invoke the query Builder. We want to see the third column however MS Access numbers column's from 0 to 2 so the third common number is 2 not 3 We don't want to see the first column however we need its data, so you leave it checked. Demonstrate that you are seeing the numbers instead of the text.. Set the limit to list to "YES" Set the column count to two, set the column width to Zero (0) centimetres and two (2) centimetres Now if you view the form in datasheet view again, it correctly displays the text instead of numbers Demonstrate how to hide the subform columns

3 - Adding a Checklist to MS Access

Demonstrate how to place a subform on a main form to display a Checklist. Select Table and press Create Change the name to sfrmData Change the form to datasheet view Demonstrate how to drag the subform onto the main form Demonstrate how the subform IS NOT linked to the main form record Demonstrate using the subform field Linker Demonstrate the records moving in unison Complain about the list showing numbers instead of text values and indicate this will be addressed in the next video

2 - Adding a Checklist to MS Access

Adding a Check-List to MS Access 1:05 fCopyListToDataOnlyOnce 1:50 Code to check if it is "NULL" 2:00 Function "fCountSQL" 3:05 Current Event for calling the "fCopyListToDataOnlyOnce" Function 4:15 #NiftyAccess Tip! always bring your latest code to the top..! 5:30 Change from one record to another causes the code to run 6:00 Navigating through the records 6:40 Example of Custom error code working 7:15 Code for trapping "NULL" 8:00 Enter new record to trigger calling of "fCopyListToDataOnlyOnce" 8:15 Shows the new records have been added 8:20 Talk through of what is required in the next Video

1 - Adding a Checklist to MS Access

Demo of a Checklist in MS Access You might be tempted to do this by creating a set of fields across in your table, however this is not considered good design. A better way would be to have a table with the check items individually listed in the table. In this Video I would like to demonstrate one method of achieving this. The minimum number of tables required to make this work is 3 tables, I have given these generic terms: master, data and list. To explain the use of these tables I will refer to a specific case. Consider a business which manages weddings for couples. In this case the master table "tblMaster" will contain details about the "wedding planners" customers. The data table "tblData" will store the data for each individual customer. Finally a table "tblList" that lists the "check off" items that need to be transferred to the data table "tblData". The transfer of the list into the data table is performed by just two functions "Copy List" fCopyListToData, and "Append List" fAppendListToData. There are several other supporting functions in this example. fCopyListToData puts the data from the list (tblList) into a recordset and then loops through the record set extracting the Row information from each field, then transfers that information via the function fAppendListToData into the data table "tblData". "tblData" is what this code is about, it is this set of data that is created. Looking at the data table (tblData) we have five fields: dataID, dataLinkID, dataSets, dataItems and dataTickedOff. dataID is not necessary and can be ignored. dataLinkID is used to relate the values in this table (tblData) to the master table (tblMaster). "dataSets" would not be necessary if you only had one set of data in your list. It is quite common to have a list divided into separate sets of information so in that instance it would be required and really for a single list with only one set, then you just add a "1" (or some other identifier) and treat it as if it was a set from a number of sets. "dataItems" links back to the table list "tblList" and can be used to find the string value related to each item. "dataTickedOff" is a boolean field which allows you to to flag whether a list item has been done, or not done.

0 - Adding a Checklist to MS Access

This is a demonstration of an excellent way to add a checklist to your Microsoft Access database The beauty of this method is it adds check list items automatically so that your user does not have to pick, and add items individually from a combo-box. This method will save your user a considerable amount of time and make you the Office Hero!

Generate Multiple PDF Report with different Criteria 3

Demonstration of how to.............. https://www.youtube.com/watch?v=IorNg7ROemE

Generate Multiple Copies of a Report (PDF Invoices) with different Criteria 2

Generate multiple copies of a Report with individual Criteria for each Report. Demonstrates how to change the Record Source of each Report, on each iteration of the Recordset Loop. Show how to use a Combo Box in the Report to display text from a related lookup table. Show that the record source SQL statement has a different value. Add a custom property to the form to hold the SQL Statement, the SQL Statement will be generated by a function in the Form. The Report will then interrogate the form, grabbing the SQL Statement from the custom property. Demonstrate how to insert Text straight into the Form Module. A Public Custom Property for holding the SQL Statement. See how to insert text straight into the Report module, a Public Custom Property for holding the name of the Form that Opened the Report. Shows how the Report On Open event extracts the Form name from OpenArgs. Demonstrate how you can use the Reports Open Event to use the DoCmd.Close command to close the Form that Opened the Report. https://www.youtube.com/watch?v=cJZzfjHCYcE

Generate Multiple Copies of a Report (PDF Invoices) with different Criteria 1

Generate multiple copies of the same report but with individual criteria for each report provided by an SQL statement. The requirement is for Reports with Ascending numbers in the Report Name. https://www.youtube.com/watch?v=4OH1owAcluw

MS Access - Generating Multiple Reports (PDF Invoices)

Generating multiple reports. Generating an invoice for each customer. Uses a Main Form to run a Record Set Loop (RSL) the record set loop loops through the SQL statement. The SQL statement is rendered unique by a value collected from the field. The SQL statement gets passed into the forms Public Custom Property. The code opens the Report. The reports Open Event extracts the SQL Statement from the Forms Public Custom Property. The SQL Statement is then passed into the Reports Record Source. https://www.youtube.com/watch?v=Tl-O3DabPz8

Automatically add a Checklist to MS Access

This YouTube playlist shows how to add a single checklist to your MS Access DB. There is a good description how the code works.

YouTube video here:-
Automatically add a Checklist to MS Access: http://www.youtube.com/playlist?list=PLhf4YcS5AjdqzkQ5egOuO1myweo6hNgG2

Leszynski's on line book shows how to add User Level Security

Years ago I was fortunate to stumble across this online book "Access 97 Expert Solutions" by Stan Leszynski, famous for the "Leszynski naming Convention" used by many MS Access Developers.

On this Page about halfway down you will find a section titled:- Setting Custom Properties Using Property Let and the code (A custom property) :-

Public Property Let prpFormMode(rstrMode As String) ' Purpose: Set the form mode when the form opens ' Arguments: rstrMode:=Form mode: Add/AddEdit/Browse/Edit ' Pseudocode: ' 1. Check the form mode argument for validity ' 2. Enable/disable buttons and features depending on argument ' 3. Set the custom property equal to the argument Code is HERE:- Listing 12.6 shows the Property Let function for the prpFormMode property. If you note the interesting line - under pseudocode (line 2) ' 2. Enable/disable buttons and features depending on argument This is exactly what you want if you need to provide user level security in your forms. You place the custom property in the top of your form and you use it in a similar way to a function, by calling it and passing in as a parameter a text string which describes the layout and use that you want the form to offer the user. Let's say your options were "Admin" or "Data Entry". The parameter is fed into a case statement, in the case statement you can hide, disable, enable, change combobox rowsources, run functions, set the controls default value, anything basically you want to do to make the form "appear as" and do whatever you wish.

Excel to Access (A)

In Microsoft Access you don't think about your data as being pages, you think about it as being a continuous list. The general way to extract data is to ask a question like "which part of this list do I want?" In your case you would say I want this "customer" where this date = X. You would ask this question with an SQL statement something like:- Select fld1, fld2 From tblMyTable Where Customer = "Fred" and myDate <> date A further requirement "If there are no results then I do not want to print the report." You would count the results returned by the above query and if the count was 0 then you would not create the report. You also want to ask the question, not for just one customer but for each customer. To achieve this you would use an SQL statement which gathers the customer names then you would put this record set into a record set loop and interrogate each record in turn. So your original question is now broken down into several elements the first element being:- "In your case you would say I want this "customer" where this date = X." Once you have achieved this you can then concentrate on solving the next element and once you have an understanding of all the elements you can draw it together to produce the result you want. Shared from Google Keep

Allow a Combo Box to Return when NOT Selected

Click HERE to download the attached zip file for this example. It's a demo showing how to return results from various criteria selected by combo boxes. The demo also shows how to handle the situation when no selection is required from a particular combobox.

I have reproduced some slides taken from a presentation. This first one is a picture of the working form. Most people get to the stage of adding three combo boxes or more, the combo boxes control the selection appearing in the subform, however they have a problem when a combo boxes left empty, instead of the expected result of returning all available options for that combo box, none are returned.

You may notice in the Picture below that the combo boxes display the word: ALL This is added by using a union query, union queries are not available from the MS Access query designer grid, you have to write them manually.
.

.
..
The Picture below is of the same form in design view, the left hand combo box property sheet has been opened showing the combo box row source displayed in the zoom window. Notice it is basically a select query with the addition of the word "distinctrow" and the final part which begins union select.
.

.
..
Below is the combo box row source (incorrectly marked as recordsource in the pictures below)
.

.
..
In this picture below you can see the parts which have been added to a basic select query, "distinct row" just make sure the query only returns one item, where the table may have several items that are the same name. And below, the "union query" which adds a Star "*" to the left hand column of the query results and the text"" to the right hand column of the query results. However the combo box only displays "" because although the first column is present in the combo box it is hidden by the combo box settings.
.

.
..
The picture below just shows a basic select statement, it's not used in the example it's here to show you what the union query is based on.
.

.
..
The picture below shows the query designer grid for the query "qryMup" there's only enough room to show the criteria for the first to columns, but the third column would be very similar to the first two if it was displayed. You may notice that this criteria contains a function.
.

.
..
Below is the full criteria of the first combo box, notice the function "fCboSearch"
.

.
..
Below is the full SQL of the query "qryMup"
.

.
..
The picture below is of the function "fCboSearch" basically what happens is the combo box result is passed in as the variable "vCboSearch" and processed. This function is designed to detect if the combo box is empty, in which my case it may contain a Null value or it may contain what is termed a zero length string "". If it contains either of those then they are replaced with the "*" which instructs the query to return all the results for that combobox election.
.

Option Group Label Color Change

This video demonstrates a code module which allows you to change the colour of the selected option group label, and optionally collect the caption of the selected label.

This code module is generic, it will work for any option group containing any number of labels (within MS access limits) and will change the colour of the selected option group label, you could also modify the code to change the other features of the label, like the border colour, border style, text style, font size, any attribute which is modifiable in VBA code.

The sample DB which contains the module HERE:

Excel in Access (Part 1)

Excel in Access (Part 2)
Excel in Access (Part 3)
Excel in Access (Parts 1, 2 and 3 as Video Instructions)
New! a follow on article
MS Access Your Data With Sub Forms

Problem for Excel Developers
One of the problems facing Excel developers moving into MS Access is actually the apparent similarity between MS Access tables and Excel spreadsheets.

MS Access is NOT Excel
This similarity of the "look" in both programs, the layout of the data, leads to the Excel developer mistakenly thinking that a database works in a similar way to a spreadsheet.

Flat File Database
Spreadsheets are very sophisticated tools for manipulating figures, and they can also handle data very well particularly in the form of a "flat file database". You can also use the flat file approach in MS Access, however MS Access is primarily a relational database, meaning that it has the ability to relate tables of information together.

Relational
It is by taking advantage of this relational property of the database that you can really make significant improvements to data handling. So if you have a spreadsheet that requires upgrading to a database to take advantage of this, then this article is for you.

Typical Spreadsheet Layout
Below is an example which shows columns that you would typically find in a spreadsheet, first name, last name for example. Then a variety of subjects, with a check box against each indicating that the subject has been taken, passed or possibly even failed!

Click on Image for Fullscreen View

Flat File is OK
A direct transfer of this spreadsheet layout into an MS Access table would be usable and indeed many Access databases are constructed in this way, and some sophisticated applications costing many hundreds of pounds are based on a flat file system. However as mentioned earlier, MS Access is a "relational" database, meaning that it has the ability to relate your data together.

You're Not Relational????
So how and why should you apply a Relational structure? Well it solves a lot of problems, there are many but I have noted the main ones below.

Uncharted Territory
The first most common problem I have noticed on forums for people with similarly designed tables imported from a spreadsheet is that they find it difficult to extract useful information, and this is one of the major reasons that you should consider constructing your new database in a different manner to your spreadsheet, no doubt you will be able to come up with your own unique methods of extracting the data, however you will find there is little help available, not because no one wants to help you, but because you are embarking into uncharted territory, "where no man has gone before".

Maintenance
Another reason, (not in any particular order of importance, the significance will change from project to project) what happens if you need to add an extra subject? Let's say the school starts offering French lessons, in the spreadsheet all you would do is add a new column "French" and indeed in your MS Access database you could also add a new column "French", however if the construction of your database is well advanced, in other words you have many queries and forms based on the table you are adding a new field to, then you will have to modify every single query and form that extracts information from this table, not something you want to do often!

Column Limit
Many modern spreadsheet programs can handle thousands of columns; however MS Access has a 255 limit to the number of fields in any particular table. So in the student table example, if you were at the 255 limit and you needed to add another subject then you would find yourself in a difficult position.

Relational Solution
So what's the solution? This is the part that is sort of counter intuitive, you actually construct two tables from the original table and link them together. This is the "relational" aspect of the database coming into play.

How?
If you look at the original layout of the data above you can ask questions about it, is there any data in the original table that is related? Looking at it, I would suspect all of the boolean columns (the check box columns, yes/no data) they are all the same, so they are a likely candidate for a separate table. And indeed there is an obvious name for this new table, they are all "subjects" that the student is or could take.

The New Table
So now you have a name for the new table, "Subjects" and to link it to the data remaining in the original table, (first name,- last name) it will need to have a field which contains a match to the RecordUniqueID field. For this example let's call this "MatchingID" then you need a field to record the subject and another field to record whether it is true or false. For the purposes of this demonstration I have terms these "TransposedSubject" and "TransposedData" and you can see what this should look like below:



Free Normalization Tool --- Download it Here How to use the Normalization Tool

How do you get that new table you may ask? Well originally it was quite a tedious task, especially if you had many columns of data to move, you had to construct an append query and append each column. However I realized it may be a process that would lend itself to automation of some sort, and I came up with a form for handling this.

Phone: +44 1635 522233
Mobile: +44 7747 018875
Email: email@tonyhine.co.uk

Excel in Access (Part 2)
Excel in Access (Part 3)

Excel in Access (Part 4) --- Video Instructions

New! a follow on article
MS Access Your Data With Sub Forms

Excel in Access (Parts 1, 2 and 3 as Video Instructions)

Excel in Access (Part 1)
Excel in Access (Part 2)
Excel in Access (Part 3)


Normalization Instructions (I'm going to leave these on Ecademy for now, they are too wide for direct transfer to here....)

Download the database used in the following videos here: (This is the "empty" database before the following modifications are carried out)

The following five videos comprise a total viewing time of approximately 21 minutes in videos of length from three to six minutes.

The video's below demonstrate a slightly different use for the Tool previously explored in text and picture based threads. In these videos I demonstrate how to: Not only transfer the data from a spreadsheet table, but also the identity of the data picked up from the field name.

Convert contacts Spreadsheet to MS Access database 1 --- 6min
The video below explores a table which is in "flat file" format, suggests how you can choose which rows and columns would possibly benefit from being transposed into a new table, then demos how to do this with the tool provided (A Form)

 .
.
Convert contacts Spreadsheet to MS Access database 2 --- 3min
Explains how to remove redundant data from the table produced above, identifies a field that would benefit from being converted into a lookup table, and shows how to create a look up table from this field.

 .
.
Convert contacts Spreadsheet to MS Access database 3 --- 3min
using the above look up table demonstrates how to replace the "text data" with a number which links the former text (now in the look up table) with the original row/record.

 .
.
Convert contacts Spreadsheet to MS Access database 4 --- 5min
demonstrates how to create a form in datasheet view, and add a combo box to display the look up data

 .
Convert contacts Spreadsheet to MS Access database 5 --- 4min
and then display that form as a sub form showing the telephone number, communication media list, as related to the contact.

 .
The final database as completed in the above videos is downloadable from here: (this database contains all of the modifications as described in videos above)


Excel in Access (Part 1)
Excel in Access (Part 2)
Excel in Access (Part 3)
New! a follow on article
MS Access Your Data With Sub Forms

Excel Power Users Find MS Access Difficult

I've done a three part blog on why some Excel power users find moving to MS Access difficult.  The video below is a verbal explanation I did of the first part of the three blogs, you can find the text the video is providing an explanation for here:

Excel in Access (Part 1)


                                          YouTube Video HERE:

.....
....
...
..
.

Computerize your Business

Someone asked me how I go about compuerizing a business, this was my reply:

There are several ways; all have their pluses and minuses like everything. 

One way is to start looking at your business processes and tailor them to fit on an A4 sheet of paper. If a task is complicated involving several/many stages, then use a master A4 sheet to control the individual steps.

This method is very handy if you have several people working in different areas, as even if you do not computerize your business, you at least develop a formal business process for each area which is transferable to someone else.

For example:
a) in the event someone leaves the business
b) business increases and you need to train up other people to do similar tasks.
c) a turnkey business is much easier to sell, and in your case franchise.

The idea is that the A4 sheet will become the computer screen layout, giving the computer programmer a good idea of what you want, and giving your employees a familiar interface when you eventually move on to the computer.

Another way as I already mentioned is to find a package that does much of what you desire, you may need to use more than one package, however I think you can see that “off the shelf” will give you a good idea of what you don't want, and makes it much easier to communicate with a programmer.

One of my USP phrases is that I am primarily a businessman that can program; this gives me a distinct advantage in the design stage.

The most formal method is to draw up a specification, this method involves a considerable cost on just developing the specification; however you have the advantage of having a specification that you can offer around several companies and get competitive prices for the development.

My preferred method is to work alongside you, and, or, your staff “doing the job” where possible, as this is the best way to develop the computer system to work in the most natural way, the most supportive way for you and your staff.

As you can see, some of these methods you can tackle yourself, or appoint a member of staff to take responsibility for.

The most important thing is to start thinking about what’s involved, make notes where you can, just by making one or two short notes every day eventually builds up into a considerable document which will be very useful for you or anyone tasked to computerise your business.