Hey guys,
Although I am familiar with almost all of MS Office, Access is one that I have not even touched in 4 yrs of college. So obviously my new job decides to see how fast I can learn..lol. I do not know anything about Visual Basic. Im a C++ and Matlab huy.
To keep things simple/ legal, I will describe my project in the following way...
I have a table, CARS, and in this table I have the fields CarBrand, Cylinders, Color, and Gas Type.
I want to be able to search for CarBrands based on what the user selects, or does not select from the the fields and return the results to CarDatabaseSrch_sub. Also the user can select more than one item in the listbox.
What I have so far is able to search based on multiple selections within one listbox, but now I am stuck on making this further search with multiple listboxes.
This is the code for my Filter button,
Private Sub Filter_Click()
' Update the record source
Form_CarDatabaseSrch_sub.RecordSource = "SELECT * FROM CARS " & BuildFilter
End Sub
Private Function BuildFilter() As Variant
Dim varWhere As Variant
Dim varColor As Variant
Dim varItem As Variant
varWhere = Null ' Main filter
varColor = Null ' Subfilter used for colors
' Check for items selected in multiselect list
For Each varItem In Me.Cylinders.ItemsSelected
varColor = varColor & "[Cylinders] = """ & _
Me.Cylinders.ItemData(varItem) & """ OR "
Next
' Test to see if we have subfilter for colors...
If IsNull(varColor) Then
' do nothing
Else
' strip off last "OR" in the filter
If Right(varColor, 4) = " OR " Then
varColor = Left(varColor, Len(varColor) - 4)
End If
' Add some parentheses around the subfilter
varWhere = varWhere & "( " & varColor & " )"
End If
' Check if there is a filter to return...
If IsNull(varWhere) Then
varWhere = ""
Else
varWhere = "WHERE " & varWhere
' strip off last "AND" in the filter
If Right(varWhere, 5) = " AND " Then
varWhere = Left(varWhere, Len(varWhere) - 5)
End If
End If
BuildFilter = varWhere
End Function
Although I am familiar with almost all of MS Office, Access is one that I have not even touched in 4 yrs of college. So obviously my new job decides to see how fast I can learn..lol. I do not know anything about Visual Basic. Im a C++ and Matlab huy.
To keep things simple/ legal, I will describe my project in the following way...
I have a table, CARS, and in this table I have the fields CarBrand, Cylinders, Color, and Gas Type.
I want to be able to search for CarBrands based on what the user selects, or does not select from the the fields and return the results to CarDatabaseSrch_sub. Also the user can select more than one item in the listbox.
What I have so far is able to search based on multiple selections within one listbox, but now I am stuck on making this further search with multiple listboxes.
This is the code for my Filter button,
Private Sub Filter_Click()
' Update the record source
Form_CarDatabaseSrch_sub.RecordSource = "SELECT * FROM CARS " & BuildFilter
End Sub
Private Function BuildFilter() As Variant
Dim varWhere As Variant
Dim varColor As Variant
Dim varItem As Variant
varWhere = Null ' Main filter
varColor = Null ' Subfilter used for colors
' Check for items selected in multiselect list
For Each varItem In Me.Cylinders.ItemsSelected
varColor = varColor & "[Cylinders] = """ & _
Me.Cylinders.ItemData(varItem) & """ OR "
Next
' Test to see if we have subfilter for colors...
If IsNull(varColor) Then
' do nothing
Else
' strip off last "OR" in the filter
If Right(varColor, 4) = " OR " Then
varColor = Left(varColor, Len(varColor) - 4)
End If
' Add some parentheses around the subfilter
varWhere = varWhere & "( " & varColor & " )"
End If
' Check if there is a filter to return...
If IsNull(varWhere) Then
varWhere = ""
Else
varWhere = "WHERE " & varWhere
' strip off last "AND" in the filter
If Right(varWhere, 5) = " AND " Then
varWhere = Left(varWhere, Len(varWhere) - 5)
End If
End If
BuildFilter = varWhere
End Function
