Hi all,
I'm struggling to write some code which uses the result of up 6 checkboxes. Each checkbox stands for a different dataset and the plan is to extract email address based on the datasets selected.
For example at one point in time I may only want address for people in one dataset, but the next time I use it it maybe that I want to select all the people in say 2 or 3 of the datasets.
Once the datasets have been selected, I then need to add another criteria such as whether they are a supplier or a customer from a drop down box. Essentially I'm trying to create a search form to select as many datasets as needed and once done then to select the type.
I'm struggling with the logic behind it. Currently once I select the datasets, it then doesn't apply the additional restriction of the type and brings back all rows based on the dataset selection.
My code is as below, and a screen shot of the skeleton view of the form can be found at: http://imageshack.us/photo/my-images/51/84341811.png/
I'm not an expert at VB and very much cobbling some of this together from googled results. I very much appreciate any help that any one is able to provide as I'm at a loss of what to do to solve this.
Many thanks
Code:
Private Function BuildFilter() As Variant
Dim varWhere As Variant
Dim varItem As Variant
Dim intIndex As Integer
varWhere = Null ' Main filter
' Check for set1
If Me.cmbset1 = -1 Then
varWhere = varWhere & "([DS_set1] = True) OR "
ElseIf Me.cmbset2 = -1 Then
varWhere = varWhere & "([DS_set2] = True) OR "
ElseIf Me.cmbset3 = -1 Then
varWhere = varWhere & "([DS_set3] = True) OR "
ElseIf Me.cmbset4 = -1 Then
varWhere = varWhere & "([DS_set4] = True) OR "
ElseIf Me.cmbset5 = -1 Then
varWhere = varWhere & "([DS_set5] = True) OR "
ElseIf Me.cmbset6 = -1 Then
varWhere = varWhere & "([DS_set6] = True) AND "
End If
' Check for Category
If Not IsNull(Me.cmbCategory) Then
varWhere = varWhere & "([Category] = """ & Me.cmbCategory & """) AND "
End If
' Check if there is a filter to return...
If IsNull(varWhere) Then
varWhere = ""
Else
' strip off last logic operator in the filter
If Right(varWhere, 5) = " AND " Then
varWhere = Left(varWhere, Len(varWhere) - 5)
Else
varWhere = Left(varWhere, Len(varWhere) - 4)
End If
End If
BuildFilter = varWhere
End Function