I'm getting no error messages here, but I'm not getting any results visible when the filter is applied. There are 2 unbound combo boxes in the main form, SetStudy and SetCDB. The only possible values for the column [ROLE] are the 9 reflected in the code below. (The form and table for [ROLE] have the Row Source: "CEC Chair";"CEC Co-Chair";"CEC Member";"DSMB Chair";"DSMB Co-Chair";"DSMB Member";"CEC/DSMB Chair";"CEC/DSMB Co-Chair";"CEC/DSMB Member") - Private Sub SetCDB_AfterUpdate()
-
If Me.SetCDB.Selected(0) Then
-
Me.SubR.Form.Filter = "[ROLE] = " & Chr(34) & "CEC Chair" & Chr(34) & " Or [ROLE] = " & Chr(34) & "CEC Co-Chair" & Chr(34) & " Or [ROLE] = " & Chr(34) & "CEC Member" & Chr(34) & " And [STUDY NAME] = " & Chr(34) & Me.SetStudy.Value & Chr(34)
-
ElseIf Me.SetCDB.Selected(1) Then
-
Me.SubR.Form.Filter = "[ROLE] = " & Chr(34) & "DSMB Chair" & Chr(34) & " Or [ROLE] = " & Chr(34) & "DSMB Co-Chair" & Chr(34) & " Or [ROLE] = " & Chr(34) & "DSMB Member" & Chr(34) & " And [STUDY NAME] = " & Chr(34) & Me.SetStudy.Value & Chr(34)
-
ElseIf Me.SetCDB.Selected(2) Then
-
Me.SubR.Form.Filter = "[ROLE] = " & Chr(34) & "CEC/DSMB Chair" & Chr(34) & " Or [ROLE] = " & Chr(34) & "CEC/DSMB Co-Chair" & Chr(34) & " Or [ROLE] = " & Chr(34) & "CEC/DSMB Member" & Chr(34) & " And [STUDY NAME] = " & Chr(34) & Me.SetStudy.Value & Chr(34)
-
Else
-
Me.SubR.Form.Filter = "[ROLE ID] = 0"
-
End If
-
Me.SubR.Form.FilterOn = True
-
SubR.Requery
-
End Sub
Any ideas what's going wrong with the filtering?
12  1149  zmbd 5,501
Recognized Expert Moderator Expert
line 12: Me!SubR.Form.Re fresh
you shouldn't need a new query if all you are doing is applying a filter to the form's current record-set; however,
line 12: Me!SubR.Form.Re query
Thanks, Z, but that wasn't it. It's performing the same with both syntaxes.
zmbd 5,501
Recognized Expert Moderator Expert
The most common issue is a malformed string which is why I always build my strings first and then set to the form or the called function.
Give me a moment to double check things....
 zmbd 5,501
Recognized Expert Moderator Expert
Ok, a little re-arrangement on the code - Private Sub SetCDB_AfterUpdate()
-
Dim zSQL As String
-
Dim zSelected as Integer
-
'
-
zSelected = Me.setcdb.value
-
'
-
Select Case zSelected
-
Case 1
-
zSQL = "[ROLE] = " & Chr(34) & "CEC Chair" & Chr(34) & _
-
" Or [ROLE] = " & Chr(34) & "CEC Co-Chair" & Chr(34) & _
-
" Or [ROLE] = " & Chr(34) & "CEC Member" & Chr(34) & _
-
" And [STUDY NAME] = " & Chr(34) & Me.SetStudy.Value & Chr(34)
-
Case 2
-
zSQL = "[ROLE] = " & Chr(34) & "DSMB Chair" & Chr(34) & _
-
" Or [ROLE] = " & Chr(34) & "DSMB Co-Chair" & Chr(34) & _
-
" Or [ROLE] = " & Chr(34) & "DSMB Member" & Chr(34) & _
-
" And [STUDY NAME] = " & Chr(34) & Me.SetStudy.Value & Chr(34)
-
Case 3
-
zSQL = "[ROLE] = " & Chr(34) & "CEC/DSMB Chair" & Chr(34) & _
-
" Or [ROLE] = " & Chr(34) & "CEC/DSMB Co-Chair" & Chr(34) & _
-
" Or [ROLE] = " & Chr(34) & "CEC/DSMB Member" & _
-
Chr(34) & " And [STUDY NAME] = " & Chr(34) & Me.SetStudy.Value & Chr(34)
-
Case Else
-
zSQL = "[ROLE ID] = 0"
-
End Select
-
'
-
Debug.Print zSQL
-
'
-
Me!SubR.Form.Filter = zSQL
-
'
-
Me!SubR.Form.FilterOn = True
-
'
-
Me!SubR.Form.Refresh
-
' Me!SubR.Requery
-
End Sub
Open the VBE, <CTRL><G> to open the immediate panel
Run your form
Check the immediate panel for properly formatted string for the filter
((another option along the line of the OP code)) - Private Sub SetCDB_AfterUpdate()
-
Dim zSQL As String
-
'
-
Select Case Me.setcdb.ListIndex
-
Case 0
-
zSQL = "[ROLE] = " & Chr(34) & "CEC Chair" & Chr(34) & _
-
" Or [ROLE] = " & Chr(34) & "CEC Co-Chair" & Chr(34) & _
-
" Or [ROLE] = " & Chr(34) & "CEC Member" & Chr(34) & _
-
" And [STUDY NAME] = " & Chr(34) & Me.SetStudy.Value & Chr(34)
-
Case 1
-
zSQL = "[ROLE] = " & Chr(34) & "DSMB Chair" & Chr(34) & _
-
" Or [ROLE] = " & Chr(34) & "DSMB Co-Chair" & Chr(34) & _
-
" Or [ROLE] = " & Chr(34) & "DSMB Member" & Chr(34) & _
-
" And [STUDY NAME] = " & Chr(34) & Me.SetStudy.Value & Chr(34)
-
Case 2
-
zSQL = "[ROLE] = " & Chr(34) & "CEC/DSMB Chair" & Chr(34) & _
-
" Or [ROLE] = " & Chr(34) & "CEC/DSMB Co-Chair" & Chr(34) & _
-
" Or [ROLE] = " & Chr(34) & "CEC/DSMB Member" & _
-
Chr(34) & " And [STUDY NAME] = " & Chr(34) & Me.SetStudy.Value & Chr(34)
-
Case Else
-
zSQL = "[ROLE ID] = 0"
-
End Select
-
'
-
Debug.Print zSQL
-
'
-
Me!SubR.Form.Filter = zSQL
-
'
-
Me!SubR.Form.FilterOn = True
-
'
-
Me!SubR.Form.Refresh
-
' Me!SubR.Requery
-
End Sub
I'm getting an "argument not optional" error for .Selected using the "Case" structure above, but I'll try using that string technique with my original code.
Interesting result, though it fits with the initial problem. -
Private Sub SetCDB_AfterUpdate()
-
Dim zSQL As String
-
Forms!FRM_ROLES!SubR!SubStudy.DefaultValue = Chr(34) & Me.SetStudy.Value & Chr(34)
-
If Me.SetCDB.Selected(0) Then
-
zSQL = "[ROLE] = " & Chr(34) & "CEC Chair" & Chr(34) & " Or [ROLE] = " & Chr(34) & "CEC Co-Chair" & Chr(34) & " Or [ROLE] = " & Chr(34) & "CEC Member" & Chr(34) & " And [STUDY NAME] = " & Chr(34) & Me.SetStudy.Value & Chr(34)
-
ElseIf Me.SetCDB.Selected(1) Then
-
zSQL = "[ROLE] = " & Chr(34) & "DSMB Chair" & Chr(34) & " Or [ROLE] = " & Chr(34) & "DSMB Co-Chair" & Chr(34) & " Or [ROLE] = " & Chr(34) & "DSMB Member" & Chr(34) & " And [STUDY NAME] = " & Chr(34) & Me.SetStudy.Value & Chr(34)
-
ElseIf Me.SetCDB.Selected(2) Then
-
zSQL = "[ROLE] = " & Chr(34) & "CEC/DSMB Chair" & Chr(34) & " Or [ROLE] = " & Chr(34) & "CEC/DSMB Co-Chair" & Chr(34) & " Or [ROLE] = " & Chr(34) & "CEC/DSMB Member" & Chr(34) & " And [STUDY NAME] = " & Chr(34) & Me.SetStudy.Value & Chr(34)
-
Else
-
zSQL = "[ROLE ID] = 0"
-
End If
-
Debug.Print zSQL
-
Me!SubR.Form.Filter = zSQL
-
Me!SubR.Form.FilterOn = True
-
Me!SubR.Form.Refresh
-
End Sub
Gives the response: zmbd 5,501
Recognized Expert Moderator Expert
I was afraid of that... I've not used the selected property like that before... I'll revise the post
The selected property is usually used to set a selection in a list box.
I've re-written the code to use the value of the cbox instead; however, this is assuming that the cbox has the bound column set so that the first selection returns 1, the second 2, etc...
Turns out I was using the wrong syntax for .Selected; it needs to be in the format Me.SetCDB.Selec ted(0) = True.
But with it corrected in the code above, I get the same string printed, so maybe that's not the location of the error?
zmbd 5,501
Recognized Expert Moderator Expert
The selected property isn't used to retrieve the row selected, it is used to set the selected row.
(This will not however, set the value of the cbox)
Please see the revised code in Post#5
Two options
First Option
The revision now will return the value of the bound column for the cbox in Line 5 and the compare against the select...case starting on line 7.
Of course, you may have to modify the Case comparisons based upon what the actual return is for the cbox's bound column.
Second Option:
Just thought of this: We could potentially us the listindex property... and this more closely matches your original logic. Not really how I would approach the use of a combobox...
you got me "out of the box" on this one ;-)
Sign in to post your reply or Sign up for a free account.
Similar topics | |
by: Jason |
last post by:
I am trying to filter records in a primary form based on records in
related tables. The data in the related tables is being displayed in
the primary form
through subforms. To be more specific, I have a primary form named
TestResults, which is connected to data in a table named TestResults.
There are basically two other tables that are related to the
TestResults table (and the primary form) named Names-Normalized and
SiteAddresses. The...
|
by: William Wisnieski |
last post by:
Hello Everyone,
I have a main form with a datasheet subform that I use to query by form.
After the user selects two criteria on the main form and clicks the
cmdShowResults button on the main form, the subform returns the records
based on the two criteria. The criteria used on the main form are values
selected in two list boxes. When the user clicks on the first list box
(lstCollege), it returns values in the second list box...
|
by: johnson_cy |
last post by:
I am using Access 2000.
My main form has a record source of a table (employeeTbl with key of
. It is filtering the record results.
My subform and mainform have the link child/link master set to
and the source object is the subform.
My subform record source is a query from another table (vacationTbl).
FYI: I was filtering on the subform, but removed it and replaced with
|
by: aintnorock |
last post by:
I have a relatively simple relational database. One of my forms is based
on a query with a 3 parameter sort: Status / Priority / Customer.
When I open the form, the records are sorted properly.
When I go to filter the form, to get a subset of the form, e.g. filter
by Responsible Party, I would like the form to sort this way:
Reposponsible Party / Status / Priority / Customer.
|
by: crazytegger |
last post by:
I am trying to filter a subforms combolist options through a combolist on the main form. Heres what i have:
Form: frmOrderDetail
Datasource: tblOrders
cmbOrderSupplierID w/ control source orders.
Subform: frmOrderDetailSubform
datasource: qryOrderDetails
cmbItemID w/ row source:
SELECT ., .
| | |
by: BSTAFFORD |
last post by:
I was researching some vba code on TheScripts.com and found this post. I’m looking to accomplish the same idea but I need to filter a sub form query with 3 combo boxes from the main form.
This is part of a tech support call log that I’m implementing as a sort of post production quality control log.
I appreciate any help you can give me
|
by: diogenes |
last post by:
"Rick Brandt" <rickbrandt2@hotmail.comwrote in news:bPnKj.456$%41.325
@nlpi064.nbdc.sbc.com:
I used this approach, and it works a treat!
ID In(SELECT Order_ID FROM orderitems WHERE NAME = 'product')
I've not used an In clause before. Thanks a lot for the education.
|
by: jbrumbau |
last post by:
Hello,
I have some specification sheets with subforms I have created. I'm having a problem where if you right click and filter/sort any field in the subform (which is tied to the main form in a 1:1 relationship), then the subform only displays the first record. This ends up permanently altering the spec sheet. I notice that when this happens, text typically shows up in the "Order By" or "Filter" fields of the subform. My only resort is to...
|
by: lovelydan |
last post by:
I have designed a form(main form) which has a record source that runs subforms. this works perfectly. I also have a macro that is attached to this subform which filters records on a different form. One of my subforms contains personnel IDs and few details. When I click on the ID field the macro runs and opens another form, filtering it by the ID number. This works when the subform is opened independently but when the subform is opened on a...
|
by: zmbd |
last post by:
Ok,
So I've created forms with both filtering as in
http://bytes.com/topic/access/insights/590551-example-filtering-form, same concept with cascading comboboxs, and I have used Main/Subform formats too for related tables/queries.
I think it is my inexperience in that I do not see the inherent advantages of the subform vs. the filtered form and have used both methods depending on what forms I'd already constructed (and their underlying vba)....
|
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,...
| | |
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...
|
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...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
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...
|
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...
|
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();...
| | |
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...
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
| |
