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.
[video_page_section type=”youtube” position=”default” color=”#2c3e50″ btn=”light” heading=”Nifty Access” subheading=”” cta=”” video_width=”1080″ hide_related=”false” hide_logo=”false” hide_controls=”false” hide_title=”false” hide_fullscreen=”false”]https://www.youtube.com/watch?v=1jGi2MVFkLA[/video_page_section]

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.
[video_page_section type=”youtube” position=”default” color=”#2c3e50″ btn=”light” heading=”Nifty Access” subheading=”” cta=”” video_width=”1080″ hide_related=”false” hide_logo=”false” hide_controls=”false” hide_title=”false” hide_fullscreen=”false”]https://www.youtube.com/watch?v=Q6-1JaolYnE[/video_page_section]

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

[video_page_section type=”youtube” position=”default” color=”#2c3e50″ btn=”light” heading=”Nifty Access” subheading=”” cta=”” video_width=”1080″ hide_related=”false” hide_logo=”false” hide_controls=”false” hide_title=”false” hide_fullscreen=”false”]https://www.youtube.com/watch?v=qBVRGh5RXbI[/video_page_section]

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
[video_page_section type=”youtube” position=”default” color=”#2c3e50″ btn=”light” heading=”Nifty Access” subheading=”” cta=”” video_width=”1080″ hide_related=”false” hide_logo=”false” hide_controls=”false” hide_title=”false” hide_fullscreen=”false”]https://www.youtube.com/watch?v=cQQSXofvQh0[/video_page_section]

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
[video_page_section type=”youtube” position=”default” color=”#2c3e50″ btn=”light” heading=”Nifty Access” subheading=”” cta=”” video_width=”1080″ hide_related=”false” hide_logo=”false” hide_controls=”false” hide_title=”false” hide_fullscreen=”false”]https://www.youtube.com/watch?v=7IbB-oaA-Mo[/video_page_section]

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.
[video_page_section type=”youtube” position=”default” color=”#2c3e50″ btn=”light” heading=”Nifty Access” subheading=”” cta=”” video_width=”1080″ hide_related=”false” hide_logo=”false” hide_controls=”false” hide_title=”false” hide_fullscreen=”false”]https://www.youtube.com/watch?v=r1y_Zl3uLkM[/video_page_section]

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!
[video_page_section type=”youtube” position=”default” color=”#2c3e50″ btn=”light” heading=”Nifty Access” subheading=”” cta=”” video_width=”1080″ hide_related=”false” hide_logo=”false” hide_controls=”false” hide_title=”false” hide_fullscreen=”false”]https://www.youtube.com/watch?v=JGLfH2on09M[/video_page_section]

Generate Multiple PDF Report with different Criteria 3

Demonstration of how to…………..
[video_page_section type=”youtube” position=”default” color=”#2c3e50″ btn=”light” heading=”Nifty Access” subheading=”” cta=”” video_width=”1080″ hide_related=”false” hide_logo=”false” hide_controls=”false” hide_title=”false” hide_fullscreen=”false”]https://www.youtube.com/watch?v=IorNg7ROemE[/video_page_section]

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.
[video_page_section type=”youtube” position=”default” color=”#2c3e50″ btn=”light” heading=”Nifty Access” subheading=”” cta=”” video_width=”1080″ hide_related=”false” hide_logo=”false” hide_controls=”false” hide_title=”false” hide_fullscreen=”false”]https://www.youtube.com/watch?v=cJZzfjHCYcE[/video_page_section]