At some stage in the last ten years, the behavior of what the Control Break keys did to queries changed. Before the Ctrl-Break key sequence used to stop the query when it had finished its action. Now the way it works depends on how big the transaction is.

If you press Control Break it stops the query running. This has two different behaviors

Small Queries) If the query can be managed in memory, the query is NOT committed and your data does not change.

Big Queries) If the query does 10,000s of lines of updates and Access cannot handle the query in memory, it stops the query and all actions taken are committed. That is they are NOT ROLLED back.

For big queries, this is a possible disaster because you really do not know the state of your data.

You Have A Big Query When You Get this prompt

There isn’t Enough disk space or memory to undo the data changes this action query is about to make

So Is This A Problem ?

If this only happened when you pressed Control Break, it wouldn’t matter because it is unlikely your users are going to do this. What does matter is that your user turns off their computer (in frustration because the query is taking so long) or finds some other way to interrupt the process. We have even had instances where switching to Excel triggers the Control Break behaviour. Then you have to decide, does this make my database incorrect.

Solution 🙁

I don’t have a great solution for you apart from breaking up the query into smaller updates so it only effects smaller number of records. That way if the process is stopped mid stream, you know that the data is in its original state.

Another clunky way to avoid the problem is to inform your users with a message box or popup form that this query takes a long time so that they don’t become impatient.

Garry Robinson
Microsoft Access MVP since 2006

1.1.     Manage your Update Tables with Query Lists

  Keith Bombard        

In this article, Keith Bombard shows off a routine that can reduce the time it takes to create a form to manage your update tables. This general-purpose routine can be used with any small table for editing, updating, adding, and deleting records.

Very early in my programming career (six or seven years ago), I was good at finding fault with other people’s code. My rule was that if I didn’t write it (and didn’t take the time to fully understand it), it wasn’t any good. I now have to admit that because of this rather immature and naïve notion, I ignored a lot of solid code written by folks who are a lot smarter than I am, which undoubtedly delayed my entrance into the professional ranks. Fortunately, as my coding skills improved, so did my acceptance of other peoples’ code. The topic of this article is an example of someone else’s code that I liked and adopted.

A lookup-table editor    

Several years ago, a large firm acquired a smaller firm, and with it came a huge Access 2.0 system written by a team of professional Access consultants (note: not an oxymoron). The acquiring company asked me to analyze this system and make a recommendation about whether to keep it or rewrite it. At the very least, we were to convert it to Access 97.

How big was the system? Well, at the time, I had no idea Access systems could get that big. I had lots of code to look at, and I was fascinated by this system. I had the feeling that someone who was familiar with coding in Visual Basic but not familiar with Access had written it. Some of the clues: queries written in native SQL, connect strings used for batch updating in unbound forms in lieu of attached tables, lots of API calls, and so on. It was different enough to be interesting.

One neat feature I remembered was a generic lookup-table editing routine. This routine took a lookup-table name as a parameter and instantly produced a form to add, change, or delete source table data. This routine was remarkable in that this one generic routine provided the editing platform for all of the small lookup tables in the system. It wasn’t perfect. The routine only worked on small tables, and the field names on the form were of the “non-descriptive” type (Field1, Field2, and so on). But the idea was a good one. Anything that cuts down on the custom-code aspect of a system and makes processing consistent and generic is a step in the right direction.

Two years elapsed between my exposure to this routine and my own re-creation of it as Query Lists (QLs). A QL bears only a conceptual resemblance to the lookup-table editor that I described, but my code is new.

What they do and how they work

A QL turns any Select query, SQL statement, or report RecordSource into a key-record lookup platform. This is useful because users can then perform online lookups from reports or queries in the database, simply by clicking the mouse on any record in a datasheet view window.

In a QL, a query or SQL statement pipes its data into a special “semi-generic” form, which is reconstructed each time the source query runs. Each form field inherits some of the properties (field types, size, and attributes, but not name) of the query’s source fields.

During each QL call, the generic form is loaded in design view in a hidden view. All of the generic form’s labels and ControlSource properties are then updated to match the field names supplied by the query. Some of the generic form’s field format properties are also manipulated. The form is then saved, closed, and reopened in datasheet view to present the data to the user. In case you forgot, Access uses the field tag label names as the default field captions in a form opened in datasheet mode. I use this feature to ensure that the display looks just like a table view or a query result when, in fact, it’s an open form complete with event processing.

You might be wondering why I’d go to all this trouble to present the user with a datasheet view. The answer lies in the user’s ability to toggle back and forth from the report environment to the lookup environment. Behind the dbl-click event of every field lurks a tailored docmd.OpenForm statement that can launch a key lookup form. Employing this technique is a great way to let your users do online research based on report and query data.

There are a few things to keep in mind when using Query Lists:

• The source query should always have the key lookup field as its first field. The sample code in the CARS sample database in the accompanying Download file is written this way. For the purist, requiring that the first field in a query be a specific field is a violation of the principles of database design. If you have trouble with this requirement, consider adding code to examine, sequentially, the form’s label fields, looking for the key lookup field. When you find it, use this form field as the criteria string in the docmd.Openform statement.
• The QLs only work with Select statements or queries (that is, not with Action queries).
• There’s very little error trapping provided in the sample db.
• OLE fields require special treatment in the lookup form. If your query produces OLE fields, always be sure to add OLE field type(s) to your lookup form, and make sure that the order of the OLE fields matches the field positions in the resulting recordset. For example, in the CARS database, the OLE picture field in the report is the fourth field in the report’s recordset. It’s also the fourth field in the form. If you neglect to do this and you’re using OLE bound fields, be prepared for errors.

The CARS database—a Query List example

I’ve created the CARS sample database to illustrate how Query Lists can be used. In Figure 1, you can see the form from the CARS database for demonstrating Query Lists.

    Figure 2
Figure 1

In this example, QLs are selected from a Report setup/menu form. Clicking the form’s default Report button launches a simple CARS report. Checking the “Call up List instead of Report” check box switches the function from using a report to using a QL (and also changes the caption on the command button to “Listing” from “Report,” as shown in the figure). Clicking the Listing button calls the ShowListing function to launch the QL:

Function ShowListing(RecSource As String, _

Cap As String, WriteTotals As Boolean, _

Optional MinLP As Variant)

If Me.chkListing Then

Call ShowListing(GetReportRecordsource(“rptCars1”), _

“Query List Sample App; ” & _

“(dbl-click the AppKeyField)”, _

Me.chkTotals, Me.MinimumListPrice)

Else

DoCmd.OpenReport “rptCars1”, acViewPreview

End If

The ShowListing function must be passed an SQL statement. In this example, I’ve used another routine of mine, GetReportRecordsource, to pull the value of the RecordSource property from the report rptCars1.

The ShowListing routine uses a “semi-generic” form called frmQueryList to display the QL data in a datasheet (see Figure 2). This form has 30 fields in it, named Text1 to Text30, and each field has its own label, named Label1 to Label30. Data from the selected report is piped into this form by the ShowListing function. The number of fields in the form must match the global variable glbMaxFields (you can have as many as 255 fields in the form). The first field in this form should be the AppKeyField field. The dbl-click event of this field uses the AppKeyField to launch the lookup form for the selected record (you’ll need to change the form that’s launched for each query list):

If Not IsNull(Me.Text1) And _

Me.Text1.ControlSource = “AppKeyField” Then

DoCmd.OpenForm “frmCars”, , , _

“AppKeyField = forms!frmQueryList!Text1”

End If

The function GetReportRecordsource() returns the RecordSource of the report. First, it opens the report, and then it retrieves the RecordSource, closes the report, and returns the RecordSource:

Function GetReportRecordsource(MyReportName) As String

DoCmd.OpenReport MyReportName, acViewDesign

Set RP = Reports(MyReportName)

RS_Temp = RP.RecordSource

GetReportRecordsource = RS_Temp

DoCmd.Close acReport, MyReportName

End Report

The QL routine needs a raw SQL statement to work properly, because any form-field criteria in the query must be resolved first to avoid the dreaded “Too Few Parameters” error when retrieving data. My GetSQL handles this. The GetSQL statement checks to see whether a query exists in the database that matches the value of the RecordSource that was passed to it. If so, the routine returns the query’s SQL statement. Otherwise, I assume that the RecordSource contains an SQL statement and return it:

Function GetSQL(Qname As String, db As Database) _

As String

GetSQL = “”

For Each qdf In db.QueryDefs

If Trim$(qdf.Name) = Trim$(Qname) Then

TQ = Left(qdf.SQL, Len(qdf.SQL) – 3)

GetSQL = TQ

Exit For

End If

Next qdf

End Function

Now, form field variables must be resolved. I do this by performing a direct substitution of actual values into the SQL string. My Substitute function does the trick here. In the CARS sample database, the criterion in effect is a minimum list price for a vehicle. Here, the form field MinimumListPrice is substituted for the optional parameter MinLP that’s passed into the ShowListing routine:

If Not IsMissing(MinLP) Then

Substitute “[Forms]![frmPrintReportSetup]! _

[MinimumListPrice]”, RecSource, CStr(MinLP)

End If

This is another area that will require customization, by the way.

If you choose to use form date fields as criteria, make sure you add the “#” characters to the front and back of the resolved date value. An example of this follows, where StartDate is an optional date parameter passed into the function from the setup form:

If Not IsMissing(StartDate) Then

Substitute “[Forms]![frmPrintReportSetup]! _

[StartDate]”, RecSource, “#” & CStr(StartDate) & “#”

End If

Here’s the Substitute function itself:

Function Substitute(WhatStr As String, _

IntoStr As String, ReplaceStr As String) As String

Dim Floc As Integer

Dim BS As String

Floc = InStr(1, IntoStr, WhatStr, 2)

If Floc = 0 Then

Substitute = IntoStr

Exit Function

End If

Do While Floc > 0

BS = Mid$(IntoStr, 1, Floc – 1) & ReplaceStr & _

Mid$(IntoStr, Floc + Len(WhatStr), _

1 + Len(IntoStr) – (Floc + Len(WhatStr)))

IntoStr = BS

Floc = InStr(1, IntoStr, WhatStr, 2)

Loop

Substitute = BS

End Function

With the SQL statement retrieved, you can access the database and open the Recordset. The Lookup form fields are then manipulated to match the properties of the fields in the Recordset. I do this by opening the form in design view, hidden mode. I then set the form’s Caption and OrderBy properties. The caption is another parameter passed in to the ShowListing function:

Set db = CurrentDb

Set rs = db.OpenRecordset(RecSource, dbOpenSnapshot)

DoCmd.OpenForm “frmQueryList”, acDesign, _

, , , acHidden

Set frm = Forms!frmQueryList

frm.Caption = Cap

frm.OrderBy = Empty

The next code section loops through all of the fields in the report’s Recordset, one field at a time. First, I change the form’s label fields captions to the query’s field names, so that when the user opens the form in Datasheet view the field names will be displayed as column headings. Then I change the form field’s control source strings to the RecordSource field names and manipulate the form’s field formats to be consistent with the RecordSource’s data types. Formatting changes here aid in the consistent presentation of data to the user. I change the label captions and the field ControlSources to the query’s field names. If you have special formatting requirements for your data, specify it here. At the end of the loop, I assign the MaxFields value, to be used later:

i = 0

For Each F In rs.Fields

i = i + 1

frm(“Label” & CStr(i)).Caption = F.Name

frm(“Text” & CStr(i)).ControlSource = F.Name

If F.Type = dbCurrency Then

frm(“Text” & CStr(i)).Format = “Currency”

End If

If F.Type = dbDate Then

frm(“Text” & CStr(i)).Format = “mm-dd-yyyy”

End If

If F.Type = dbBoolean Then

frm(“Text” & CStr(i)).Format = “Yes/No”

Else

If F.Type <> dbLongBinary Then _

frm(“Text” & CStr(i)).Format = Empty

End If

If i >= glbMaxFields Then Exit For

Next F

MaxFields = i

Next, I make the form’s RecordSource a query, qryTempLookupList. This is necessary because Access has a 2048-byte limit to the size of SQL statements. If I make the form’s RecordSource an SQL statement, there’s a danger that the operation will go down with an error if the SQL statement exceeds 2048 bytes.

To create the temporary query, I first check to see whether this query exists in the database. If the query already exists, I delete it, and re-create it using the new RecordSource SQL string:

If DoesQueryExist(“qryTempLookupList”, CurrentDb) _

Then db.QueryDefs.Delete “qryTempLookupList”

db.CreateQueryDef “qryTempLookupList”, RecSource

frm.RecordSource = “qryTempLookupList”

With my form generated, I save all of the changes made to the form in design mode:

DoCmd.Save acForm, “frmQueryList”

The next code section computes the count and total values if the Write-totals check box on the setup form is set to True. This sample code displays the count of AppKeyField values and the sum of any Recordset field that’s formatted as Currency. The computed values are displayed in parentheses following the caption of the field. After making these changes, I close the form, saving my changes:

If WriteTotals Then

i = 1

For Each F In rs.Fields

If F.Type = dbCurrency Then

frm(“Label” & CStr(i)).Caption = _

frm(“Label” & CStr(i)).Caption & _

” (” & Format$(DSum(F.Name, “qryTempLookupList”), _

“$#,##0.00”) & “)”

Else

If F.Name = “AppKeyField” Then

frm(“Label” & CStr(i)).Caption = _

frm(“Label” & CStr(i)).Caption & _

” (” & Format$(DCount(F.Name, _

“qryTempLookupList”), “#,###”) & “)”

End If

End If

i = i + 1

If i > glbMaxFields Then

Exit For

End If

Next F

End If

DoCmd.Close acForm, “frmQueryList”, acSaveYes

rs.Close

With the ad hoc customizations made, I open the form again to display the new data, this time in datasheet mode. Once I’ve reopened the form and got a reference to it, I make any fields past my glbMaxField limit invisible:

DoCmd.OpenForm “frmQueryList”, acFormDS

Set frm = Forms!frmQueryList

For i = 1 To glbMaxFields

If i > MaxFields Then

frm(“Text” & CStr(i)).ColumnHidden = True

Else

frm(“Text” & CStr(i)).ColumnHidden = False

End If

Next i

ShowListing = True

Voilà! In Figure 3 you see a report, and in Figure 4, a query listing. In Figure 5, the lookup form associated with this QL is displayed.

 
Figure 3

I know if you take the time to sample the CARS database and try Query Lists out for size, you’ll be surprised how much they’ll add to your application. I also hope you’re further tempted to take a few more minutes out of your day to sit down and review the code to see how it works… something I should have done more of back when I got started.

 Figure 4

 Figure 5

 Your download file is called  Bombard_Query_List.accdb   and you can download it on this page Queries >>