By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,658 Members | 1,924 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,658 IT Pros & Developers. It's quick & easy.

Need to include Field List In Form, help!

P: 10
Hey guys,
I have two main questions
First off (pictures are kind of blurry)
I have this table
http://i197.photobucket.com/albums/a.../DiscTable.jpg

And thats my database I use my Form on

I created a search form:
http://i197.photobucket.com/albums/a...SearchForm.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/a...earchForm2.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:

Expand|Select|Wrap|Line Numbers
  1.  
  2. Option Compare Database
  3. Option Explicit
  4.  
  5. Private Sub btnClear_Click()
  6. Dim intIndex As Integer
  7.  
  8. ' Clear all search items
  9.  
  10.  
  11. ' De-select each item in Color List (multiselect list)
  12. For intIndex = 0 To Me.SizeList.ListCount - 1
  13. Me.SizeList.Selected(intIndex) = False
  14. Next
  15.  
  16. End Sub
  17.  
  18. Private Sub btnSearch_Click()
  19.  
  20. ' Update the record source
  21. Me.DiscForm.Form.RecordSource = "SELECT * FROM DiscTable " & BuildFilter
  22.  
  23. ' Requery the subform
  24. Me.DiscForm.Requery
  25. End Sub
  26.  
  27.  
  28. Private Sub Form_Load()
  29.  
  30. ' Clear the search form
  31. btnClear_Click
  32.  
  33. End Sub
  34.  
  35. Private Function BuildFilter() As Variant
  36. Dim varWhere As Variant
  37. Dim varSize As Variant
  38. Dim varItem As Variant
  39. Dim intIndex As Integer
  40.  
  41. varWhere = Null ' Main filter
  42. varSize = Null ' Subfilter used for colors
  43.  
  44.  
  45.  
  46. ' Check for Colors in multiselect list
  47. For Each varItem In Me.SizeList.ItemsSelected
  48. varSize = varSize & "[Size] = """ & _
  49. Me.SizeList.ItemData(varItem) & """ OR "
  50.  
  51. Next
  52.  
  53. ' Test to see if we have subfilter for colors...
  54. If IsNull(varSize) Then
  55. ' do nothing
  56. Else
  57. ' strip off last "OR" in the filter
  58. If Right(varSize, 4) = " OR " Then
  59. varSize = Left(varSize, Len(varSize) - 4)
  60. End If
  61.  
  62. ' Add some parentheses around the subfilter
  63. varWhere = varWhere & "( " & varSize & " )"
  64. End If
  65.  
  66. ' Check if there is a filter to return...
  67. If IsNull(varWhere) Then
  68. varWhere = ""
  69. Else
  70. varWhere = "WHERE " & varWhere
  71.  
  72. ' strip off last "AND" in the filter
  73. If Right(varWhere, 5) = " AND " Then
  74. varWhere = Left(varWhere, Len(varWhere) - 5)
  75. End If
  76. End If
  77.  
  78. BuildFilter = varWhere
  79.  
  80. End Function
  81.  
Someone please help me out with this, I would appreaciate it.
Jun 14 '07 #1
Share this Question
Share on Google+
1 Reply


MMcCarthy
Expert Mod 10K+
P: 14,534
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/a...earchForm2.jpg
I don't use the field list setting very often but I'm not aware of any way of restricting the list. However, you can also base the list on a query so create a query with just the fields you want to show in the list and set the query as the lists row source. This should solve that problem.

As for building the subforms recordsource, try the following:

Expand|Select|Wrap|Line Numbers
  1. Private Sub btnSearch_Click
  2.     ' Update the record source
  3.     Me.DiscForm.Form.RecordSource "SELECT " & BuildSelect & " FROM DiscTable " & BuildFilter
  4.     ' Requery the subform
  5.     Me.DiscForm.Requery
  6. End Sub 
  7.  
  8. Private Function BuildSelect() As String
  9. Dim strSelect As String
  10. Dim varItem As Variant
  11.  
  12.     For Each varItem In Me.PressureList.ItemsSelected
  13.         strSelect = strSelect & Me.PressureList.ItemData(varItem) & ", " 
  14.     Next
  15.  
  16.     ' Remove the last comma
  17.     strSelect = Left(strSelect, Len(strSelect) - 2)
  18.  
  19.     BuildSelect = strSelect
  20.  
  21. End Function
  22.  
  23. Private Function BuildFilter() As String
  24. Dim strWhere As String
  25. Dim strPressure As String
  26. Dim varItem As Variant
  27.  
  28.     strWhere = "WHERE ("
  29.  
  30.     ' Check for Colors in multiselect list
  31.     For Each varItem In Me.SizeList.ItemsSelected
  32.         strWhere = strWhere & "[Size] = """ & _
  33.         Me.SizeList.ItemData(varItem) & """ OR "
  34.     Next
  35.  
  36.     ' set pressure criteria
  37.     For Each varItem In Me.PressureList.ItemsSelected
  38.         strPressure = strPressure & Me.PressureList.ItemData(varItem) & " <> 'N/A' AND " 
  39.     Next
  40.  
  41.     ' Remove last AND
  42.     strPressure = Left(strPressure, Len(strPressure) - 5)
  43.  
  44.     ' Test to see if we have subfilter for colors...
  45.     If strWhere = "WHERE (" Then
  46.         strWhere = strPressure & ")"
  47.     Else
  48.         ' strip off last "OR" in the filter
  49.         strWhere = Left(strWhere, Len(strWhere) - 4)
  50.         strWhere = strWhere & ") AND (" & strPressure & ")"
  51.     End If
  52.  
  53.     BuildFilter = strWhere
  54.  
  55. End Function
  56.  
Jun 14 '07 #2

Post your reply

Sign in to post your reply or Sign up for a free account.