I have a form (F_ptselect) with multiple subforms (F_s1, F_s2, F_s3). On the main form I have a combo box that allows me to choose an integer identifier (i.e., ID = 1001, id = 1002, id = 1003, id = 1004, etc.). The subforms are also all linked by ID using "Link Master Fields" and "Link Child Fields". I've found and modified vba that allows me to choose an ID (say 1001) from the combo box on F_ptselect, and subsequently pull up all the data for ID = 1001 on F_s1, F_s2, and F_s3.

Here's that vba:

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

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

Now, for each ID there are multiple records (i.e., ID=1001 and day=1, ID=1001 and day=2, ID=1002 and day=1, ID=1002 and day=2 etc.). I'd like to be able to have a combo box or button or something that allows me to synchronize the ability to cycle through these records of a single ID. So if I select ID 1001 from the F_ptselect combo box, I'd like to see F_s1, F_s2, and F_s3 for ID 1001, day 1. Then I'd like to be able to change to day 2 for ID 1001 quickly using a combo box selection, button or something. Currently, I'd have to go to the record arrows at the bottom of each subform to change the record. Each row of data has a primary key (let's call it KEY) as well. So a query row or table row would look like:

CodePudding user response：

Options:

1. RecordsetClone/Bookmark method for each subform
   applied by Gustav sample Access db in https://www.experts-exchange.com/articles/18107/Synchronizing-Multiple-Subforms-in-Access.html?preview=cUa6D5QxDFA=

2. set each subform Filter and FilterOn properties

3. parameterized query as RecordSource for each subform

I always name subform container different from the form it holds, like ctrFS1. For option 2, consider:

Sub cbxDay_AfterUpdate()
Dim strF As String
With Me
strF = "[Day]=" & .cbxDay
.ctrFS1.Form.Filter = strF
.ctrFS1.Form.FilterOn = True
.ctrFS2.Form.Filter = strF
.ctrFS2.Form.FilterOn = True
.ctrFS3.Form.Filter = strF
.ctrFS3.Form.FilterOn = True
End With
End Sub

If you name each subform container like: ctrFS1, ctrFS2, ctrFS3, consider:

Dim x As Integer
With Me
For x = 1 to 3
    .Controls("ctrFS" & x).Form.Filter = "Day=" & .cbxDay
    .Controls("ctrFS" & x).Form.FilterOn = True
Next
End With

CodePudding user response：

Here's the final code (based on the answer from June7) for any interested. The "Day" field is actually a string in my tables so that's why the concatenation syntax is for a string.

Private Sub find_day_AfterUpdate()
Dim strF1 As String
With Me
strF1 = "[Day]='" & .find_day & "'"
.F_s1.Form.Filter = strF1
.F_s1.Form.FilterOn = True
End With

Dim strF2 As String
With Me
strF2 = "[Day]='" & .find_day & "'"
.F_s2.Form.Filter = strF2
.F_s2.Form.FilterOn = True
End With

Dim strF3 As String
With Me
strF3 = "[Day]='" & .find_day & "'"
.F_s3.Form.Filter = strF3
.F_s3.Form.FilterOn = True
End With
End Sub