locked
How do i filter a subform based on a string? RRS feed

  • Question

  • Hello, i wonder how i filter a subform so it only shows related records to the open main record?

    I have a form which displays detailed information about a client. The user are able to see all the contact information and events related to the clients.

    When form B is open the subform displays all the events currently, i'm trying to filter the subform so the form to only displays the related records. If there is no related records then leave the subform empty. I have built a query which does the filter i want, however i do not know how i make the query run run since i haven't manage to change the subforms recordsource so the query could filter in the "Security Number". 

    Below have i shared the forms code with images and a version of the db am building.

    Form A, Lists all clients.

    Option Compare Database
    Option Explicit
    
    Dim SrchVal As String
    Dim SrchCrit As String
    Dim LastFld As String
    Dim fldName As String
    Dim FlitStr As String
    
    Private Function addCrLf(addValue As Integer) As String
        'Convenience routine to build a concatenated string of carriage returns / line feeds
        'given a specified count
        
        Dim intCount As Integer
        Dim strReturn As String
        
        strReturn = ""
            For intCount = 1 To addValue
                strReturn = strReturn & vbCrLf
            Next intCount
            
            addCrLf = strReturn
    End Function
    
    Private Sub FiltCITY_GotFocus()
        Me.AllowEdits = True
        If Nz(FlitStr, "") = "" Then
            Me.FiltCITY.RowSource = "SELECT DISTINCT tblClients.City FROM tblClients;"
        Else
            Me.FiltCITY.RowSource = "SELECT DISTINCT tblClients.City FROM tblClients WHERE " & FlitStr & ";"
        End If
            Me.FiltCITY.Dropdown
    End Sub
    
    Private Sub FiltCITY_LostFocus()
        Me.AllowEdits = False
    End Sub
    
    Private Sub filtORO_GotFocus()
        Me.AllowEdits = True
        If Nz(FlitStr, "") = "" Then
            Me.filtORO.RowSource = "SELECT tbl_Oro.ClassType_Oro FROM tbl_Oro;"
        Else
            Me.filtORO.RowSource = "SELECT tbl_Oro.ClassType_Oro FROM tbl_Oro WHERE " & FlitStr & ";"
        End If
            Me.filtORO.Dropdown
    End Sub
    
    Private Sub filtORO_LostFocus()
        Me.AllowEdits = False
    End Sub
    
    Private Sub filtNAME_GotFocus()
        Me.AllowEdits = True
        If Nz(FlitStr, "") = "" Then
            Me.filtNAME.RowSource = "SELECT DISTINCT tblClients.LastName FROM tblClients;"
        Else
            Me.filtNAME.RowSource = "SELECT DISTINCT tblClients.LastName FROM tblClients WHERE " & FlitStr & ";"
        End If
            Me.filtNAME.Dropdown
    End Sub
    
    Private Sub filtNAME_LostFocus()
        Me.AllowEdits = False
        Me.Refresh
    End Sub
    Private Sub txtClientID_DblClick(Cancel As Integer)
        Dim intID As String
        intID = Me!intID
    
          'DoCmd.Close acForm, "frmClientsList"
          DoCmd.Close acForm, "frmClientsMenu"
          DoCmd.OpenForm "frmClientDetails", acNormal, "", "", acReadOnly, acDialog, OpenArgs:=intID
    End Sub
    
    Private Sub btnShowClient_Click()
        Dim intID As String
        intID = Me!intID
    
          'DoCmd.Close acForm, "frmClientsList"
          DoCmd.Close acForm, "frmClientsMenu"
          DoCmd.OpenForm "frmClientDetails", acNormal, "", "", acReadOnly, acDialog, OpenArgs:=intID
    End Sub
    
    Private Sub lblAnmäldDatum_Click()
        If Me.OrderBy = "AnmäldDatum" Then
            Me.OrderBy = "AnmäldDatum DESC"
        Else
            Me.OrderBy = "AnmäldDatum"
        End If
    End Sub
    
    Private Sub Form_KeyDown(KeyCode As Integer, Shift As Integer)
    
        On Error GoTo Err_Process
          Dim ctl As control
          Dim rs As Recordset
          
          Select Case KeyCode
                Case vbKeyEnd
                      KeyCode = 0
                      DoCmd.RunCommand acCmdRecordsGoToLast
                Case vbKeyHome
                    KeyCode = 0
                    DoCmd.RunCommand acCmdRecordsGoToFirst
                Case vbKeyUp
                    KeyCode = 0
                    DoCmd.RunCommand acCmdRecordsGoToPrevious
                Case vbKeyDown
                    KeyCode = 0
                    DoCmd.RunCommand acCmdRecordsGoToNext
                Case vbKeyRight, vbKeyLeft
                Case vbKeyPageUp, vbKeyPageDown
                    KeyCode = 0
                Case 9, 13    'tab or enter keys
                Case 8          'backspace key
                Case 48 To 57, 65 To 90
                   Set ctl = Screen.ActiveControl
                     fldName = ctl.Name
                     Select Case UCase(fldName)
                            Case "filtNAME", "FiltCITY", "filtORO"
                                Exit Sub
                        End Select
                         If fldName <> LastFld Then
                            SrchVal = ""
                         End If
                   LastFld = fldName
                   SrchVal = SrchVal & Chr(KeyCode)
                   KeyCode = 0
                      If fldName = "Address" Then
                         SrchCrit = "[" & fldName & "] Like '*" & SrchVal & "*'"
                      Else
                         SrchCrit = "[" & fldName & "] Like '" & SrchVal & "*'"
                      End If
                   Set rs = Me.RecordsetClone
                      rs.FindFirst SrchCrit
                            If rs.NoMatch Then
                               MsgBox (" Record not found! ")
                            Else
                               Me.Bookmark = rs.Bookmark
                            End If
                   rs.Close
                   
                Case 107, 187
                   If SrchVal = "" Then
                      KeyCode = 0
                      Exit Sub
                   End If
                   Set ctl = Screen.ActiveControl
                         fldName = ctl.Name
                         KeyCode = 0
                   Set rs = Me.RecordsetClone
                         rs.Bookmark = Me.Bookmark
                         rs.FindNext SrchCrit
                               If rs.NoMatch Then
                                  MsgBox (" Record not found! ")
                               Else
                                  Me.Bookmark = rs.Bookmark
                               End If
                         rs.Close
                         
                Case 109, 189
                     If SrchVal = "" Then
                        KeyCode = 0
                        Exit Sub
                     End If
                     Set ctl = Screen.ActiveControl
                         fldName = ctl.Name
                         KeyCode = 0
                     Set rs = Me.RecordsetClone
                         rs.Bookmark = Me.Bookmark
                         rs.FindPrevious SrchCrit
                               If rs.NoMatch Then
                                  MsgBox (" Record not found! ")
                               Else
                                  Me.Bookmark = rs.Bookmark
                               End If
                     rs.Close
                Case 27
                   KeyCode = 0
                   SrchVal = ""
                Case Else
                   KeyCode = 0
    End Select
            
    Exit_Process:
        Exit Sub
    Err_Process:
            Select Case Err.Number
            Case 2046
            Case 2474
            Case Else
                MsgBox Err.Number & " " & Err.Description
            End Select
            Resume Exit_Process
    End Sub
    
    Private Sub Form_Open(Cancel As Integer)
        'Me.OrderBy = "ID"
        Me.OrderByOn = True
          Me.AllowAdditions = False
          Me.AllowEdits = False
          Me.AllowDeletions = False
        
        
    End Sub
    
    Private Sub cmdDeleteClient_Click()
        Dim strClient As String
        If MsgBox(" Du är på väg att RADERA en KLIENT. ALLA relaterade objekt kommer att försvinna. Är du SÄKER på att du vill radera denna klienten? OBS! detta går inte att ångra! ", vbYesNo) = vbYes Then
            strClient = "Klient: " & Me!intID & " # " & Me!txtClientID & addCrLf(2) & "Namn: " & addCrLf(1) & Me!FirstName & " - " & Me!LastName & addCrLf(2)
            Me.AllowDeletions = True
            DoCmd.RunCommand acCmdDeleteRecord
            Me.AllowDeletions = False
            MsgBox (" " & strClient & " Har raderats! ")
        End If
    End Sub
    
    Private Sub lblClientID_Click()
        If Me.OrderBy = "ClientID" Then
            Me.OrderBy = "ClientID DESC"
        Else
            Me.OrderBy = "ClientID"
        End If
        Me.AllowAdditions = False
        Me.AllowEdits = False
        Me.AllowDeletions = False
    End Sub
    
    Private Sub lblePost_Click()
        If Me.OrderBy = "ePost" Then
            Me.OrderBy = "ePost DESC"
        Else
            Me.OrderBy = "ePost"
        End If
    End Sub
    
    Private Sub lblFirstName_Click()
              If Me.OrderBy = "FirstName" Then
            Me.OrderBy = "FirstName DESC"
        Else
            Me.OrderBy = "FirstName"
        End If
    End Sub
    
    Private Sub lblHomeNumnber_Click()
        If Me.OrderBy = "HomeNumnber" Then
            Me.OrderBy = "HomeNumnber DESC"
        Else
            Me.OrderBy = "LastName"
        End If
    End Sub
    
    Private Sub lblID_Click()
        If Me.OrderBy = "intID" Then
            Me.OrderBy = "intID DESC"
        Else
            Me.OrderBy = "intID"
        End If
    End Sub
    
    Private Sub lblLastName_Click()
        If Me.OrderBy = "LastName" Then
            Me.OrderBy = "LastName DESC"
        Else
            Me.OrderBy = "LastName"
        End If
    End Sub
    
    Private Sub lblPhoneNumber_Click()
        If Me.OrderBy = "PhoneNumber" Then
            Me.OrderBy = "PhoneNumber DESC"
        Else
            Me.OrderBy = "PhoneNumber"
        End If
    End Sub
    
    Private Sub lblPoastAdress_Click()
        If Me.OrderBy = "PoastAdress" Then
            Me.OrderBy = "PoastAdress DESC"
        Else
            Me.OrderBy = "LastPoastAdressName"
        End If
    End Sub
    
    Private Sub lblPostOrt_Click()
        If Me.OrderBy = "PostOrt" Then
            Me.OrderBy = "PostOrt DESC"
        Else
            Me.OrderBy = "PostOrt"
        End If
    End Sub
    
    Private Sub lblRegistreradDatum_Click()
        If Me.OrderBy = "RegistreradDatum" Then
            Me.OrderBy = "RegistreradDatum DESC"
        Else
            Me.OrderBy = "RegistreradDatum"
        End If
    End Sub
    
    Private Sub lblRegistreradesAv_Click()
        If Me.OrderBy = "RegistreradesAv" Then
            Me.OrderBy = "RegistreradesAv DESC"
        Else
            Me.OrderBy = "RegistreradesAv"
        End If
    End Sub
    
    Private Sub lblAvRegistreradDatum_Click()
        If Me.OrderBy = "AvRegistreradDatum" Then
            Me.OrderBy = "AvRegistreradDatum DESC"
        Else
            Me.OrderBy = "AvRegistreradDatum"
        End If
    End Sub
    
    Sub BuildFlitStr()
        FiltStr = ""
            If Me!NameFlit <> "" Then
                FlitStr = "[LastName] = '" & Me!filtNAME & "'"
            End If
            If Me!NameFlit <> "" Then
                If FiltStr = "" Then
                    FlitStr = "[CITY] = '" & Me!FiltCITY & "'"
                Else
                    FlitStr = FiltStr & " [CITY] = '" & Me!cboFliterCity & "'"
            End If
            If Me!NameFilt <> "" Then
                FlitStr = "[ORO] = '" & Me!filtORO & "'"
            End If
    End Sub

    Form B, Displays detailed information about a client.

    Option Compare Database
    Option Explicit
    
    Dim strClientID As String
    
    Private Sub cmdNewCase_Click()
        DoCmd.Close acForm, "frmClientDetails"
        DoCmd.OpenForm "frmErrand", acNormal, "", "", acFormAdd, acDialog
    End Sub
    
    Private Sub btnAddErrand_Click()
            Dim intID As String
            DoCmd.Close acForm, "frmMenu"
            DoCmd.Close acForm, "frmClientDetails"
            
            
            intID = Me!txtID
            DoCmd.OpenForm "frmErrandV2", acNormal, "", acFormAdd, OpenArgs:=intID
    End Sub
    
    Private Sub btnClose_Click()
        DoCmd.Close acForm, "frmClientDetails", acSaveNo
    End Sub
    
    Private Sub btnEN_Click()
                Me.lblH1.Caption = "Personal Information:"
                    Me.lblClient_ID.Caption = "Security Number:"
                    Me.lblFirstName.Caption = "First name"
                    Me.lblLastName.Caption = "Last name"
                Me.lblH2.Caption = "Contact information"
                    Me.lblAdress.Caption = "Adress"
                    Me.lblZIP.Caption = "ZIP"
                    Me.lblCity.Caption = "City"
                    Me.lblEmail.Caption = "e-mail"
                    Me.lblHomeNumber.Caption = "Home Number"
                    Me.lblPhoneNumber.Caption = "Mobile Number"
                Me.lblH3.Caption = "Signature"
                    Me.lblRegisteredOf.Caption = "Registered by"
                    Me.lblRegisteredDate.Caption = "Registerd on"
            Me.btnAddErrand.Caption = "Add Case"
            Me.btnSave.Caption = "Save"
            Me.btnEdit.Caption = "Edit"
            Me.btnDelete.Caption = "Delete"
            Me.btnClose.Caption = "Close"
    End Sub
    
    Private Sub btnSE_Click()
                Me.lblH1.Caption = "Personuppgifter"
                    Me.lblClient_ID.Caption = "Personnummer"
                    Me.lblFirstName.Caption = "För Namn"
                    Me.lblLastName.Caption = "Efter Namn"
                Me.lblH2.Caption = "Kontaktuppgifter"
                    Me.lblCity.Caption = "Post ort"
                    Me.lblAdress.Caption = "Post Adress"
                    Me.lblEmail.Caption = "e-mail"
                    Me.lblHomeNumber.Caption = "Hem Nummer"
                    Me.lblPhoneNumber.Caption = "Mobil Nummer"
                Me.lblH3.Caption = "Signatur"
                    Me.lblRegisteredOf.Caption = "Registrerades den"
                    Me.lblRegisteredDate.Caption = "Registrerades av"
            Me.btnAddErrand.Caption = "Lägg till ärende"
            Me.btnSave.Caption = "Spara"
            Me.btnEdit.Caption = "Redigera"
            Me.btnDelete.Caption = "Radera"
            Me.btnClose.Caption = "Stäng"
    End Sub
    
    Private Sub Form_AfterUpdate()
        If Not IsNull(Me.OpenArgs) = 0 Then
            Dim SQL As String
                    SQL = "SELECT tblErrand.ErrandID, tblErrand.ClientID, tblErrand.DatumAtgStart, tblErrand.DatumAtgSlut, tblErrand.Oro, tblErrand.Ansvarig " _
                    & "FROM tblErrand WHERE (((tblErrand.ClientID)=" & Me.OpenArgs & "));"
                Me.subFrmClientEventSummery.Form.RecordSource = SQL
                Me.subFrmClientEventSummery.Form.Requery
        End If
    End Sub
    
    Private Sub Form_Open(Cancel As Integer)
    '    MsgBox (Me.OpenArgs)
          If Nz(Me.OpenArgs) = 0 Then
                'Me.RecordSource = "tblClients"
          Else
                Me.RecordSource = "SELECT tblClients.* FROM tblClients WHERE (((tblClients.ID)=" & Me.OpenArgs & "));"
          End If
        
        Dim strClientID As String
                strClientID = Me.txtClientID
    
          'strClientID = Me.txtClientID
          
          'Debug.Print txtClientID
          Debug.Print strClientID
          Debug.Print Me.OpenArgs
          
          Me.txtID.Visible = False
          Me.btnEdit.Visible = True
          Me.btnSave.Visible = False
          Me.AllowEdits = False
          Me.AllowAdditions = False
          Me.AllowDeletions = False
    End Sub
    
    Private Sub btnSave_Click()
        If Me.btnSave.Visible = True Then
            Me.btnClose.SetFocus
            Me.AllowEdits = False
            Me.Refresh
            Me.btnSave.Visible = False
            Me.btnEdit.Visible = True
        End If
    End Sub
    
    Private Sub btnEdit_Click()
        Me.AllowEdits = True
        If Me.btnEdit.Visible = True Then
            Me.txtClientID.SetFocus
            Me.btnEdit.Visible = False
            Me.btnSave.Visible = True
        End If
    End Sub

    Form C, list all events registered to all clients.

    Form C is a  

    Sample db


    What we do not know. Can we learn.

    Tuesday, November 3, 2015 9:14 AM

Answers

  • Hi,

    >>When form B is open the subform displays all the events currently, i'm trying to filter the subform so the form to only displays the related records. If there is no related records then leave the subform empty. I have built a query which does the filter i want, however i do not know how i make the query run run since i haven't manage to change the subforms recordsource so the query could filter in the "Security Number". <<

    A filter that restricts or sorts the form's records. You can enter the name of either an existing query or a filter that was saved as a query. However, the query must include all the fields in the form you are opening or have its OutputAllFields property set to Yes.

    For example, there is a form named order detail, we wanted to filter this form via the OrderId filed. Then we can using code below to open the form:

    DoCmd.OpenForm "OrderDetail", acFormDS, "select * from orderdetail where orderid=2"

    Or another easy way to achieve the goal is using WhereCondition parameter. You can refer to code below:

    DoCmd.OpenForm "OrderDetail", acFormDS, , "orderid=1"

    More about this method, please refer to link below:
    DoCmd.OpenForm Method

    Regards & Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Wednesday, November 4, 2015 5:43 AM
  • > "When form B is open the subform displays all the events currently, i'm trying to filter the subform so the form to only displays the related records"

    The best and easiest way to synchronize a subform to a mainform is by specifying LinkMasterFields and LinkChildFields for the subform control.

    The subform control is the container for the subform and specifies the formname on the Data tab of the Property Sheet in the SourceObject property.  The Data tab is also where you will find LinkMasterFields and LinkChildFields.  If the Property Sheet is not showing, press ALT-ENTER.  It will show you more details about whatever is selected.

    While the subform often hides the corresponding link field(s) by setting Visible to No, they must be there for it to function properly.

    If Relationships are defined when the subform is put onto the mainform, Access will fill these properties for you.

    So no need to put criteria into the query you are using for the RecordSource when specifying link fields ~


    Warm Regards, Crystal http://www.AccessMVP.com/strive4peace * (: have an awesome day :)


    Friday, November 13, 2015 7:35 AM

All replies

  • Hi,

    >>When form B is open the subform displays all the events currently, i'm trying to filter the subform so the form to only displays the related records. If there is no related records then leave the subform empty. I have built a query which does the filter i want, however i do not know how i make the query run run since i haven't manage to change the subforms recordsource so the query could filter in the "Security Number". <<

    A filter that restricts or sorts the form's records. You can enter the name of either an existing query or a filter that was saved as a query. However, the query must include all the fields in the form you are opening or have its OutputAllFields property set to Yes.

    For example, there is a form named order detail, we wanted to filter this form via the OrderId filed. Then we can using code below to open the form:

    DoCmd.OpenForm "OrderDetail", acFormDS, "select * from orderdetail where orderid=2"

    Or another easy way to achieve the goal is using WhereCondition parameter. You can refer to code below:

    DoCmd.OpenForm "OrderDetail", acFormDS, , "orderid=1"

    More about this method, please refer to link below:
    DoCmd.OpenForm Method

    Regards & Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Wednesday, November 4, 2015 5:43 AM
  • But won't that command only affect a from when a new instant or cloon is opened of the form?

    I have tried the method you suggested Fei, however i haven't manage to make it work with a subform.

    This is the code u use to pass the client to the next from with the subform.

    Private Sub btnShowClient_Click()
        Dim intID As String
        intID = Me!intID
    
          'DoCmd.Close acForm, "frmClientsList"
          DoCmd.Close acForm, "frmClientsMenu"
          DoCmd.OpenForm "frmClientDetails", acNormal, "", "", acReadOnly, acDialog, OpenArgs:=intID
    End Sub

    And this is the procedure i have thelen for the form when it opens and receives the values.

    Private Sub Form_Open(Cancel As Integer) ' MsgBox (Me.OpenArgs) If Nz(Me.OpenArgs) = 0 Then 'Me.RecordSource = "tblClients" Else Me.RecordSource = "SELECT tblClients.* FROM tblClients WHERE (((tblClients.ID)=" & Me.OpenArgs & "));" End If
    End Sub

    This works just fine, however i would like to pass this value to the sub-form as well.
    Click the sample db link in my previous post to download my sample database.


    What we do not know. Can we learn.

    Friday, November 13, 2015 7:28 AM
  • > "When form B is open the subform displays all the events currently, i'm trying to filter the subform so the form to only displays the related records"

    The best and easiest way to synchronize a subform to a mainform is by specifying LinkMasterFields and LinkChildFields for the subform control.

    The subform control is the container for the subform and specifies the formname on the Data tab of the Property Sheet in the SourceObject property.  The Data tab is also where you will find LinkMasterFields and LinkChildFields.  If the Property Sheet is not showing, press ALT-ENTER.  It will show you more details about whatever is selected.

    While the subform often hides the corresponding link field(s) by setting Visible to No, they must be there for it to function properly.

    If Relationships are defined when the subform is put onto the mainform, Access will fill these properties for you.

    So no need to put criteria into the query you are using for the RecordSource when specifying link fields ~


    Warm Regards, Crystal http://www.AccessMVP.com/strive4peace * (: have an awesome day :)


    Friday, November 13, 2015 7:35 AM
  • I got it working now thanks for the response and help.

    I found that i had a relationship between the the subform and mainform which linked wrong filds, so i all i had to do was correct that relationship and follow your advise. :)


    What we do not know. Can we learn.

    Tuesday, November 24, 2015 7:22 AM
  • you're welcome ;) ~ happy to help ... and happy you got it!

    Warm Regards, Crystal http://www.AccessMVP.com/strive4peace * (: have an awesome day :)

    Tuesday, November 24, 2015 7:26 AM