Search code examples
ms-access

Using two comboboxes to synchronize three subforms - can I use multiple criteria in rs.FindFirst?

So this is a bit of a continuation of this thread: Form with multiple subforms - synchronize record change of subforms

I thought I was able to solve this with the use of subform Filter and FilterOn properties, but this filtering wasn't resulting in the right record selection. It would synchronize the subforms based on both the ID and the Day (i.e., I was able to pull the three different subforms that were all linked to ID = 1001 and Day = 2). However, I noticed the record for the main form was staying the same (ID = 1001 and Day = 1) even though my two combo boxes read ID = 1001 and Day = 2 and the subforms all have ID 1001 and Day 2. Here's what I have in vba currently:

Private Sub find_cupnx_AfterUpdate()
    ' Find the record that matches the control.
    Dim rs As Object

    Set rs = Me.Recordset.Clone
    rs.FindFirst "[cupnx] = " & Me![find_cupnx] & ""
    If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

Private Sub find_visit_AfterUpdate()
    Dim strF As String
    With Me
    strF = "[Visit]='" & .find_visit & "'"
    .F_4100form_s1.Form.Filter = strF
    .F_4100form_s1.Form.FilterOn = True
    .F_4100form_s2_Toxicity.Form.Filter = strF
    .F_4100form_s2_Toxicity.Form.FilterOn = True
    .F_4100form_s3_Infection.Form.Filter = strF
    .F_4100form_s3_Infection.Form.FilterOn = True
    End With
End Sub

Rather than using the subform Filter property. Would it be possible to do another Recordset.Clone and Bookmark that has multiple criteria in it? For instance, something like this (the code isn't right but hopefully the idea gets across):

Private Sub find_visit_AfterUpdate()
    ' Find the record that matches the control.
    Dim rs As Object

    Set rs = Me.Recordset.Clone
    rs.FindFirst "[cupnx] = " & Me![find_cupnx] & "" And "[visit] = '" & Me![find_visit] & "'"
    If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

I've tried to go through Gustav's sample for multiple synchronous subforms (https://www.experts-exchange.com/viewCodeSnippet.jsp?codeSnippetId=30-18107-3) but I just can't make sense of it.

Edit: I was able to combine the use of the subform Filter and FilterOn properties with the Recordset.clone and Bookmark methods under one AfterUpdate() event to get to the right record on the main form as well as on the subforms! Code below:

Option Compare Database
Option Explicit


Private Sub find_cupnx_AfterUpdate()
    ' Find the record that matches the control.
    Dim rs As Object

    Set rs = Me.Recordset.Clone
    rs.FindFirst "[cupnx] = " & Me![find_cupnx] & ""
    If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

Private Sub find_visit_AfterUpdate()
    ' Find the record that matches the control.
    Dim rs As Object

    Set rs = Me.Recordset.Clone
    rs.FindFirst "[cupnx] = " & Me![find_cupnx] & " And [visit] = '" & Me![find_visit] & "'"
    If Not rs.EOF Then Me.Bookmark = rs.Bookmark
    
    Dim strF As String
    With Me
    strF = "[Visit]='" & .find_visit & "'"
    .F_4100form_s1.Form.Filter = strF
    .F_4100form_s1.Form.FilterOn = True
    .F_4100form_s2_Toxicity.Form.Filter = strF
    .F_4100form_s2_Toxicity.Form.FilterOn = True
    .F_4100form_s3_Infection.Form.Filter = strF
    .F_4100form_s3_Infection.Form.FilterOn = True
    End With
End Sub
Solution

  • Your concatenation is not correct - too many quote marks:

    rs.FindFirst "[cupnx] = " & Me![find_cupnx] & " And [visit] = '" & Me![find_visit] & "'"

    Place literal text within quote marks and concatenate variables to construct criteria string. The AND operator is literal text.