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.
.