Opening a Report from a Filtered Form

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a client management database for a law firm which contains a "Client
Information" form. This form contains client data along with combo boxes
that allow users to assign an attorney, a status, a case type, etc. to the
particular record from drop down lists. These pre-set lists (attorneys,
status, etc.) are their own tables and the selected items are recorded in a
master table.

I am able to filter the data on the "Client Information" form nicely. For
example, I can see all clients assigned to a particular attorney using
filter-by-form and selecting that particular attorney from the drop down
menu. However, after filtering using a criteria from a combo list (such as
an attorney) and attempting to open a report using the following string:

DoCmd.OpenReport "Client Summary", acViewPreview, , Me.Filter

It gives me an error message box asking for input:

Enter Parameter Value (Lookup_Attorney.Initials)

It gives the same type of error when I try to open a report after filtering
the form using any of the combo box criteria (attorney, status, etc.). I
can't figure out what I'm doing wrong, but it seems to have something to do
with the fact that these combo boxes contain data from separate
tables...please help.
 
If you want to use the values form the lookup fields in the report, then you
will need to set up a query as the RecordSource of the report so that it
matches they way the form is set up.

1. Create a query into the main Client table.

2. Add the Attorney table to the query. Right-click it and choose
Properties. Set the Alias property for the table so it matches what the form
calls it, i.e.: Lookup_Attorney

3. If some of the Client records have no Attorney, double-click the line
joining the 2 tables. Access gives you a dialog with 3 choices. Choose the
one that says:
All records from Clients, and any matches from ...
If you don't do this, the query omits the clients who have no Attorney.

4. Repeat steps 2 and 3 for the other lookup tables.

5. Drag the fields you want from the various tables into the grid.

6. Save the query.

7. Open the report in design view, and set its RecordSource property to this
query.

That should allow you to use the values returned from the Filter By Form.
Personally, I prefer to provide an alternative interface where the user can
select the filters as it is more obvious, cleaner, more efficient, and works
in the runtime version as well.
 
Would you elaborate on your personal preference for the report. I'd like to
learn how you would do it.
Thanks!!!
 
Most forms have from 0 to 5 fields the user may need to filter on. We put
some unbound controls in the Form Header section (so they are clearly
separate from the main section) and color them differently (yellow), along
with 2 command buttons for Filter and RemoveFilter.

In the Click event of the Filter button, build up a Where string based on
the non-null boxes. If they are combos, the bound column contains the key
value, so there is no need to mess with lookup values. Some of the filter
boxes allow for a range, e.g. a "from" date and a "to" date.

-----------------code starts---------------
Private Sub cmdFilter_Click()
Dim strWhere As String
Dim lngLen As Long
Const conJetDate = "\#mm\/dd\/yyyy\#"

'Save First
If Me.Dirty Then Me.Dirty = False

'Concatenate each phrase to the Where string.
If Not IsNull(Me.txtFilterSurname) Then
strWhere = strWhere & "([Surname] Like """ & Me.txtFilterSurname &
"*"") AND "
End If

If Not IsNull(Me.txtFromDate) Then
strWhere = strWhere & "[InvoiceDate] >= " & Format(Me.txtFromDate,
strcJetDate) & ") AND "
End If

If Not IsNull(Me.txtEndDate) Then
strWhere = strWhere & "[InvoiceDate] < " & Format(Me.txtFromDate +
1, strcJetDate) & ") AND "
End If
lngLen = Len(strWhere) - 5 'Without trailing " AND ".
If lngLen > 0 Then
Me.Filter = Left(strWhere, lngLen)
Me.FilterOn = True
Else
MsgBox "No criteria"
End If
End Sub
-----------------code ends---------------

Notes:
1. Use = rather than Like where you can.

2. Omit the extra quotes for Number type fields.

3. The example using "less than the next date" rather than Between avoids
the problem where the last date is not included if the date/time field has a
time component.

4. The explicit save before filter avoids some bugs.

5. The explicit date formatting means your code will work correctly
regardless of the user's regional setting. If you set the Format property of
these unbound text boxes to "Short Date", it also prevents the user entering
invalid dates.


For the cmdClearFilter button, we actually set its On Click property to
exactly this:
=ClearFilterAndHeader([Form])
and put the function below into a standard module.

-----------------code starts---------------
Public Function ClearFilterAndHeader(frm As Form)
On Error GoTo Err_ClearFilterAndHeader
'Purpose: Remove the filter, and clear all the unbound contorls in the
form header.
Dim ctl As Control 'Each controls in the form header.

'Save if necessary.
If HasProperty(frm, "Dirty") Then
If frm.Dirty Then
frm.Dirty = False
End If
End If

'Remove the filter.
frm.FilterOn = False
frm.Filter = vbNullString

'Clear all the unbound controls in the form header.
For Each ctl In frm.Section(acHeader).Controls
If HasProperty(ctl, "ControlSource") Then
If Len(Nz(ctl.ControlSource, vbNullString)) = 0& Then
If Not IsNull(ctl.Value) Then
ctl.Value = Null
End If
End If
End If
Next

Exit_ClearFilterAndHeader:
Set ctl = Nothing
Exit Function

Err_ClearFilterAndHeader:
MsgBox "Error " & Err.Number & " - " & Err.Description
Resume Exit_ClearFilterAndHeader
End Function
Public Function HasProperty(obj As Object, strPropName As String) As Boolean
'Purpose: Return true if the object has the property.
Dim vardummy As Variant

On Error Resume Next
vardummy = obj.Properties(strPropName)
HasProperty = (Err.Number = 0)
End Function

-----------------code ends---------------

Hmm. Probably should set up a web page that explains that properly.
 
Back
Top