This month, Ken takes on three topics: modifying the Top Values property of a query, selecting items from a hierarchical list on a form, and why two equivalent strings really aren’t. The next two topics apply equally to both Access 2 and Access 95, and the final topic applies only to Access 95. Remember, if you have questions you’d like to see covered here, contact Ken using one of the means listed at the end of the article, or at the Pinnacle Publishing address on page 2.
I have a report that prints out all my customers. I’d like to limit the number of rows it prints, each time it prints, to a different number. It seems like I should be able to set an underlying query’s “Top N” property programmatically, but I can’t find the appropriate property that will let me set it. I see the “Top Values” entry in the property sheet, but there’s no corresponding TopValues property for a query. How can I set, at runtime, the number of rows that my report will display? You can’t find the appropriate property to set because there’s no such property. The Top Values entry in the property sheet corresponds to the “TOP n” clause of a query’s SQL statement, but it’s not exposed as a simple property of the query. If you want to modify this setting at runtime, you must build a new SQL string and apply it to the saved query.
Suppose the sample query, qryTopCustomers, requests the top five customers, in Primary Key order. (OK, so it’s not the most useful example, but the concepts still hold.) It’s SQL string is as follows:
SELECT DISTINCTROW TOP 5 Customers.* FROM Customers;
If you want to allow the user to select the number of customers the query returns, you’ll need to modify that SQL expression and replace it. In your instance, the simplest place to do that is from the report’s Open event. If you attach code to this event, you’ll be able to request the number of rows to print, and if the user enters an invalid value or no value at all, you can still cancel the printing of the report.
The simplest event procedure that will do what you need might look like this:
Sub Report_Open (Cancel As Integer)
Dim strSQL As String
Dim qdf As QueryDef
Dim db As Database
Dim strItem As String
strItem = InputBox( _
"How many customers do you need?")
If Not IsNumeric(strItem) Then
Cancel = True
Else
strSQL = "SELECT DISTINCTROW TOP " & _
strItem & " Customers.* FROM Customers;"
Set db = CurrentDB()
Set qdf = db.QueryDefs("qryTopCustomers")
qdf.SQL = strSQL
End If
End Sub
(You can find this example in rptTopCustomers in this month’s sample database in download file _GETZ26.EXE.)
This code does its work by first requesting a numeric value from the user. If InputBox returns anything that isn’t numeric, the code sets the Cancel parameter to True, so Access knows to cancel the report’s Open event and exit. If InputBox returns a numeric value, the code builds up a new SQL string, incorporates that value, and then sets the SQL property of the appropriate querydef. Finally, when Access opens the report it’ll use the new SQL string that you just created as it runs the underlying query. Once you’ve run the example, open qryTopCustomers to verify that the SQL string was changed as you opened the report.
This technique doesn’t actually require a saved querydef object. You can accomplish the same task by setting the report’s RecordSource to be the new SQL string. This method not only preserves all your persistent objects (it doesn’t modify any existing query’s SQL), but it also runs a bit faster because Access doesn’t need to take the time to update the query on disk.
You may also find this technique useful if you need to modify the column headings on a crosstab query. This property appears in the property sheet, but also isn’t a real property. You must modify the query’s SQL string to change the column headings. Look at the SQL generated by a query with its column headings set to study the syntax.
I have a table containing hierarchical reference numbers, such as 1.1, 1.1.1, 1.2, 1.2.1, 1.2.1.1, and so on. I need to have a form where I can choose one of these reference numbers, display it in a list box on the form, and show all the values subordinate to the chosen item. For example, if I choose 1.1, I need to see any item whose reference number starts with 1.1 (but not 1.11) in the list box. Can I do this?
Dominick Mormile
Houston, Texas
This turns out to be a rather interesting technique. I have to start by saying that this data isn’t normalized in the most expeditious manner, but there’s no reasonable way to change it, and it matches many in-place systems for storing hierarchical references.
The solution hinges on the use of a parameter query to fill the list box at the bottom of the form. Figure 1 shows the sample form (frmReferences), after selecting a single item from the combo box (double-clicking an item in the list box has the same effect: it places the value in the combo box, and then requeries the list box). Play with the sample form until you understand how it works.
Figure 1
The query that fills the list box takes its parameter from the combo box on the sample form. If that value is Null, the query uses the following (matching any value) as the parameter on the Reference column:
Like "*"
If the combo box’s value is not Null, it uses the following so that if you’d selected “2”, for instance, it would look for values matching “2.*”:
Like Forms!frmReferences!cboSelected & ".*"
To make the list box’s RowSource property fill the list correctly, the SQL it actually uses looks like this:
Like IIf(IsNull([Forms]![frmReferences]![cboSelected]), _ "", [Forms]![frmReferences]![cboSelected] & ".") & "*"))
This expression creates the correct parameter value, whether or not the combo box’s value is Null.
Once you’ve made a selection from the combo box, you must tell Access to refill the list box’s values. This requires you to use the list box’s Requery method, called from the combo box’s AfterUpdate event:
Sub cboSelected_AfterUpdate () Me!lstSelected.Requery End Sub
After double-clicking on the list box, place the selected value into the combo box, and then requery the list box. This code, called from the list box’s DblClick event, does the work:
Sub lstSelected_DblClick (Cancel As Integer) Me!cboSelected = Me!lstSelected Me!lstSelected.Requery End Sub
The list box’s RowSource query, combined with the two event procedures, make an interesting way to select subordinate values from a hierarchical list.
I’ve been programming in Access for several years and recently converted to Microsoft Access for Windows 95. I’ve written code that calls the Windows API, and to pass a null string to that API call I’ve created a constant containing an empty string:
Const conNullString = ""
I passed this to an API call where the function requires a null string, but the API call doesn’t work. If I replace this user-defined constant with the built-in constant, vbNullString, the API call works. This surprised me, because if I type the following in the Debug Window, I get a True response:
? conNullString = vbNullString
What’s going on here? Are the strings equal, or not?
The values these two strings point to are equal, but the values themselves are not. They’re just pointers to strings. Just as in any other language that supports pointers (and you’re getting there when you start calling API functions), you’re passing the pointer to a string to that API call (when you say ByVal stringName in the declaration, you’re passing the address of the string. If you pass it by reference, you’re passing the address of the address of the string). Access always dereferences pointers when you ask for a string value — there’s no way to get the real value of the variable, which is the address of the string.
So, it’s quite possible that two strings will compare equally — they point to the same value — but have their values not be equal when passed along to an API call. When you send conNullString, you’re passing the address of that memory variable. When you pass vbNullString, you’re passing the address of that string, which is defined internally as being Null as far as the API call is concerned. (It’s not the value of the string that’s getting passed to the API call, but some sort of address, which can’t be equivalent for both strings in this case).
Think of it this way: if there was an Address() function in Access that returned the address of this string it would return a False value:
? Address(vbNullString) = Address(conNullString)
But there’s no such function, and there’s no way to find the address, so there’s no way to guess about this behavior. The best source of information is VB4DLL.TXT, a text file that comes with the professional edition of Visual Basic 4. This useful document explains all this stuff in excruciating detail. At this point, it’s the ultimate reference if you want to work with VB or Access and external libraries.
Sidebar: Access Answers Corrections
In the Access Answers column in the November 1995 issue, there were a couple of mistakes in the DMedian() function. You need to replace the following section of code. The two changed lines are noted with comments (I left out the comments found original function):
lngPos = lngCount \ 2 rst.MoveFirst If lngPos > 0 Then rst.Move lngPos ' *** Change this End If varValue = rst(strField) If lngCount MOD 2 = 0 Then rst.MovePrevious ' *** Change this varValue = (varValue + rst(strField)) / 2 End If
Also, in the Access Answers column in January 1996, I wrote an answer to a question dealing with calculating dates. In my attempt to write the shortest formula that made sense, I made a small oversight. The formula returns an invalid result if you calculate someone’s birthdate on their date of birth, and they were born in a leap year.
Here’s the corrected formula:
=DateDiff("yyyy", [Birthdate], Date()) + _
(Date() < DateSerial(Year(Date()), _
Month([Birthdate]), Day([Birthdate])))
Other Page That You Might Want To Read
Create Dynamic Chart Applications
Giving Your Access Form Controls Web-style Behaviors
