jharding08
Member
- Local time
- Yesterday, 22:55
- Joined
- Feb 16, 2021
- Messages
- 55
I have a form with a recordsource of one table. There are subforms that link to the main form using the Link Master/Child Fieldvand everything works great with adding, etc.
I also have dropdowns to filter the data. I take the value from the filter and create a string to use for the Form.Filter property.
I also now have a dropdown to filter by data/fields in the subform. If the field isnt part of the main form, how do I filter the main form with the value of a subform, that is a foreign key to the main form recordset. Would I have to do a filter in the subform?
Main Form
PK - ID
Subform
PK - ID
FK - MainForm.ID
This is the function called on the dropdown_Afterupdate event (all fields on the main form :
Private Sub FilterForm()
Dim strwhere As String
If Nz(Me.cboCustomer, "") <> "" Then
strwhere = strwhere & "[CustomerID] = " & Me.cboCustomer & " AND "
End If
If Nz(Me.cboStatus, "") <> "" Then
strwhere = strwhere & "[StatusID] = " & Me.cboStatus & " AND "
End If
If Nz(Me.cboSubject, "") <> "" Then
strwhere = strwhere & "[SubjectID] = " & Me.cboSubject & " AND "
End If
If strwhere <> "" Then
strwhere = Left(strwhere, Len(strwhere) - 5)
Me.Filter = strwhere
Me.FilterOn = True
Else
Me.Filter = ""
Me.FilterOn = False
End If
End Sub
The dropdown for the subform is cboProduct, would I create a separate filter for the subform and would that filter the main form based on the subform because of the linked fields?
I also have dropdowns to filter the data. I take the value from the filter and create a string to use for the Form.Filter property.
I also now have a dropdown to filter by data/fields in the subform. If the field isnt part of the main form, how do I filter the main form with the value of a subform, that is a foreign key to the main form recordset. Would I have to do a filter in the subform?
Main Form
PK - ID
Subform
PK - ID
FK - MainForm.ID
This is the function called on the dropdown_Afterupdate event (all fields on the main form :
Private Sub FilterForm()
Dim strwhere As String
If Nz(Me.cboCustomer, "") <> "" Then
strwhere = strwhere & "[CustomerID] = " & Me.cboCustomer & " AND "
End If
If Nz(Me.cboStatus, "") <> "" Then
strwhere = strwhere & "[StatusID] = " & Me.cboStatus & " AND "
End If
If Nz(Me.cboSubject, "") <> "" Then
strwhere = strwhere & "[SubjectID] = " & Me.cboSubject & " AND "
End If
If strwhere <> "" Then
strwhere = Left(strwhere, Len(strwhere) - 5)
Me.Filter = strwhere
Me.FilterOn = True
Else
Me.Filter = ""
Me.FilterOn = False
End If
End Sub
The dropdown for the subform is cboProduct, would I create a separate filter for the subform and would that filter the main form based on the subform because of the linked fields?