Listboxes, subforms and removing filters

A

Anne

I have two forms, each with a listbox (studies or
employees) and a subform displaying info about that study
or employee. Double-clicking on a control on the subform
opens the other form displaying that specific study or
employee. This works fine when jumping from form to form,
but I have to remove the filter in order to be able to
change which study or employee is shown in the subform.
(In other words, I can double-click on the name of the
employee from the study form, open the employee form to
the right employee, but I can't pick another employee
[from the listbox to display in the subform] without
removing the filter/sort.) This is what I'm struggling
with. When and how do I remove the filter? Also, how do
I get the listbox to highlight the right study or employee
when I'm jumping from form to form?

I'm using the following to find the study in the subform
that matches the selected one from listbox (and similar
one for employees):

Private Sub listbox_PickStudy_AfterUpdate()
Dim RSC_List As Recordset, BkMkStr As String
Set RSC_List = Me.RecordsetClone
With RSC_List
.FindFirst "[StudyID] = " & Me![listbox_PickStudy]
BkMkStr = .Bookmark
End With
Me.Bookmark = BkMkStr
End Sub

I'm using the following to open the study form when you
double-click the study (again, similar code to go from
study to employee):

Private Sub Study_DblClick(Cancel As Integer)
If IsNull(Me![tbl_EmployeeStudies.StudyID]) Then
MsgBox "You must select a study to open."
Response = acDataErrContinue
Exit Sub
End If
DoCmd.OpenForm "frm_MainStudy", , , "[StudyID] = " &
[tbl_EmployeeStudies.StudyID]
End Sub

Any help is appreciated!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top