filtering a form based on the values of a subform


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
 
Ad

Advertisements


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top