New Sample: Form_MtoMViaCheckBoxArray

by AD Tejpal

This sample db demonstrates use of check box array for populating a junction table serving many to many relationship between clients and training courses. Data is stored in normalized manner in tables T_Clients, T_Courses and T_ClientCourses. No temporary table is needed.


Two styles of user interface are demonstrated:

1 - Normal Style (Using Virtual Records Matrix):
-------------------------------------------------

1.1 - Client particulars are displayed in the parent form while all available course options are displayed in a subform, as a matrix of virtual new records.

1.2 - As and when the user clicks a check box into selected state against desired course in the subform, the row in question gets converted into a freshly entered actual record in the junction table.

1.3 - Similarly, if a check box is clicked into a de-selected state, corresponding record in junction table gets deleted. This is accompanied by a virtual new record getting displayed in lieu of the recently deleted actual record (At all times, the subform matrix continues to display all available course options).

1.4 - Total number of courses opted for current client as well as the grand total for all clients are also displayed in the parent form

2 - Spreadsheet Style - Datasheet View (Using Crosstab Query):
-----------------------------------------------------------------

2.1 - Adequate number of check boxes along with child labels are inserted in the form at design stage. This is a one time exercise, using the subroutine P_AddControls().

2.2 - Each check box column of this datasheet form (used as a subform) represents an individual course. A crosstab query, based upon Cartesian join between tables T_Clients and T_Courses, serves as the record source.

2.3 - As and when the user clicks a check box into selected state in desired course column, a record with appropriate values for ClientID and CourseID gets added to the junction table.

2.4 - Similarly, if a check box is clicked into a de-selected state, corresponding record in junction table gets deleted.

2.5 - First three columns display client name, list of courses opted and total number of courses opted respectively. These three columns are frozen so as to always remain in view while the user moves across check box columns representing individual courses.

2.6 - Third column depicts total number of courses opted for each client. Bottom cell of this column shows the overall total courses opted for all clients. Overall total is also depicted within parenthesis in header caption for this column, so as to always remain in view despite vertical scrolling.

2.7 - For each course, total number of clients who have opted for it, is depicted within parenthesis in the caption for respective column header, so as to always remain in view despite vertical scrolling.

You can find this sample here:
http://www.rogersaccesslibrary.com/forum/Form-mtomviacheckboxarray_topic553.html

.

Domain Function Examples: Numbered Query With DCount

So far in this series on Domain Functions, I've discussed the general syntax (Domain Functions Demystified) and problems involved in building criteria expressions (Domain Functions Demystified: Criteria Expressions). Unfortunately, many of the examples I've given are relatively trivial. So for my next few blog posts, I thought I'd give what I consider truly useful applications of domain functions.

Other Examples:

1. Simulate AutoNumber with DMax
2. Running Sum with DSum
3. Difference Between with DMax
4. Rolling Average with DAvg and DCount 
5. Begin Date and End Date from Effective Date

Numbered Query

One interesting problem is how to create a numbered sequence in a query, that is, have each record numbered sequentially.

This is fairly easy to accomplish in an Access report. All you need to do in a report is add an unbound text box. In the control source, put =1 and set the Running Sum property to Over All.

But suppose you don't want to do it in a report. Suppose you want to do it directly in a query. There are two different ways to accomplish this. The first uses the Domain Aggregated function DCount and the second uses a Correlated Subquery. Since this series is devoted to domain functions, I'm going to concentrate on that.

Both of these methods require a unique column in the table to create the sequence on. This could be the Primary Key field or any field that has a Unique Index. In the Customers table, there are two such columns, CustID (Customer ID), which is the primary key, and CustName (Customer Name), which has a unique index.

DCount Method

Domain Aggregate functions are an Access-only method to return statistical information about a specific set of records, whether from a table or query. DCount in particular will return the number of records in a given recordset. It has three arguments: 1) an expression that identifies a field, 2) a string expression that identifies a domain (that is, the table or query), and 3) a Criteria, which is essentially an SQL Where clause without the word WHERE.

The specific DCount expression we're going to use looks like this:

DCount("CustID","Customers","CustID <=" & [CustID]

In the query, it will look like the following.

SELECT DCount("CustID","Customers","CustID <=" & [CustID]) AS Sequence,
CustName, CustPhone, CustID
FROM Customers
ORDER BY CustID;

The Order By clause in the query is important. This will sort the query on the CustID field. We'll need to have that order to use the criteria argument in the DCount.

Here's how it works.

For each record in the query, Access runs the DCount function. The DCount returns the number of records in the domain where the CustID in the function is less than or equal to the CustID in that record of the query.

So in the first record, the CustID is 1. So the DCount opens the domain (essentially opens the Customers table again) and it sees that there is only 1 record whose CustID is less than or equal to 1. So it returns 1.

Then it processes the second record. The CustID of that record is 3, and the DCount function sees that there are only 2 records which have an CustID whose value is less than or equal to 3. So it returns 2.

It is not necessary that the Order By field is an unbroken sequence. As long as that field has unique values and is sorted, it will work.

The output of this query looks like Figure 1. Strictly speaking, you wouldn't need to show the CustID number in the query at all. However, I included it to show that while the order is the same as CustID, the sequence number does not have gaps in the numbering sequence.

You don't need to use a number field as your Order By field. You can sort on text fields and number the query as well.

If you wanted to sort on the Customer Name field (CustName), you would change the DCount to the following:

DCount("CustName","Customers","CustName <='" & [CustName] & "'")
 The output would look like this:

Subquery Method
As I said, this can also be done with a correlated subquery, which I may discuss at a later date. However, you can find both methods on my website in this sample: NumberedQuery.mdb.
.

Domain Function Example: Simulate AutoNumber with DMax

So far in this series on Domain Functions, I've discussed the general syntax (Domain Functions Demystified) and problems involved in building criteria expressions (Domain Functions Demystified: Criteria Expressions). Unfortunately, many of the examples I've given are relatively trivial. So for my next few blog posts, I thought I'd give what I consider truly useful applications of domain functions.

Other Examples:

1. Numbered Query with DCount  
2. Running Sum with DSum  
3. Difference Between with DMax 
4. Rolling Average with DAvg and DCount
5. Begin Date and End Date from Effective Date 

One of the classic uses of a domain function is using the DMax() to generate your own "autonumber" field. The reason you would want to do this is if you want an unbroken sequential number field. The Autonumber data type cannot guarantee an unbroken sequence, so if you want one, you have to develop it yourself.

Single-User Application

While there are several ways to do this, one of the simplest is to use a DMax function in as the Default Value of a control on a form.

So let's say I want to generate my own sequential Product Number for a product table. I can create a form based on the Product table, and create a textbox bound to the ProductNum field. In the DefaultValue property, I would put the following domain function.

=DMax("ProductNum","Product")+1

 This opens the Product table, find the largest ProductNum and add 1 to it. That's it.

Multi-user Application

It becomes a little more complex in a multi-user environment, since two or more people may try to select the same number at the same time. There's a fairly simple solution for multi-user collisions, but it requires a tiny bit of VBA code.

First of all, the field you're incrementing must be a Primary Key or have a Unique Index on it. When you try to save the record, if the number has already been used, it will throw an error (3022). You can trap this error in the OnError event of the form:

Private Sub Form_Error(DataErr As Integer, Response As Integer)

    Response = IncrementField(DataErr)

End Sub

This calls the IncrementField user-defined function, which looks like this:

Function IncrementField(DataErr)

   If DataErr = 3022 Then

     Me!ProductID = DMax("ProductID", "Product") + 1

     IncrementField = acDataErrContinue

   End If

End Function

So, if you have a collision, the IncrementField function will go out and grab another.

You can find a working example of both the single-user method and the multi-user method on my website here: AutonumberProblem.mdb.

.

Domain Functions Demystified: Criteria Expressions

In my last post: Domain Functions Demystified: Introduction, I discussed the structure and syntax of the domain functions in Microsoft Access. The basic syntax is as follows:
DFunctionName("<Fieldname >", "<RecordSource>", "<Criteria Expression>")

• Fieldname refers to the field against which the function will be applied.
• RecordSource refers to a table or query from which the records will be pulled.
• Criteria Expression is basically an SQL Where clause without the WHERE keyword. (optional)

Criteria Expressions

The third argument, the Criteria expression, is the most confusing, so it might be useful to take a closer look at them.
A Criteria Expression is an SQL Where clause without the "WHERE" keyword. Just as in a Where clause, sometimes the value of the expression needs to be delimited, sometimes it doesn't. What determines the delimiter (or lack of one) is the data type of the field.

Numeric

Numeric fields don't need a delimiter. So we can use:
"[OrderNum] = 1"
for the criteria expression. (Although remember that the entire expression must be enclosed in quotes.) Substituting a variable (in this case the name of a bound textbox on a form) for the value yields this:
"[OrderNum] = " & Me.txtOrderID)

Dates

Date fields require the date delimiter. In Access, that's the pound sign or hash mark (#). So with a hard-coded value, my criteria might look something like this:
"[OrderDate] = #1/1/2010#"
With a variable, it would look like this:
"[OrderDate] = #" & Me.txtOrderDate) & "#"

Strings

String or text values are the trickiest type and cause the most confusion. String values must have string delimiters: either quote marks (") or apostrophe ('), but since the entire argument must also be encased in string delimiters you have to somehow tell the expression evaluator which string is which. So suppose I want to use CustName in my domain function.
    [CustName] = "Roger Carlson"
So if I put quotes around the whole thing, I get
"[CustName] = "Roger Carlson""
This will cause an error, however, because of the way the interpreter reads the quotes. In order to put a quote within a quoted string, you have to double the quotes:
"[CustName] = ""Roger Carlson"""
So now, I need to replace the explicit Roger Carlson with the variable:
"[CustName] = "" & Me.txtCustNum & """
But this will also cause an error because I now have two different strings that have to be concatenated and the interpreter isn't reading the quotes right again. To fix it, I have to double the inner quotes again:
"[CustName] = """ & Me.txtCustNum & """"
Now, I said you can also do this with apostrophes. So let me repeat the process:
[CustName] = 'Roger Carlson'
can be fixed surrounded with quotes this way and it will not error.
"[CustName] = 'Roger Carlson'"
But adding the variable again:
"[CustName] = ' & Me.txtCustNum & '"
gives us two incomplete strings. We need to add a quote to each:
"[CustName] = '" & Me.txtCustNum & "'"
So either:
"[CustName] = '" & Me.txtCustNum & "'"
or
"[CustName] = """ & Me.txtCustNum & """"
will work.
Now, you might ask, why would you ever use the double quote if the apostrophe will work? Well, if your data can have an apostrophe in it, (like O'Brian) you have to use the double quotes.

ASCII Code

Another possibility is to use the Chr$(34) character (which is the ASCII code for a quote) like this:
"[CustName] = " & Chr$(34) & Me.txtCustNum & Chr$(34)
This gives us the opportunity to deal with delimited values of all kinds programmatically. I suppose I could write one of my own, but Ken Getz wrote the classic routine for that. Anything I'd write would simply be a copy, so I'll show his:
(excerpted from "Microsoft Access 2.0 How-To CD" (by Getz, Feddema, Gunderloy,and Haught and published by the Waite Group Press)
Function FixUp (ByVal varValue As Variant) As Variant

'Add the appropriate delimiters, depending on the data type.
'Put quotes around text, "#" around dates, and nothing
'around numeric values.

    Dim strQuote As String
    ' strQuote contains the ANSI representation of
    ' a quote character
    strQuote = Chr$(34)
    Select Case VarType(varValue)
        Case V_INTEGER, V_SINGLE, V_DOUBLE, V_LONG, V_CURRENCY
            FixUp = CStr(varValue)
        Case V_STRING
            FixUp = strQuote & varValue & strQuote
        Case V_DATE
            FixUp = "#" & varValue & "#"
        Case Else
            FixUp = Null
    End Select
End Function
To use this function, copy it into a general module, and then call it like this:
"[CustName] = " & FixUp(Me.txtCustNum)
Using this method, it doesn't matter what data type or delimiter, the function fixes it automatically.

Mixed Apostrophe and Quotes

One last problem. What if you have both embedded apostrophes and quote marks in your string value? For instance, you have a height value which is a string with feet and inches like this: 6' 3". This will cause a problem regardless of which delimiter you use.
However, there's a solution for that too. AD Tejpal has developed a comprehensive solution, which you can find here: http://www.rogersaccesslibrary.com/forum/topic113.html
In my next post, I'll look at some in-depth examples of how Domain Functions are used.
.

Domain Functions Demystified

Introduction

In Microsoft Access, domain functions work like mini-SQL statements that can be used in queries, forms, reports, or code. Domain functions can also be used in places where SQL statements cannot, like the control source of a textbox. Like an aggregate (or totals) query, the domain functions build a resultset (that is, a set of records) and then apply a function to it. Unlike a query, however, a domain function can return ONLY one value.

Access has several built-in domain functions, the most common of which are: DCount(), DLookup(), DSum(), DAvg(), DMax(), DMin(), DFirst(), and DLast().

• DCount() returns the number of records of the resultset.
• DLookup() returns the value of a field in the resultset. If the resultset has multiple values, it returns the first one.
• DAvg() averages the values of the indicated field of the resultset.
• DMax() and DMin() find the highest and lowest values (respectively) of the resultset.
• DFirst() and DLast() finds the first and last values (respectively) of the resultset.

Note: At first glance, DFirst and DLast appear to be same as the DMax and DMin, but it they're really very different. DFirst returns the very first record in the recordset, which may not be the minimum value. Likewise, the last record in the recordset may not be the maximum. In general, I avoid DFirst and DLast as being less than useful.

One popular myth is that domain functions are slower than other methods. This is not true.  In some cases, the domain functions can be as fast or faster than other methods.  If performance is an issue, you should test both methods to and see which performs better.

Syntax

Domain functions have three arguments (the third is optional, however). The syntax is as follows:

DFunctionName("<Fieldname >", "<RecordSource>", "<Criteria Expression>")

• Fieldname refers to the field against which the function will be applied.
• RecordSource refers to a table or query from which the records will be pulled.
• Criteria Expression is basically an SQL Where clause without the WHERE keyword. (optional)

Each argument must be a string expression and must, therefore, be surrounded with quotes. This can be a problem when the Criteria Expression must also have quotes in it. I'll get to that a bit later.

Just like SQL Where clauses, some values must be delimited and others are not. For instance:

• Numeric values do not need delimiters (DLookup example below).
• Date values need the # delimiter around them (DMax example below).
• Strings need either the apostrophe (') or quote (") delimiters (DSum example below).

Examples of Simple Domain functions

• DCount("EmpID", "tblEmployee")Counts the number of records in the Employee table
• DLookup("SSN", "tblEmployee", "[EmpID] = 16")Returns the social security number of employee number 16.
• DMax("OrderDate", "tblOrders", "[OrderDate] < #1/1/2009#")Returns the latest order date from the Orders table which is before 1/1/2009
• DAvg("Cost", "tblProduct", "[Category] = 'printer'")
  Returns the average cost of all printers from the Products table.
• DSum("Cost", tblProduct", "[Category] = 'printer' AND [Manufacturer] = 'Epson'"or
  DSum("Cost", tblProduct", "[Category] = ""printer"" AND [Manufacturer] = ""Epson"""
  Returns the total cost of Epson printers from the Products table.

Because the entire Criteria Expression must be string delimited, the expression evaluator will get confused when using the quote to delimit a sting value, so you have to use two quote marks ("") in place of one ("). I'll discuss this in a later post.

Variables in Criteria Expressions

So far, I've just used hard-coded values in the Criteria Expression. That's not the most useful application of domain functions. Domain functions become most useful when you use a variable for the value in the Criteria Expression.

For instance,

DSum("Price", "tblOrderDetails", "[OrderNum] = 1")

will return the total price for a particular order (1). (The value is not delimited with quotes in this case because order number is a numeric field.) But if I put this expression in the control source of a textbox on a continuous form, it would always show the same value, regardless of the other values on the screen.

However, if I use a variable in place of the hard-coded order number, it becomes much more useful. Taking the OrderID from each record, gives me:

DSum("Price", "tblOrderDetails", "[OrderNum] = " & Me.txtOrderID)

In the first example, it is calculating the total for order 1 regardless of the other values in the row. In the second, it calculates the total based on the order number of that row.

Criteria expressions in domain functions are confusing, so it might be useful to take a closer look at them, which I will do in my next post: Domain Functions Demystified: Criteria Expressions.

.

Should I use the Compact On Close feature of Access?

I generally try to dissuade people from using the Compacting on Close feature of Microsoft Access, especially in a multi-user environment. Why?

Well, there are a variety of problems.

First of all, if your database bloats so quickly that it needs to be compacted every time it closes, then you have a design issue that should be addressed. See my blog post: How can I How Can I Compact my Access Database Less Often?

The two major causes of bloat are 1) importing data to a temporary table for processing and 2) creating temporary, intermediary tables with Make-table queries or other methods.

The data import problem can be addressed by linking a table or file for processing rather than importing it. If it can't be linked, it can be imported into a second, temporary database and then linked. This temporary database can be deleted later. I have a sample on my website called ImportToTempDatabase.mdb, which illustrates how to do this.

The problem of intermediate tables can also be solved by use of a temporary, local database which you create at startup and delete on database close. The solution is similar to the data import solution.

Secondly, compacting a database that doesn't need it will actually slow down the database some.  Not a lot, perhaps, but some.  Every database needs working space that Access has to create.  If your database grows a lot on first opening. but then very little on subsequent uses, this isn't bloat. It's the normal working space that your database needs.  Removing that working space every time you close the database requires Access to create it again the next time you use it.

Thirdly, it may be useless. A majority of Access databases are (or should be) in a Front-End/Back-End (FE/BE) configuration. This is often called a "split" database, where the tables reside in a separate database from the one which holds the queries, forms, reports, and code. Compacting on Close will only effect the database actually being used, which in most cases is going to be the FE. Unfortunately, it is the BE where the bloat actually occurs, so at best, compacting on close is useless, at worst, it's actively dangerous. Which leads me to the next point.

Fourth, and most importantly, there is a chance of database corruption any time you compact the database.  Admittedly, with more reliable networking hardware and software, this chance is smaller than in years past, but it's still there.   A small database on your local drive probably has little chance of corruption.  However, even a single-user database if it's very large and on a network has a chance of some network glitch causing the whole compact/repair to fail.  A failure during a compact means the database is toast.  There is no recovery.

A large, multi-user database on a network has a much greater chance of failure. 

So, I prefer to do a compact as a deliberate action rather than a hidden default.  Often, when compacting a large database on the network, I will copy it to my local drive, compact it locally, then upload it back to the network.  This gives me a backup in case anything goes wrong.

Also, if you compact the database programmatically (I have samples on my website: CompactDatabase.mdb and CompactBackEndDB.mdb), you have control to make a backup if you wish.