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

Filter search based on subform

P: 7
I have the following code in my program allowing users to search for entries matching up to five different criteria at once. I changed the form to now contain a subform and would like to allow the users to search by fields contained in the subform. The field on the subform that I would like to include in the search is [Presentation], but just including [Presentation] in the code did not do the trick -the search was not recognizing the subform field [Presentation]. The code is pasted below:

Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdInitiate_Search_Click()
  2. On Error GoTo Err_cmdInitiate_Search_Click
  3.  
  4.     Dim strCriteria(5) As String
  5.     Dim strSearchField(5) As String
  6.     Dim LinkCriteria(5) As String
  7.     Dim LinkCriteriaFinal As String
  8.     Dim strSQL As String
  9.     Dim i As Integer
  10.  
  11.     If IsNull(Me!criteria1) Then
  12.         MsgBox "Please enter valid search criteria.", vbCritical, "Error"
  13.         Me![criteria1].SetFocus
  14.         Exit Sub
  15.     Else
  16.         strCriteria(0) = Me![criteria1]
  17.     End If
  18.     If Not IsNull(Me![Criteria2]) Then
  19.         strCriteria(1) = Me![Criteria2]
  20.     Else
  21.         strCriteria(1) = Me![criteria1]
  22.     End If
  23.     If Not IsNull(Me![Criteria3]) Then
  24.         strCriteria(2) = Me![Criteria3]
  25.     Else
  26.         strCriteria(2) = Me![criteria1]
  27.     End If
  28.     If Not IsNull(Me![Criteria4]) Then
  29.         strCriteria(3) = Me![Criteria4]
  30.     Else
  31.         strCriteria(3) = Me![criteria1]
  32.     End If
  33.     If Not IsNull(Me![Criteria5]) Then
  34.         strCriteria(4) = Me![Criteria5]
  35.     Else
  36.         strCriteria(4) = Me![criteria1]
  37.     End If
  38.  
  39.     If IsNull(Me!SearchField1) Then
  40.         MsgBox "Please enter valid search field.", vbCritical, "Error"
  41.         Me![SearchField1].SetFocus
  42.         Exit Sub
  43.     Else
  44.         strSearchField(0) = Me![SearchField1]
  45.     End If
  46.     If Not IsNull(Me![SearchField2]) Then
  47.         strSearchField(1) = Me![SearchField2]
  48.     Else
  49.         strSearchField(1) = Me![SearchField1]
  50.     End If
  51.     If Not IsNull(Me![SearchField3]) Then
  52.         strSearchField(2) = Me![SearchField3]
  53.     Else
  54.         strSearchField(2) = Me![SearchField1]
  55.     End If
  56.     If Not IsNull(Me![Searchfield4]) Then
  57.         strSearchField(3) = Me![Searchfield4]
  58.     Else
  59.         strSearchField(3) = Me![SearchField1]
  60.     End If
  61.     If Not IsNull(Me![SearchField5]) Then
  62.         strSearchField(4) = Me![SearchField5]
  63.     Else
  64.         strSearchField(4) = Me![SearchField1]
  65.     End If
  66.  
  67.     Me.Visible = False
  68.  
  69. For i = 0 To 4
  70.     Select Case strSearchField(i)
  71.     Case 1
  72.         LinkCriteria(i) = "[Region]"
  73.     Case 2
  74.         LinkCriteria(i) = "[Firm_Name]"
  75.     Case 3
  76.         LinkCriteria(i) = "[Address_1]"
  77.     Case 4
  78.         LinkCriteria(i) = "[Address_2]"
  79.     Case 5
  80.         LinkCriteria(i) = "[Contact]"
  81.     Case 6
  82.         LinkCriteria(i) = "[City]"
  83.     Case 7
  84.         LinkCriteria(i) = "[State]"
  85.     Case 8
  86.         LinkCriteria(i) = "[Zip]"
  87.     Case 9
  88.         LinkCriteria(i) = "[County]"
  89.     Case 10
  90.         LinkCriteria(i) = "[Country]"
  91.     Case 11
  92.         LinkCriteria(i) = "[Orig_Info]"
  93.     Case 12
  94.         LinkCriteria(i) = "[Last_Update]"
  95.     Case 13
  96.         LinkCriteria(i) = "[Firm_Size]"
  97.     Case 14
  98.         LinkCriteria(i) = "[Specialty_Work]"
  99.     Case 15
  100.         LinkCriteria(i) = "[Last_Office_Visit]"
  101.     Case 16
  102.         LinkCriteria(i) = "[Residential]"
  103.     Case 17
  104.         LinkCriteria(i) = "[Type of Operation]"
  105.     Case 18
  106.         LinkCriteria(i) = "[Presentation]"
  107.     Case 19
  108.         LinkCriteria(i) = "[COR200]"
  109.     Case 20
  110.         LinkCriteria(i) = "[COR300]"
  111.     Case 21
  112.         LinkCriteria(i) = "[COR400]"
  113.     Case 22
  114.         LinkCriteria(i) = "[COR500]"
  115.     Case 23
  116.         LinkCriteria(i) = "[Binder Holder?]"
  117.     Case 24
  118.         LinkCriteria(i) = "[Yearly Sales Volume]"
  119.     Case 25
  120.         LinkCriteria(i) = "[Type of Entry]"
  121.     End Select
  122. Next i
  123.  
  124.  
  125. For i = 0 To 4
  126.     If ViewOptions.Value = 1 Then
  127.         If i = 0 Then
  128.             If IsNumeric(strCriteria(i)) Then
  129.                 LinkCriteriaFinal = "(" & LinkCriteria(i) & " like " & strCriteria(i) & " &'*' )"
  130.             Else
  131.                 LinkCriteriaFinal = "(" & LinkCriteria(i) & " like '" & strCriteria(i) & "' &'*' )"
  132.             End If
  133.         Else
  134.             If IsNumeric(strCriteria(i)) Then
  135.                 LinkCriteriaFinal = LinkCriteriaFinal & " and (" & LinkCriteria(i) & " like  " & strCriteria(i) & " & '*' )"
  136.             Else
  137.                 LinkCriteriaFinal = LinkCriteriaFinal & " and (" & LinkCriteria(i) & " like  '" & strCriteria(i) & "' & '*' )"
  138.             End If
  139.         End If
  140.     Else
  141.         If i = 0 Then
  142.             If IsNumeric(strCriteria(i)) Then
  143.                 LinkCriteriaFinal = "(" & LinkCriteria(i) & " like " & strCriteria(i) & " &'*' )"
  144.             Else
  145.                 LinkCriteriaFinal = "(" & LinkCriteria(i) & " like '" & strCriteria(i) & "' &'*' )"
  146.             End If
  147.         Else
  148.             If IsNumeric(strCriteria(i)) Then
  149.                 LinkCriteriaFinal = LinkCriteriaFinal & " or (" & LinkCriteria(i) & " like  " & strCriteria(i) & " & '*' )"
  150.             Else
  151.                 LinkCriteriaFinal = LinkCriteriaFinal & " or (" & LinkCriteria(i) & " like  '" & strCriteria(i) & "' & '*' )"
  152.             End If
  153.         End If
  154.      End If
  155. Next i
  156.  
  157.     MsgBox "The Link Criteria specified is " & LinkCriteriaFinal
  158.  
  159.     Me.Visible = False
  160.  
  161.     DoCmd.OpenForm "QueryResults", , , LinkCriteriaFinal, acFormReadOnly
  162.  
  163.  
  164. Exit_cmdInitiate_Search_Click:
  165.     Exit Sub
  166.  
  167. Err_cmdInitiate_Search_Click:
  168.     MsgBox Error$
  169.     Resume Exit_cmdInitiate_Search_Click
  170.  
  171. End Sub
Any ideas?

Thank you!
Mar 22 '07 #1
Share this question for a faster answer!
Share on Google+

Post your reply

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