Filter records

[Bruce]

I have asked variants of this question before, and have
received a variety of answers, but none of them have
worked, probably because I left something out of my
description.
I have a training records database that works well for
entering training information. It contains 20 or so
fields, but let us say that it contains just the fields
[PrimaryKey], [Subject], [PartNumber], and [Department].
It works by entering training information on a form, and
attendance on a subform. The subform is linked to the
main form through the PK of the main form. It works as
intended, and can probably be ignored in considering the
following questions. The underlying table for training
session information is tblTrainingSession, and the
associated form is frmTrainingSession. The value in any
one of the fields I mentioned (except PK) is present for
many reords. I can right click on a field (on the form)
and filter by selection, so that for instance I could find
all records where the subject is Masking. The main
problems are 1) I first need to find a record with that
subject, and 2) it would be better if this could be
accomplished by combo box, command button, or other
visible control.
I would prefer to use a combo box to locate, say, Masking
as the subject. After that I woud like to find all
records that contain Masking as the subject. I can set up
a combo box (cboLookupSubject) with the following in the
After Update event:
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[PrimaryKey] = " & Str(Me!_
[cboLookupSubject])
Me.Bookmark = rs.Bookmark

The underscore is supposed to indicate no line break. I
don't remember if I got the code from the wizard or from a
newsgroup, and I don't fully understand what it is doing.
The combo box row source is:

SELECT DISTINCT tblTrainingSession.*,
tblTrainingSession.Subject
FROM tblTrainingSession
WHERE (((tblTrainingSession.Subject) Is Not Null))
ORDER BY tblTrainingSession.Subject;

This shows [Subject] for all of the records, but if there
are 20 records with the subject Masking, all 20 will show
up on the list. I can select one of those records, then
right click on [txtSubject] on the form to filter by
selection, but I would like to do all of that with fewer
steps and without the need for the right click menu.
What I need is for the combo box to display a list
containing no duplicate items. I would like the After
Update event for the combo box to filter by the combo box
selection, but a command button would be OK for that.
The main form is in the Detail section. Does it matter
where the combo box is located (Header, Detail, etc.), or
is one better than the other? Is it a problem doing this
on the same form where I add new records?

 

[Russ]

To make the combo only show unique subjects, drop
tblTrainingSession.*, out of the query text. You can
click the ... builder button next to the combo box row
source and view the query - and run it - to make sure
it's right.

To use it as a filter, make the AfterUpdate event look
like this:
Me.Filter = "[Subject] = " & [cboLookupSubject]
Me.FilterOn = True

which will turn the form's filter on. You may choose not
to use the "Me" there, but rather designate the form by
name. You should probably add a button to "show all"
(turn the filter off) which would have an OnClick event
that looks like this:
Me.Filter = ""
Me.FilterOn = False

Finally, you probably should add a "Add New Record"
button which would turn the filter off and go to a new
record.

 

[Bruce]

Thanks. That did the trick with filtering, and is very
simple once I see how it is done. I had gotten stuck on
thinking the combo box needed a PK field. I ended up
using DoCmd.ApplyFilter, since I kept getting an error
message about how it couldn't find a field named Filter or
something like that. I appreciate the reminders on the
buttons I will need to remove the filter, go to a new
record, and that sort of thing.
My form's On Open event has the following:
cmdPrevious.Enabled = Not Me.CurrentRecord = 1
cmdNext.Enabled = (Me.CurrentRecord = 1 And_
Me.Recordset.RecordCount > 1) Or Me.CurrentRecord_
< Me.Recordset.RecordCount

cmdPrevious and cmdNext are navigation buttons on the
form, and the code (from a newsgroup posting) disables
buttons when there are no more records in that direction.
I like this better than error messages. However, they are
both disabled when I apply the filter until I use the
built-in ones at the bottom of the page, then the ones I
added work as expected. I expect I need the above code in
some other event, perhaps the combo box's After Update
event?
Finally, I would like a record count to appear on the form
in the X of Y format. I can get the total number of
records with Me.txtRecordCount = Me.Recordset.RecordCount
in the On Current event, where txtRecordCount is an
unbound control box. However, I don't know how to get the
current record (e.g. 1 of 12, 2 of 12). I can get the
twelve, but not the 1, 2 etc.
Sometimes I guess about which event to use for code. I'm
learning, but I still guess quite a bit.
Thanks again for taking the time to help.