Full Version: Filtering to record on a subform
UtterAccess Forums > Microsoft® Access > Access Forms
BobbyDigital
I am attempting to filter to a specific record on a subform. I created a search form that is independent of the main form where the subform lives. In my search form a person my be listed multiple times (it's a hospital discharge tracking tool). Right now when I search for a member it takes me to the main form that hosts the subform but it displays all their discharges and I only need the specific record I selected At any rate, here is the code that has been pieced together from multiple sources:

CODE
  
Private Sub lstMemberSearch_DblClick(Cancel As Integer)
On Error GoTo Err_lstMemberSearch_DblClick
    
    Dim stDocName As String
    Dim stLinkCriteria As String
    Dim rst As DAO.Recordset
    
    Set rst = Forms!frmMain!subfrmDischarges.Form.RecordsetClone
    stDocName = "frmMain"
    
    stLinkCriteria = "[MemberID]=" & "" & Me![lstMemberSearch] & ""
    DoCmd.OpenForm stDocName, , , stLinkCriteria
    rst.FindFirst "DischargeID=" & Me.lstMemberSearch
    Forms!frmMain!subfrmDischarges.Form.Bookmark = rst.Bookmark
    DoCmd.Close acForm, "frmSearch", acSaveNo
    DoCmd.GoToControl "CaresteppID"
    
Exit_lstMemberSearch_DblClick:
    Exit Sub
Err_lstMemberSearch_DblClick:
    MsgBox "Member not selected!", vbOKOnly, "Error!"
    Resume Exit_lstMemberSearch_DblClick
    
End Sub


Orealized that FindFirst does not accomplish what I want to do but I wanted to verify that the code works without any errors. I am just stuck now.
Edited by: BobbyDigital on Fri Aug 17 13:07:30 EDT 2007.
jmcwk
Bobby,
Could be wrong on this but it looks like the Search is doing exactly as you describe. It opens to the MemberID as that is the first criteria to the MemberID and I assume that you have the Discharges (Subform) linked against the MemberID so you see ALL Discharges. Have you tried just opening the SubForm against the DischargeID? Or is that what you are after? I also assume that your 1stMember Search reference is the DischargeID Primary Key ???
CODE
stDocName = "frmMain"
    
    stLinkCriteria = "[MemberID]=" & "" & Me![lstMemberSearch] & ""
    DoCmd.OpenForm stDocName, , , stLinkCriteria
BobbyDigital
Thanks for the reply!

lstMemberSearch has both primary keys (MemberID and DischargeID) included in the query that powers the search form. But yes, I wanted to see all discharges. I can get it work if I just open the subform as a stand alone form, it filters it just fine.

EDIT: MemberID is the parent forms primary key, DischargeID is the subform primary key. Parent-child link is MemberID. Sorry, should have made that more clear.


Edited by: BobbyDigital on Fri Aug 17 13:30:04 EDT 2007.
jmcwk
Bobby,
Sorry for the delay, is there any particular reason you have to open The Mainform and not just the Sub ?
BobbyDigital
No, not really. User convenience more than anything. That way everything is all on one form.
jmcwk
hmmmmm.... I am not much help at all for you here am scratching my head to come up with a solution but.... for whatever reason one has not come to mind yet !
BobbyDigital
No worries. I will keep messing around with it and see what I can come up with.
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.