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

filter a subform based on multiple multi select list boxes

P: 15
Hi Everyone and many thanks for your time.. I am trying to begin access and a bit of VBA i am enjoying it but I have a annoying problem I just can’t get any where on.
My databse mostly includes bits of code for different examples. I have one last thing to finish.
I am trying to create a search form that will allow users to select criteria from multiple sources eg ,multi select list boxes , combo boxes.

I have a subform showing all the required fields under the reports Tab of my main form. It’s unfiltered to begin and shows all records, when the user searches the form via the unbound controls listed above I have one error that I can’t figure out. It’s regarding my multi select list boxes.

I have 3 multi select list boxes that allow the user to search for counties, nationality’s or qualifications and then filter the subform to show results via my “Build filter” function .

County and nationality searches work fine with but when the user searches for qualification the form filters and shows the results which is great but right away but they are asked for the parameter for tblMemberqualifications.qualCode. even though its just filtered the suborm with the selected criteria.
If i enter the value for “qualcode” lets say 417 the form will work as desired. If i don’t enter the value i get the error message Run time error 2467 “ the expression you entered refers to an object that is closed our doesn’t exist”.

I think it maybe to do with the filtering on the main form “Me.RecordsetClone”. There is a similar subform search page under one of my main form Tab’s that uses the method below and works fine.

I think that “.txtGoToRecord” was a hidden text box that holds the current record for the filter but for the life of me i cant find it anywhere on the main form. I can only see it mentioned under parts 2 and 3 of my code.

Thanks or all your time and your help is greatly appreciated .
Kind regards all the way from Ireland. Thanks again. JAMES.

1 Filter
Expand|Select|Wrap|Line Numbers
  1. Private Sub btnSearch_Click()
  2.  Me.Filter = BuildFilter
  3.         Me.FilterOn = True
  4.         If Me.CurrentRecord = 1 Then
  5.             Forms!frmdcd.Filter = BuildFilter
  6.             Forms!frmdcd.FilterOn = True
  7.  
  8.             Dim rst As Object
  9.             Set rst = Me.RecordsetClone
  10.             On Error Resume Next
  11.             rst.MoveLast
  12.             On Error GoTo 0
  13.             Forms!frmdcd.txtGoToRecord.Value = Me.CurrentRecord
  14.             Me.lblRF.Caption = "Records Found = " & rst.RecordCount
  15.             Me.lblRF.Visible = True
  16.  
  17.         Else
  18.             Forms!frmdcd.FilterOn = False
  19.             Me.lblRF.Caption = "Records Found = 0"
  20.             Me.lblRF.Visible = True
  21.             Forms!frmdcd.txtGoToRecord.Value = ""
  22.             Me.FilterOn = True
  23.         'DoCmd.GoToRecord acDataForm, "frmDCD", acFirst
  24.  
  25.         End If
  26.  
2
Expand|Select|Wrap|Line Numbers
  1.  Private Sub Form_Load()
  2. DoCmd.MoveSize Right:=300, down:=300, Width:=18300, Height:=14000
  3. Me.txtGoToRecord.Value = Me.RegNumber.Value
  4. Me!frmChooseRegister.Visible = True
  5. Me!frmChooseRegistrationType.Visible = False
  6. Me!frmChooseSpecialistRegister.Visible = False
  7. Me!frmSpecRegType.Visible = False
  8. Me!frmFullQualFrom.Visible = False
  9. Me!frmNursesApp.Visible = False
  10. Me!frmTempReg.Visible = False
  11. Me!frmRestDentist.Visible = False
  12. Me!frmRestNurse.Visible = False
  13. Me!frmSpecRegFull.Visible = False
  14. Me!frmHygienistRegType.Visible = False
  15. Me!frmHygienistQual.Visible = False
  16. Me!frmRestSpecialist.Visible = False
  17. Me!frmRestHygienist.Visible = False
  18. Me!frmHygienistIrishUKApp.Visible = False
  19. Me!frmHygienistEEAApp.Visible = False
  20. Me!frmIrishQual.Visible = False
  21. Me!frmEEANonEEAQual.Visible = False
  22. Me!frmNursesRegType.Visible = False
  23. Me!frmNursesQual.Visible = False
  24. Me!frmEEAQual.Visible = False
  25. Me!frmSpecRegQuals.Visible = False
  26. Me!frmPassedExam.Visible = False
  27. 'Me!frmNewRegistration.Visible = False
  28. 'Me!frmStartScreen.Visible = True
  29. Me!TabCtl1.Visible = True
  30. End Sub 
3
Expand|Select|Wrap|Line Numbers
  1.  Private Sub TxtGoToRecord_AfterUpdate()
  2. Dim C As Integer
  3. s = txtGoToRecord.Value
  4. C = DMax("RegNumber", "tblMemberDetails")
  5. If txtGoToRecord.Value > 0 And txtGoToRecord.Value < C + 2 Then
  6. DoCmd.GoToRecord acDataForm, "frmDCD", acGoTo, txtGoToRecord.Value
  7. End If
  8. End Sub 
4.My Search filter
Expand|Select|Wrap|Line Numbers
  1. Private Function BuildFilter() As Variant
  2.  
  3.     Dim varWhere As Variant
  4.     Dim varItem As Variant
  5.     Dim intIndex As Integer
  6.     Dim CountyCode As Variant
  7.     Dim NationalityCode As Variant
  8.     Dim QualCode As Variant
  9.  
  10.     varWhere = Null  ' Main filter
  11.     CountyCode = Null  ' Subfilter used for CountyCode
  12.     NationalityCode = Null ' Subfilter used for NationalityCode
  13.     QualCode = Null ' Subfilter used for qualCode
  14.  
  15.  
  16.     ' Check for LIKE First Name
  17.  
  18. If Me.txtFirstName > "" Then
  19.         varWhere = varWhere & "[FirstName] LIKE """ & Me.txtFirstName & "*"" AND "
  20.     End If
  21.  
  22.     ' Check for LIKE Last Name
  23.     If Me.txtSurname > "" Then
  24.         varWhere = varWhere & "[surname] LIKE """ & Me.txtSurname & "*"" AND "
  25.     End If
  26.  
  27.     If Me.txtRegNumber > "" Then
  28.         varWhere = varWhere & "[regnumber] like """ & Me.txtRegNumber & """  And "
  29.    End If
  30.   ' Check for county in multiselect list
  31.     For Each varItem In Me.lstCountyCode.ItemsSelected
  32.         CountyCode = CountyCode & " [tblMemberDetails].[CountyCode] = """ & _
  33.                     Me.lstCountyCode.ItemData(varItem) & """ OR "
  34.  
  35.     Next
  36.    'Test to see if we have subfilter for colors...
  37.     If IsNull(CountyCode) Then
  38.         ' do nothing
  39.     Else
  40.         ' strip off last "OR" in the filter
  41.         If Right(CountyCode, 4) = " OR " Then
  42.             CountyCode = Left(CountyCode, Len(CountyCode) - 4)
  43.         End If
  44.  
  45.         'Add some parentheses around the subfilter
  46.         varWhere = varWhere & "( " & CountyCode & " ) AND "
  47.     End If
  48.  
  49.  
  50.       ' Qual Code
  51.     For Each varItem In Me.lstqual1.ItemsSelected
  52.         QualCode = QualCode & " [tblMemberQualifications].[qualCode] = " & _
  53.                     Me.lstqual1.ItemData(varItem) & " OR "
  54.  
  55.     Next
  56.  
  57.     'Test to see if we have subfilter for colors...
  58.     If IsNull(QualCode) Then
  59.         ' do nothing
  60.     Else
  61.         ' strip off last "OR" in the filter
  62.         If Right(QualCode, 4) = " OR " Then
  63.             QualCode = Left(QualCode, Len(QualCode) - 4)
  64.         End If
  65.  
  66.         'Add some parentheses around the subfilter
  67.         varWhere = varWhere & "( " & QualCode & " ) and "
  68.     End If
  69.  
  70.   'NationalityCode
  71.  
  72.         ' Check for Nationality in multiselect list
  73.     For Each varItem In Me.lstNationality.ItemsSelected
  74.         NationalityCode = NationalityCode & " [tblmemberdetails].[NationalityCode] = """ & _
  75.                     Me.lstNationality.ItemData(varItem) & """ OR "
  76.  
  77.     Next
  78.  
  79.     'Test to see if we have subfilter for colors...
  80.     If IsNull(NationalityCode) Then
  81.         ' do nothing
  82.     Else
  83.         ' strip off last "OR" in the filter
  84.         If Right(NationalityCode, 4) = " OR " Then
  85.             NationalityCode = Left(NationalityCode, Len(NationalityCode) - 4)
  86.         End If
  87.  
  88.         'Add some parentheses around the subfilter
  89.         varWhere = varWhere & "( " & NationalityCode & " )  "
  90.     End If
  91.  
  92.        'Check if there is a filter to return...
  93.     If IsNull(varWhere) Then
  94.         varWhere = "''"
  95.     Else
  96.  
  97.         ' strip off last "AND" in the filter
  98.         If Right(varWhere, 5) = " AND " Then
  99.             varWhere = Left(varWhere, Len(varWhere) - 5)
  100.         End If
  101.  
  102.     End If
  103.  
  104.  
  105.     BuildFilter = varWhere
  106.  
  107.     End Function
  108.  
Mar 1 '09 #1
Share this Question
Share on Google+
1 Reply


P: 15
I was missing the qualcode field in my main frm. As soon as i added it to the record source of my main form it works.

Thanks again for your time and expertise its hugely appericated.

Thanks and best of luck in the future.

Kind regaeds,

James
Mar 1 '09 #2

Post your reply

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