Hi
I'm working on a search form which is then used for reports and I'm nearly there. It consists of a couple of combo boxes and one multiple select list box field. I can get it to work of sorts.
The database is based on a menu. The user has to choose the course i.e. starter (TypeofCourseID), then type of starter(MenuCategoryID) i.e. mushroom starter, and then they can choose a number of mushroom starters (CoursecodeID) i.e. oven baked open mushroom and grilled flat field mushroom starter for this example.
They then state which restaurant is required to analyse the data for the above (BrandCodeID).
My problem is that the restaurant filter gets ignored so that all restaurants appear for both starters.
When I had a play around with a query and the field CoursecodeID, I found this result happens when you put the oven baked open mushroom and grilled flat field mushroom starter criteria on separate lines in the query i.e one on the "criteria" line and one on the "or" line. However if I keep the starters on the same line (criteria line) i.e. "oven baked open mushroom" or "grilled flat field mushroom" it works how I want.
Is there anyway I can make this occur in my VBA code??
THis is what I have
Private Sub Preview73_Click()
Dim SWhereCondition As String
Dim sJoin As String
Dim varItem As Variant
Dim stCriteria As String
sJoin = " AND "
If (Me.MenuCategoryID) Then
SWhereCondition = SWhereCondition & "[MenuCategoryID]= " & Chr$(39) & Me.MenuCategoryID & Chr$(39) & sJoin
End If
If (Me.TypeofCourseID) Then
SWhereCondition = SWhereCondition & "[TypeofCourseID]= " & Chr$(39) & Me.TypeofCourseID & Chr$(39) & sJoin
End If
For Each varItem In Me.CourseCodeID.ItemsSelected
SWhereCondition = SWhereCondition & "[CourseCodeID] = " & Chr$(39) & Me.CourseCodeID.Column(0, varItem) & Chr$(39) & " Or "
Next varItem
SWhereCondition = Left(SWhereCondition, Len(SWhereCondition) - 4) & sJoin
If (Me.BrandCodeID) Then
SWhereCondition = SWhereCondition & "[BrandCodeID]= " & Chr$(39) & Me.BrandCodeID & Chr$(39) & sJoin
End If
If (Me.HouseCodeID) Then
SWhereCondition = SWhereCondition & "[HouseCodeID]= " & Chr$(39) & Me.HouseCodeID & Chr$(39) & sJoin
End If
SWhereCondition = Left$(SWhereCondition, Len(SWhereCondition) - Len(sJoin))
DoCmd.OpenReport "Query1", acPreview, , SWhereCondition
End Sub
I hope I make sense!
Any help will be much appreciated!
Carrie
I'm working on a search form which is then used for reports and I'm nearly there. It consists of a couple of combo boxes and one multiple select list box field. I can get it to work of sorts.
The database is based on a menu. The user has to choose the course i.e. starter (TypeofCourseID), then type of starter(MenuCategoryID) i.e. mushroom starter, and then they can choose a number of mushroom starters (CoursecodeID) i.e. oven baked open mushroom and grilled flat field mushroom starter for this example.
They then state which restaurant is required to analyse the data for the above (BrandCodeID).
My problem is that the restaurant filter gets ignored so that all restaurants appear for both starters.
When I had a play around with a query and the field CoursecodeID, I found this result happens when you put the oven baked open mushroom and grilled flat field mushroom starter criteria on separate lines in the query i.e one on the "criteria" line and one on the "or" line. However if I keep the starters on the same line (criteria line) i.e. "oven baked open mushroom" or "grilled flat field mushroom" it works how I want.
Is there anyway I can make this occur in my VBA code??
THis is what I have
Private Sub Preview73_Click()
Dim SWhereCondition As String
Dim sJoin As String
Dim varItem As Variant
Dim stCriteria As String
sJoin = " AND "
If (Me.MenuCategoryID) Then
SWhereCondition = SWhereCondition & "[MenuCategoryID]= " & Chr$(39) & Me.MenuCategoryID & Chr$(39) & sJoin
End If
If (Me.TypeofCourseID) Then
SWhereCondition = SWhereCondition & "[TypeofCourseID]= " & Chr$(39) & Me.TypeofCourseID & Chr$(39) & sJoin
End If
For Each varItem In Me.CourseCodeID.ItemsSelected
SWhereCondition = SWhereCondition & "[CourseCodeID] = " & Chr$(39) & Me.CourseCodeID.Column(0, varItem) & Chr$(39) & " Or "
Next varItem
SWhereCondition = Left(SWhereCondition, Len(SWhereCondition) - 4) & sJoin
If (Me.BrandCodeID) Then
SWhereCondition = SWhereCondition & "[BrandCodeID]= " & Chr$(39) & Me.BrandCodeID & Chr$(39) & sJoin
End If
If (Me.HouseCodeID) Then
SWhereCondition = SWhereCondition & "[HouseCodeID]= " & Chr$(39) & Me.HouseCodeID & Chr$(39) & sJoin
End If
SWhereCondition = Left$(SWhereCondition, Len(SWhereCondition) - Len(sJoin))
DoCmd.OpenReport "Query1", acPreview, , SWhereCondition
End Sub
I hope I make sense!
Any help will be much appreciated!
Carrie
