Manage your Update Tables with Query Lists

<< Click to Display Table of Contents >>

Navigation:  Queries >

Manage your Update Tables with Query Lists

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

200108_kb1   200108_kb2 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)


   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)



 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"


     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") & ")"


  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


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


       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.



200108_kb4 Figure 4


200108_kb5 Figure 5


Your download file is called  108BOMBARD.ZIP   in the file

This can be purchased with all the other downloads on this page


Other Articles

Consolidating Your Data With Queries
SubQueries to the Rescue
Query-Based Applications