Forms, Sorting Reports, and Dating Errors

This month, Andy Baron addresses a variety of issues, including several tips related to forms, a look at how to dynamically sort reports, information on an undocumented feature of Access, and a discussion of how VBA handles certain date/time calculations.

I have a combo box based on a query with criteria that references various values on my form. In certain situations, the query won’t return any records for selection, in which case I want to disable the combo box. How can I detect that the query didn’t return any records after doing a requery?

There are a couple of solutions that you could use. You could open a recordset based on the current row source of the combo box and check the recordcount of the recordset:

Dim rst As Recordset

Me!MyCombo.Requery

Set rst = _

CurrentDb.OpenRecordset(Me!MyCombo.RecordSource)

Me!MyCombo.Enabled = (rst.RecordCount > 0)

However, assuming that the bound column of the combo box wouldn’t normally contain a null value, there’s a much easier and faster technique you can use. Just check whether the first row of the ComboBox is null:

Me!MyCombo.Requery

Me!MyCombo.Enabled = Not IsNull(Me!MyCombo.ItemData(0))

The code Debug.Print DateAdd(“h”, -3, #12:00 AM#) returns 3:00 AM, but it should return 9:00 PM. It functions correctly when I use +3, returning 3:00 AM. Is this a known bug?

This isn’t a bug. It’s an artifact of the date/time storage method used by VBA. All date/time values are double precision numbers with two parts. The integer part refers to the day. The fractional part refers to the time, as a portion of a 24-hour day.

The starting point in your example, #12:00 AM#, is the zero point in this numeric system: the number 0.0. Although you’re not displaying the date, this value represents midnight on 12/30/1899, which is day 0 in the VBA date system.

When you subtract a value less than 1 from this zero value, the result is a negative fraction. If you subtract three hours — which is 1/8 of a day, or .125 — from 0, the result is -0.125. If, instead, you add three hours, the result is +0.125. However, to VBA, the integer part of both numbers (-0 or +0) still refers to the same day, 12/30/1899. Therefore, -0.125 and +0.125 both refer to the same date/time: 12/30/1899 3:00 AM.

The decimal part of the “date number,” which represents the time of day, always counts forward from midnight on the day that’s represented by the integer part of the number. To get the answer that you want, 12/29/1899 9:00 PM, you need to generate the number -1.875: one day before day 0 and 7/8 of the way through the day.

DateAdd does seem to be trying to handle negative dates properly. For example, the following works fine:

?DateAdd("h",-3, #12/1/1899 1:00 AM#)

11/30/1899 10:00:00 PM

But here’s an example where it fails, giving the correct time but the wrong date:

?DateAdd("h",-1, #12/1/1899 12:00 AM#)

12/2/1899 11:00:00 PM

That, I would say, is indeed a bug.

Use date functions with caution if you’re dealing with negative dates, and remember that VBA always stores and works with a full date, even when you’re only displaying the time.

I have an application that allows you to filter out records and produce reports based upon those. It works fine. But now I want to be able to sort the report based on user selection (for instance, Sort by Company, then by LastName). This works fine on a form level using SQL statements: The subform displays exactly what I want to have on a report. But when I do the report preview, it’s always sorted by the same field (LastName) and totally ignores all attempts to ORDER BY anything! Setting the report’s OrderBy and OrderByOn properties had no effect. Setting the query that drives the report to sort the way I want doesn’t work either. I can’t understand why my report filters out the records it doesn’t need, but ignores everything that I try to order the records!

Report filters are criteria that are layered on top of any criteria defined in the record source of the report. As you discovered, report sorting doesn’t exactly follow the same model as report filtering.

The Order By clause of the report’s record source query is always ignored. Instead, Access uses two other sets of properties to sort the report: the OrderBy/OrderByOn properties, and a set that you didn’t mention, the report’s GroupLevels. The GroupLevels property holds an array of objects, each with its own properties. GroupLevels corresponds to the values that you set in the Sorting and Grouping dialog box when working with the report in design view, and these GroupLevels always take precedence over the sorting you apply using OrderBy. From your description, it sounds like you have a GroupLevel defined for the LastName field.

Fortunately, the properties of each GroupLevel, including the field or expression it’s bound to, can be set at runtime in the Open event of the form. The code would look something like this:

With Me

Select Case Forms!frmChooseSort!grpSort

Case 1 'Name

.GroupLevel(0).ControlSource = "LastName"

.GroupLevel(1).ControlSource = "FirstName"

.GroupLevel(2).ControlSource = "Company"

Case 2 'Company

.GroupLevel(0).ControlSource = "Company"

.GroupLevel(1).ControlSource = "LastName"

.GroupLevel(2).ControlSource = "FirstName"

End Select

End With

When you use the OrderBy property, you use syntax that’s identical to what’s used in a SQL Order By clause, so a descending sort is specified by adding “DESC” after a field name. When working with GroupLevels, however, you need to set the GroupLevel member’s SortOrder property to True to get get a descending sort (the default is False, which sorts ascending):

Me.GroupLevel(0).SortOrder=True

You can’t create new GroupLevel members programmatically unless you open the report in design view, so be sure that you created as many grouping levels as you need when you’re designing your report. If necessary, you can create dummy group levels that will have no effect by giving them a control source that’s an expression like: =1.

My goal is to provide a button that, when pushed, will close the database window, so that Access will still be open, but without any database open. I have one way of doing it if the Database window is visible. I count all of the open objects and then issue a DoCmd.Close for every open object plus one more for the database window. If the database window is hidden, I can unhide it, but then I get the dialog box asking me which I want to unhide. How do I unhide the database window without getting this prompt? Or how do I close just the database window even if it’s hidden?

You can unhide the database window by using SelectObject to select an object in it. If you specify the type of object but not which one, then that tab of the window will become active and the last selected object of that type will be selected. You could then close the database:

Public Function CloseDB()

DoCmd.SelectObject acForm, , True

DoCmd.RunCommand acCmdClose

End Function

However, it isn’t necessary to do any of what you’re doing if all you want is to close the database. There’s an automation method of the Access Application object that you can use:

Public Function CloseDB2()

CloseCurrentDatabase

End Function

I have a form where the user sometimes adds new records and I sometimes add and save new records in code. I have a BeforeInsert event procedure to fill in certain required fields on all new records. But the BeforeInsert code never seems to run when I add records with code. What am I doing wrong?

The BeforeInsert event will only fire if the new record is dirtied by the user, and it fires when the user first dirties a new record, not when it’s saved. Dirtying the record in code doesn’t cause the event to fire. This is contrary to what you’d expect. It’s quite possible for a new record to be created and saved without BeforeInsert ever firing.

BeforeUpdate, on the other hand, will always fire before a changed record is saved, even if the changes were made in code. If you want code to run before a new record is added, then you can use something like this:

Private Sub Form_BeforeUpdate(Cancel As Integer)

If Me.NewRecord Then

'Do what you need to here.

End If

End Sub

I have a transaction that’s rolled back when certain conditions are met. But there are changes made during this transaction that I don’t want to be reset. My idea was to create an additional Workspace and “hide” these changes in that workspace, which then wouldn’t get rolled back. Unfortunately, I discovered that the CreateWorkspace method of DBEngine requires a user name and password, and I definitely don’t want to leave mine in the code. Is there a way to get around this problem? Can I create a Workspace the way the default one, DBEngine(0), is created, using the name and password of the current user?

You’re in luck. There’s a little-known method in Access that exists exactly for the purpose you’ve described. It’s the DefaultWorkspaceClone method of the Application object. This allows you to create a second workspace using the security account of the of the current user:

Dim wrkClone as Workspace

Set wrkClone = Application.DefaultWorkspaceClone

I’ve got several tables with Yes/No fields in each. In one, DataSheet view shows check boxes. In all of the others, it shows Yes/No. All have Yes/No formats. What decides which way they display? I prefer check boxes, but I can’t seem to make the Yes/No display go away. When I remove the format, the fields display as 0/-1.

Open the table in Design view and select the Yes/No field. In the lower pane, where the field properties are listed, select the Lookup tab. You can then choose Check Box or Text Box (or Combo Box). This tab is usually used to define foreign key lookups, but you can use it just to set the default control. This default control will be used in datasheet view, and in form view when you use a wizard to create your form, and when you drag a field onto the form from the field list.

I have a form that’s currently listing a number of records based on a query. I want to allow the user to change the order of the records. Each record has its own Line#ID, which I create as new lines are added, and the form itself is sorted by Line#ID. I want to have Up and Down buttons that will move the selected row up or down by changing the Line#ID and requerying the form. I’ve tried using the RecordsetClone, but I can’t seem to get it to work right, and when I requery to display the records in their new order, the first record of the form gets selected. I want the selected record to stay selected so the user can move it several rows up or down by clicking the button several times without having to find the row again each time.


 Figure 1

The sample database for this month’s column — available in the accompanying Download file — contains the form that appears in Figure 1. It sounds like you have the row numbering part working okay, but here’s some code that might be useful to readers who are starting from scratch (all of the code in this answer appears in the sample database):

Public Function GetNextLineNumber(frm As Form) _

As Long

'Assumes calling form has field named LineNumber.

'Place the following line in the form's

'BeforeUpdate event procedure:

'   If IsNull(Me!LineNumber) Then _

'      Me!LineNumber = GetNextLineNumber(Me)

Dim rs As Recordset

Set rs = frm.RecordsetClone

If (rs.RecordCount) Then

' Not the first record

rs.MoveLast

GetNextLineNumber = rs!LineNumber + 1

Else

' First record

GetNextLineNumber = 1

End If

Set rs = Nothing

End Function

Once you have the procedure in place that increments the row number for each new row, you can use the MoveItem routine that follows to move the selected row up or down, and finish with the record still selected. This procedure would be called from the Click event of the up or down button. You need to pass the routine:

  • the direction you want to move (“Up” or “Down”)
  • a reference to the form (Me or Me!MySubform.Form)
  • a list of the field names, types, and values of that row’s primary key

To ensure that the line number for the row has been assigned, you’ll want to save the record before you start moving. For example, you might call the routine as follows:

Private Sub cmdMoveUp_Click()

Me.Dirty = False

MoveItem "Up", Me!fsubOrderDetails.Form, _

"OrderID", dbLong, Me!OrderID, _

"ProductID", dbText, _

Me!fsubOrderDetails!ProductID

End Sub

The MoveItem procedure uses the form’s RecordsetClone to adjust the line numbers. After the row has been moved, the routine calls Access BuildCriteria function passing it the information to find the moved row after the requery that displays the records in their new order:

Public Sub MoveItem(strUpDown As String, _

frm As Form, _

ParamArray avarPKFieldNamesTypesValues() _

As Variant)

On Error GoTo Handle_Err

'Assumes LineNumber is incremented when

'a new record is added.

'avarPKFields() is an array of the names, _

'types (dbText, dbLong, and so on), and values _

'of the form's primary key fields in this format:

'  name1,type1,value1,name2,type2,value2,etc.

Dim rs As Recordset

Dim fMoved As Boolean

Dim strCriteria As String

Dim intI As Integer

If Not IsNull(frm!LineNumber) And _

Not frm.NewRecord Then

Set rs = frm.RecordsetClone

rs.Bookmark = frm.Bookmark

If strUpDown = "Up" Then

rs.MovePrevious

If Not rs.BOF Then

rs.Edit

rs!LineNumber = rs!LineNumber + 1

rs.Update

rs.MoveNext

rs.Edit

rs!LineNumber = rs!LineNumber - 1

rs.Update

fMoved = True

Else

rs.MoveNext

End If

ElseIf strUpDown = "Down" Then

rs.MoveNext

If Not rs.EOF Then

rs.Edit

rs!LineNumber = rs!LineNumber - 1

rs.Update

rs.MovePrevious

rs.Edit

rs!LineNumber = rs!LineNumber + 1

rs.Update

fMoved = True

Else

rs.MovePrevious

End If

End If

If fMoved Then

'To reposition to the moved record,

'build criteria and use findfirst.

For intI = _

LBound(avarPKFieldNamesTypesValues) _

To UBound(avarPKFieldNamesTypesValues) _

Step 3

If Len(strCriteria) > 0 Then

strCriteria = strCriteria & " AND "

End If

strCriteria = strCriteria & _

BuildCriteria( _

avarPKFieldNamesTypesValues(intI), _

avarPKFieldNamesTypesValues(intI + 1), _

"=" & _

CStr(avarPKFieldNamesTypesValues(intI + 2)))

Next intI

'Requery to show new sort.

frm.Requery

'Reposition to original record.

Set rs = frm.RecordsetClone

rs.FindFirst strCriteria

If Not rs.NoMatch Then

frm.Bookmark = rs.Bookmark

End If

End If

Set rs = Nothing

End If

Exit_Here:

Exit Sub

Handle_Err:

Select Case Err.Number

Case Else

MsgBox "Error: " & Err.Number & _

vbCrLf & Err.Description, _

Title:="GetNextLineNumber"

End Select

Resume Exit_Here

End Sub

About Andy Baron

Although he still does a bit of Access work, he is mostly working on a completely new career. He enrolled in a three-year program to study and get licensed in Chinese medicine, learning acupuncture, herbology, etc.
This entry was posted in VBA. Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *


*


This site uses Akismet to reduce spam. Learn how your comment data is processed.