473,593 Members | 2,841 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

filter a subform based on multiple multi select list boxes

15 New Member
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 tblMemberqualif ications.qualCo de. 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.RecordsetCl one”. 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
1 6789
woodey2002
15 New Member
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

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

Similar topics

3
18213
by: arthur-e | last post by:
I can filter a query for a report on a field of a subform BUT now.... I'd like to be able to select more than one item in a multi-list box to select all the records. ALSO to use two fields (or more) fields to filter a query based on selections in two or more fields: All the records with NAME = all the names selected in a list box BETWEEN StartDate and EndDate
1
7838
by: Robert Neville | last post by:
I would like to add filter functionality to my database whether through the Main form or the subform. This question may be rudimentary, yet I have not less experience with filtering data outside from queries. Let me just add that Allen Browne excellent article about this subject may not apply to this scenario on an elementary level. (Here's the link to the article; Filter a Form on a Field in a Subform -...
25
10197
by: Lyn | last post by:
Hi, I am working on a genealogy form. The only table (so far) lists everybody in the family, one record per person. Each record has an autonum ID. The parent form (frmMainForm) displays the data in each record, which includes the ID of the father and the mother (who also have records in the table). One record per form. I have a Tab Control in the form, and in one of the tabs I have a subform (sfmSiblings) in which I wish to list...
2
4625
by: Tim Marshall | last post by:
The following is happening in two instances in A2003. The one I s\describe is the easiest one. Theme controls and autocorrupt have been turned off long ago. An unbound main form with a list box (query row source and multi-select turned off) and a datasheet subform. The short cut menu for the datasheet subform includes standard sort ascending and descending and remove filter/sort menu items. The recordsource for the subform datasheet...
6
4875
by: Joe | last post by:
I have 2 multi-list boxes, 1 displays course categories based on a table called CATEGORIES. This table has 2 fields CATEGORY_ID, CATEGORY_NAME The other multi-list box displays courses based on a table called COURSES. This table has 2 fields CATEGORY_ID, COURSE_NAME. The CATEGORY_ID is a FK in COURSES and a PK in CATEGORIES. I want to populate the course list box based on any category(s)
3
2881
by: syounger | last post by:
Hi. I have a report in Access 2000 that is based on selection made from a series of interdependent list boxes. The boxes I have right now are Source, Table, Column, Date. The user chooses Source first, then the Table list box populates only tables from that source. Once a table is chosen, only the columns for that table appear in the Column list box. In the date box, the only dates that appear are those that are stored against the...
11
6086
by: Bob | last post by:
I am in the process of upgrading an Access database to SQL Server (and climbing that learning curve!). The wizard happily upgraded all the tables and I can link to them OK using ODBC. The application controls allocation of revisions to aircraft maintenance manuals for an airline type operation. In the application there is a form loaded at start-up allowing the user/s to select the records that they are currently interested in from 4...
9
15833
by: natwong | last post by:
Hi All, I'm a newbie in terms of Access and some of its functionality . I've been stuck on this problem for a couple days, even after searching the Web, etc. Currently I have five combo boxes (actually list boxes) that are multiselects in my main form. I need to use these combo boxes to filter a subform within my main form. My combo boxes are as follows: 1. A - 4 select options 2. B - 10 select options 3. C - 4 select options
1
4761
by: JC21 | last post by:
Hi guys, I was wondering if someone could give me some insight on this. On a form I would like to have 3 combo boxes which can be used as filters. I would like the information to be displayed on a Subform/Subreport and then be able to print a report. For example Filter1, list the company name. Filter2 list the options (open, close). Filter3 is related to the company acct balance, the options are (<$1000, >$1000, >$5000). I would like to be...
0
7874
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
8241
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
8366
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
0
8227
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
6646
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
0
5402
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
3853
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
3893
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
0
1203
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.