Can I use a field in a form as a filter?

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

Guest

I have created a database for a user that tracks training
meetings. The database contains the following tables

EmployeeTable SectionLookupTable
EmployeeID Section
FirstName
LastName
Section/Department
OfficePhone

MeetingsTable
MeetingID
MeetingTopic
MeetingLocation
FacilitatorID

FacilitatorTable
FacilitatorID
FacFirstName
FacLastName

MeetingDetailTable
MeetingID
AttendanceID
EmployeeID
InAttendance (Yes/No)

I have a form/subform to do data entry for a meeting the
form includes;

MeetingID MeetingDate MeetingLocation
FacID (drop down list)

Subform includes

AttendanceID
EmployeeID(drop down choose by name)
InAttendance

The user has told me that she would like to be able to
choose a section/department from a drop down list which
would limit the number of names on the employeeID lookup.
How do I do this?
 
----- Glenda Garcia wrote: -----

I have created a database for a user that tracks training
meetings. The database contains the following tables

EmployeeTable SectionLookupTable
EmployeeID Section
FirstName
LastName
Section/Department
OfficePhone

MeetingsTable
MeetingID
MeetingTopic
MeetingLocation
FacilitatorID

FacilitatorTable
FacilitatorID
FacFirstName
FacLastName

MeetingDetailTable
MeetingID
AttendanceID
EmployeeID
InAttendance (Yes/No)

I have a form/subform to do data entry for a meeting the
form includes;

MeetingID MeetingDate MeetingLocation
FacID (drop down list)

Subform includes

AttendanceID
EmployeeID(drop down choose by name)
InAttendance

The user has told me that she would like to be able to
choose a section/department from a drop down list which
would limit the number of names on the employeeID lookup.
How do I do this?


First, the Section/Department field must exist on both the form and subform.
The Section/Department field in the subform can be set to Visible = False in the
properties. Make this field the Master/Child fields. Then in the module add:

Private Sub cboSection_AfterUpdate()
On Error GoTo Err_cboSection_After

Dim strSQL As String

strSQL = "SELECT * FROM qryTargets WHERE Section = '" & Me.cboSection &"'"
Me.sfrmTargets.Form.RecordSource = strSQL

Exit_cboSection_After:
Exit Sub

Err_cboSection_After:
Msgbox Err.Description
Resume Exit_cboSection_After
End Sub

Where qryTargets is the recordsource for your subform, and sfrmTargets is the name
of your subform. One thing to remember, this only works for a subform. You cannot
change the recordsource for a form that has focus.

This may not be the easiest way but I've been using it for a couple of months without
fail.
 
Back
Top