Thanks for the reply. I will mention now that the point
of the database is to keep track of employee training.
Training is often done on the fly, for instance as a
revised procedure necessitates refresher training. It is
rarely done from a set course list.
The trouble I was having was that Filter by Selection was
not available for the control (combo box). I did some
investigating, and discovered that the combo box from
which the user selects department, which has as its row
source a Department table, had Filter by Selection
enabled. My combo box's row source was derived from a SQL
statement that drew its data from previous training
sessions in the past 12 months. When I saved the SQL
statement as a stand-alone query, and used that query as
the row source, the filtering code you provided worked,
but with some curiosities about which I would like to ask
now.
First, I am trying to put everything the user needs in
plain sight on the form itself, whcih is why I don't want
to ask the users to right click, etc. To this end, I have
added record navigation buttons to the form, with the
following On Click event code (which the wizard provided):
Private Sub cmdNext_Click()
On Error GoTo Err_cmdNext_Click
DoCmd.GoToRecord , , acNext
Exit_cmdNext_Click:
Exit Sub
Err_cmdNext_Click:
MsgBox Err.Description
Resume Exit_cmdNext_Click
End Sub
There is similar code for the Previous Record button.
The forms's On Current event is:
cmdPrevious.Enabled = Not Me.CurrentRecord = 1
cmdNext.Enabled = (Me.CurrentRecord = 1 And
Me.Recordset.RecordCount > 1) Or Me.CurrentRecord <
Me.Recordset.RecordCount
The code occupies two lines. I don't know how it might
appear with wrapping in this forum. The code enables the
navigation buttons only when there is either a previous or
a next record. At the last record, the cmdNext button is
grayed out.
Back to the filter. When I select a part number from the
combo box, the built-in navigation buttons are enabled,
but the ones I added are not enabled (even when there are
several records) until I tab out of the combo box (and
into the first control in the tab order). Clicking in
another field does not work.
Also, I would like for the lookup combo boxes to be in the
form header, so that I can make the header invisible
unless somebody clicks a button in the detail section.
(The form is used to enter new records as well as search
for existing ones. When it is to be used for entering new
records I would prefer that the search options are not
seen.) However, when the combo box is in the header, only
the built in navigation buttons are enabled when the
filter is applied. I cannot get the navigation buttons I
added to be enabled except by clicking on one of the built
in ones. Tabbing does not work, because apparently I
cannot tab out of the header and into the detail section.
I expect I am missing something pretty basic here, but
darned if I can figure out what.
I have added Me.FilterOn = False to the form's On Open
event. I would also like a command button to do the same
thing. I added the code to the On Click event of a
command button on the form. I also added the code listed
above to enable the buttons. However, I need to click the
button twice (not double click, but twice at any time
interval) before the custom navigation buttons are
enabled. Clicking on the built in navigation buttons also
enables the custom ones.
Similarly, I have an unbound text box (txtRecordCount) on
the form. The form's On Current event includes
Me.txtRecordCount = Me.Recordset.RecordCount (or maybe it
should be Me!txtRecordCount = Me.Recordset.RecordCount. I
found the code in a newsgroup). This is to display the
number of records produced by a search. For a filtered
set of records, the number shows as 1 until I tab to the
first field, as described earlier, at which time the count
is accurate. When I click the command button to remove
the filter, then click it again to enable the custom
navigation arrow, I need to click the arrow to move to a
new record before the count is accurate. What it amounts
to is that I need to navigate to a new record before the
count is accurate.
I realize this is a lot of stuff. A lot of this is fine
tuning, but it is necessary that everything be as
accessible as is possible. There are people here who will
decide this is just too difficult if they need to remember
to right click a control or something like that.
Everything a user might need to do must be in the open, or
at least be accessible by a single button click (e.g.
going to another form or to a report).
-----Original Message-----
I can apply filter by selection to a table. I would like
to accomplish the same thing (even if it is called
something else) using a form as the user interface. I can
use a combo box (cboPartNumber) with Unique Values set to
Yes to produce a drop-down list of all part numbers for
which there is a record in the source query. Now I would
like the form to display all records for that part
number. I have been prowling around newsgroups for
several hours, and have discovered many tantalizing
suggestions, but so far nothing that has actually helped.
One requirement is that it be either the after update
event of the combo box, a command button, or something
else of the sort that keeps the user in the same area of
the form. Sending the user to the menu, even a simplified
custom menu, is the last choice.
If what I am attempting can be done, I would like next to
be able to filter by several cirteria (for instance, to
find all instances of Part 123 that have had process 987
performed). This first part is pretty important. This
next part would be nice, but is not worth a lot of extra
work right now.
First, before you go writing a whole lot of code, notice that the
default right-click menu for controls on a form includes Filter by
Selection. So your users can impose a filter without having to go up to
the menu or tool bar.
It's easy enough to get the combo box to impose a one- field filter.
Code for the combo's AfterUpdate event might look like:
'----- start of code -----
Private Sub cboPartNumber_AfterUpdate()
Me.Filter = "PartNumber = " & Me.cboPartNumber
Me.FilterOn = True
End Sub
'----- end of code -----
That assumes that PartNumber is a number field. If it's text, use
Me.Filter = "PartNumber = '" & Me.cboPartNumber & "'"
Note, by the way, that cboPartNumber should be unbound. You don't want
to use a bound control for this.
Combining two fields for the filter, like part number and process, can
be done with only a bit more complex code. Assuming you have unbound
controls for both -- let's assume for now that you're using a combo box,
cboProcess, for the Process field -- you might put a function something
like this into the General section of your form's class module:
'----- start of "air" code -----
Function BuildAndApplyFilter()
Dim strFilter As String
If Not IsNull(Me.cboPartNumber) Then
strFilter = " AND PartNumber = " & Me.cboPartNumber
End If
If Not IsNull(Me.cboProcess) Then
strFilter = " AND Process = " & Me.cboProcess
End If
If Len(strFilter) = 0 Then
Me.FilterOn = False
Me.Filter = vbNullString
Else
Me.Filter = Mid$(strFilter, 6)
Me.FilterOn = True
End If
End Function
'----- end of "air" code -----
Then you could call it directly from the AfterUpdate event properties of
the cboPartNumber and cboProcess controls, by setting each of them to
=BuildAndApplyFilter()
instead of to
[Event Procedure]
--
Dirk Goldgar, MS Access MVP
www.datagnostics.com
(please reply to the newsgroup)
.
