Hey guys,
I have two main questions
First off (pictures are kind of blurry)
I have this table
http://i197.photobucket.com/albums/aa109/ebernedo/DiscTable.jpg
And thats my database I use my Form on
I created a search form:
http://i197.photobucket.com/albums/aa109/ebernedo/DiscSearchForm.jpg
Then created a listbox, selected a "field List" and got all of the header values from my table..
so its a box with every header from the table so if you look its like this:
http://i197.photobucket.com/albums/aa109/ebernedo/DiscSearchForm2.jpg
As you see, I turned on the multi-seclect feature to simple so I can select more than one
So from the original discform without the field list
I was able to select one or more sizes, click search then it will update the subform/table and show only those sizes,
so heres my two questions:
for the field list first of all, I dont want the first 2 things which are part #, size, just those pressures, so I don't need thsoe first two columns how do I make that show?
second:
heres my code from the search button,
that works with the size list,
but I need to integrate this Field list into the search button so it will also update, oh yeah and if it says NA under a pressures, insteaf of a number, I dont want that to show as a search result
so heres the code: what do I need to add to make this work?
code:
Option Compare Database
Option Explicit
Private Sub btnClear_Click()
Dim intIndex As Integer
' Clear all search items
' De-select each item in Color List (multiselect list)
For intIndex = 0 To Me.SizeList.ListCount - 1
Me.SizeList.Selected(intIndex) = False
Next
End Sub
Private Sub btnSearch_Click()
' Update the record source
Me.DiscForm.Form.RecordSource = "SELECT * FROM DiscTable " & BuildFilter
' Requery the subform
Me.DiscForm.Requery
End Sub
Private Sub Form_Load()
' Clear the search form
btnClear_Click
End Sub
Private Function BuildFilter() As Variant
Dim varWhere As Variant
Dim varSize As Variant
Dim varItem As Variant
Dim intIndex As Integer
varWhere = Null ' Main filter
varSize = Null ' Subfilter used for colors
' Check for Colors in multiselect list
For Each varItem In Me.SizeList.ItemsSelected
varSize = varSize & "[Size] = """ & _
Me.SizeList.ItemData(varItem) & """ OR "
Next
' Test to see if we have subfilter for colors...
If IsNull(varSize) Then
' do nothing
Else
' strip off last "OR" in the filter
If Right(varSize, 4) = " OR " Then
varSize = Left(varSize, Len(varSize) - 4)
End If
' Add some parentheses around the subfilter
varWhere = varWhere & "( " & varSize & " )"
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
Someone please help me out with this, I would appreaciate it.
Note: I have attached a copy of my database,so if you want to take a look..open up the databse and go to "Disc Search Form"
I have two main questions
First off (pictures are kind of blurry)
I have this table
http://i197.photobucket.com/albums/aa109/ebernedo/DiscTable.jpg
And thats my database I use my Form on
I created a search form:
http://i197.photobucket.com/albums/aa109/ebernedo/DiscSearchForm.jpg
Then created a listbox, selected a "field List" and got all of the header values from my table..
so its a box with every header from the table so if you look its like this:
http://i197.photobucket.com/albums/aa109/ebernedo/DiscSearchForm2.jpg
As you see, I turned on the multi-seclect feature to simple so I can select more than one
So from the original discform without the field list
I was able to select one or more sizes, click search then it will update the subform/table and show only those sizes,
so heres my two questions:
for the field list first of all, I dont want the first 2 things which are part #, size, just those pressures, so I don't need thsoe first two columns how do I make that show?
second:
heres my code from the search button,
that works with the size list,
but I need to integrate this Field list into the search button so it will also update, oh yeah and if it says NA under a pressures, insteaf of a number, I dont want that to show as a search result
so heres the code: what do I need to add to make this work?
code:
Option Compare Database
Option Explicit
Private Sub btnClear_Click()
Dim intIndex As Integer
' Clear all search items
' De-select each item in Color List (multiselect list)
For intIndex = 0 To Me.SizeList.ListCount - 1
Me.SizeList.Selected(intIndex) = False
Next
End Sub
Private Sub btnSearch_Click()
' Update the record source
Me.DiscForm.Form.RecordSource = "SELECT * FROM DiscTable " & BuildFilter
' Requery the subform
Me.DiscForm.Requery
End Sub
Private Sub Form_Load()
' Clear the search form
btnClear_Click
End Sub
Private Function BuildFilter() As Variant
Dim varWhere As Variant
Dim varSize As Variant
Dim varItem As Variant
Dim intIndex As Integer
varWhere = Null ' Main filter
varSize = Null ' Subfilter used for colors
' Check for Colors in multiselect list
For Each varItem In Me.SizeList.ItemsSelected
varSize = varSize & "[Size] = """ & _
Me.SizeList.ItemData(varItem) & """ OR "
Next
' Test to see if we have subfilter for colors...
If IsNull(varSize) Then
' do nothing
Else
' strip off last "OR" in the filter
If Right(varSize, 4) = " OR " Then
varSize = Left(varSize, Len(varSize) - 4)
End If
' Add some parentheses around the subfilter
varWhere = varWhere & "( " & varSize & " )"
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
Someone please help me out with this, I would appreaciate it.
Note: I have attached a copy of my database,so if you want to take a look..open up the databse and go to "Disc Search Form"
