filtering a form based on the values of a subform

  • Thread starter Thread starter K
  • Start date Start date
K

K

Hi.

I have a form (frmProjDataEntry) which displays project records. The form Record Source is tblProjectData. There is a subform on form which list locations (suburbs). There may be zero or many locations per project.

I have another form (frmWelcome) which filters the records displayed in theprojects form using a keyword search and number of combo boxes to select project by "project status", "project agency", etc. I want to add to this search form the option to select one or more locations from a multilistbox. The user then clicks the search button (cmdFilter) and the project form is opened and the user can browse through the relevant records.

Here's the procedure below to open the projects form with the search form (frmWelcome). If I add a multilistbox to the search form listing the locations, what is the VBA required to modify the open form arguments?

Thanks
K
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Private Sub cmdFilter_Click()

Dim strmainform As String
Dim strmaincriteria As String
Dim strKeyword As String

' Name of the main form
strmainform = "frmProjDataEntry"

' Criteria for main form

If Me.txtWordSearch <> "" Then
strKeyword = [Forms]![frmWelcome]![txtWordSearch]
strmaincriteria = "[ProjectName] Like " & Chr(34) & "*" & strKeyword & "*" & Chr(34) & " And "
End If

If Me.cboStatus <> "*" Then
strmaincriteria = strmaincriteria & "[Status] = " & Chr(34) & Me.cboStatus & Chr(34) & " AND "
End If

If Me.cboAgency <> "*" Then
strmaincriteria = strmaincriteria & " [AgencyName] = " & Chr(34) & Me.cboAgency & Chr(34) & " AND "
End If

If Me.cboProgram <> "*" Then
strmaincriteria = strmaincriteria & " [ProgramName] = " & Chr(34) & Me.cboProgram & Chr(34) & " AND "
End If

If Me.cboProjectType <> "*" Then
strmaincriteria = strmaincriteria & " [ProjectType] = " & Chr(34) & Me.cboProjectType & Chr(34) & " AND "
End If

If Me.cboDataSource <> "*" Then
strmaincriteria = strmaincriteria & " [DataSourceAgency] = " & Chr(34) & Me.cboDataSource & Chr(34)
End If

' Remove final "AND" from string
If Right(strmaincriteria, 4) = "AND " Then
strmaincriteria = Left(strmaincriteria, Len(strmaincriteria) - 4)
End If

' Open the main form with WhereCondition
DoCmd.OpenForm strmainform, , , strmaincriteria

DoCmd.Close acForm, "frmWelcome", acSavePrompt


End Sub
 
Back
Top