Solved

Filter records in continuous subforms based on the combination selection of combo boxes from the main form

Posted on 2008-06-11
10
219 Views
Last Modified: 2013-12-26
hi,
I have trouble filtering data in the subform. There are 4 combo boxes in the main form named "CATDATA" and the subform's name is "sfrProjectList". I have a Filter command button named cmdFilter in the main Form. Upon clicking the button, the data in the subform should be filtered based on any combination selection of values from the 4 combos. The problem is my code doesnt work at all. Perhaps could you write me a source code for the cmdFilter command button? Thanks so much.. Below are the code for cmdFilter that i used and it doesn't work. The data from the subform sftProjectList are obtain from the "ProjectList" table
Private Sub cmdFilter_Click()
    Dim strFilter As String

    If Len(Me.cboFilterStatus & vbNullString) > 0 Then
        strFilter = "ProjectList.StatusID=" & Me.cboFilterStatus.Value
    End If

    If Len(Me.cboFilterAccuracy.Value & vbNullString) > 0 Then
        If Len(strFilter) > 0 Then
            strFilter = strFilter & " AND "
        End If
        strFilter = strFilter & "ProjectList.AccuracyID='" & Me.cboFilterAccuracy.Value & "'"
    End If
   
    If Len(Me.cboFilterDepartment.Value & vbNullString) > 0 Then
      If Len(strFilter) > 0 Then
           strFilter = strFilter & " AND "
        End If
        strFilter = strFilter & "ProjectList.DepartmentID='" & Me.cboFilterDepartment.Value & "'"
    End If
   
     If Len(Me.cboFilterARPOther.Value & vbNullString) > 0 Then
        If Len(strFilter) > 0 Then
            strFilter = strFilter & " AND "
        End If
        strFilter = strFilter & "ProjectList.ARPOther='" & Me.cboFilterARPOther.Value & "'"
    End If

    If Len(strFilter) > 0 Then
       Me.sfrProjectList.Form.Filter = strFilter
        Me.sfrProjectList.Form.FilterOn = True
        Me.sfrProjectList.Form.Requery
    Else
        Me.sfrProjectList.Form.FilterOn = False
    End If
End Sub
0
Comment
Question by:Graceho
  • 6
  • 4
10 Comments
 
LVL 27

Expert Comment

by:MikeToole
ID: 21759163
Assuming that the combo boxes are Null if they have no filter value selected, the attached snippet will set up the filter string. It relies on the use of the + string concatenation operator which, unlike &, returns a Null string if either of the arguments are Null.
    Dim strFilter As String

    strFilter = strFilter & (" AND ProjectList.StatusID=" + Me.cboFilterStatus)

    strFilter = strFilter & (" AND ProjectList.AccuracyID='" + Me.cboFilterAccuracy.Value + "'")

    strFilter = strFilter & (" AND ProjectList.DepartmentID='" + Me.cboFilterDepartment.Value + "'")

    strFilter = strFilter & (" AND ProjectList.ARPOther='" + Me.cboFilterARPOther.Value + "'")

    If Len(strFilter) > 0 Then

        strFilter = Mid(strFilter, 6)

    End If

Open in new window

0
 

Author Comment

by:Graceho
ID: 21760294
I've placed the codes under the command button cmdFilter but the records in the subform sfrProjectList are not filtered. There isnt any response to the records in the subform
0
 
LVL 27

Expert Comment

by:MikeToole
ID: 21760686
I presume that you added the actual filter assignment? - I didn't code the whole routine.
Add a Debug statement for strFilter and post the result.
What are the data types for the 4 table columns?

    If Len(strFilter) > 0 Then

       strFilter = Mid(strFilter, 6)

       Me.sfrProjectList.Form.Filter = strFilter

        Me.sfrProjectList.Form.FilterOn = True

        Me.sfrProjectList.Form.Requery

    Else

        Me.sfrProjectList.Form.FilterOn = False

    End If

Debug.Print strFilter

Open in new window

0
 

Author Comment

by:Graceho
ID: 21760992
U mean for the combo boxes? the 4 combo boxes are unbound and their source are from the LookUp tables which i create for each cboFilterStatus, cboFilterAccuracy, cboFilterAccuracy and cboFilterARPOther.
For cboFilterStatus, it chooses "r" , "i", "c" or "s". ( the letters indicate the status of the projects)
For cboFilterAccuracy, it chooses "10" , 20", "30" or "50" ( The values are the percentage completion of project)
For cboFilterARPOther, it chooses "A" or "O" ( A refers to ARP Projects and O refers to Other Projects)
For cboFilterDepartment, it chooses "EAP"or  "EEE" and so on ( The fields are the department names)

The Main form here named "CATDATA" is unbound. The combo boxes are placed in this form. The subform gets its data from the ProjectList table. The fields in ProjectList table are ProjectID(Primary Key),  WBSNO(Text), Description(Text), StatusID (Number - It Looks up the status value from the Lookup table for Status) , AccuracyID ((Number - It Looks up the status value from the Lookup table for Accuracy),
ARPOther(Number - It Looks up the status value from the Lookup table for ARPOther) and DepartmentID(Number - It Looks up the status value from the Lookup table for Department)

Oh i tried editing the script with your codes but upon clicking the Filter button,  there is run-time error. The error line is at "Me.sfrProjectList.Form.Filter = strFilter". Actually i am quite new to visual basic, its my first time using it and it really give me a hard time.Thank you for your kind assistance :D Hope you understand what i am writing here
0
 
LVL 27

Expert Comment

by:MikeToole
ID: 21762266
The values you need to supply in your filter are those for the columns in ProjectListTable, not those from the lookup tables. Hence all your filter values need to be numeric. The RowSource for your comboboxes should be the related Lookup table,
E.g. Select StatusText, StatusID From StatusLookUp
Set the bound column to 2 so that the value of the combo box is the ID
Do this for all the combos where you're not displaying the actual numberic value
Construct the filter to deal with numeric values - as in the snippet - basically no '' surrounding the values
    Dim strFilter As String

    strFilter = strFilter & (" AND ProjectList.StatusID=" + Me.cboFilterStatus)

    strFilter = strFilter & (" AND ProjectList.AccuracyID=" + Me.cboFilterAccuracy.Value )

    strFilter = strFilter & (" AND ProjectList.DepartmentID=" + Me.cboFilterDepartment.Value)

    strFilter = strFilter & (" AND ProjectList.ARPOther=" + Me.cboFilterARPOther.Value)

Open in new window

0
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 

Author Comment

by:Graceho
ID: 21765411
Yea the values of the columns to be filtered in the ProjectList are from the LookUp tables. The row source for each of my combos are, eg. SELECT [LookupStatus].[StatusID], [LookupStatus].[Status] FROM LookupStatus; I have set my bound column to 2. But There will still be a prompt box asking to enter the parameter value and the records in the Subform are not filtered. I duno what's happening.
I also tried using the following code  in the snippet below but it gives me an error,

  I attach a copy of my database ... the cmdFilter button is located in the CapexDataForm. Hope you can help me edit it .. THanks
  Dim strARPOther As String

     Dim strStatus As String

    Dim strDepartment As String

     Dim strAccuracy As String

  

    strStatus = IIf(cboFilterStatus.Value = "" Or IsNull(cboFilterStatus.Value), _

                    "(ProjectList.StatusID IS NULL OR ProjectList.StatusID IS NOT NULL)", _

                    "ProjectList.Status='" & cboFilterStatus.Value & "'")
 

  

    strDepartment = IIf(cboFilterDepartment.Value = "" Or IsNull(cboFilterDepartment.Value), _

                    "(ProjectList.DepartmentID  IS NULL OR ProjectProjectList.DepartmentID IS NOT NULL)", _

                    "ProjectListDepartmentID='" & cboFilterDepartment.Value & "'")

    
 

    strARPOther = IIf(cboFilterARPOther.Value = "" Or IsNull(cboFilterARPOther.Value), _

                    "(ProjectList.ARPOther IS NULL OR ProjectList.ARPOther  IS NOT NULL)", _

                    "ProjectList.StatusID ='" & cboFilterLeader.Value & "'")
 
 

    strAccuracy = IIf(cboFilterAccuracy.Value = "" Or IsNull(cboFilterAccuracy.Value), _

                "(ProjectList.AccuracyID  IS NULL OR ProjectList. AccuracyID IS NOT NULL)", _

                "ProjectList.AccuracyID ='" & cboFilterAccuracy.Value & "'")
 

    With sfrProjectList.Form

    

        .RecordSource = "SELECT ProjectList.ProjectID,  ProjectList.WBSNO, ProjectList.Description FROM ProjectListt " _

                      & "WHERE " & strARPOther _

                      & " AND " & strStatus _

                      & " AND " & strDepartment _

                      & " AND " & strAccuracy _

                      & " ORDER BY ProjectID;"

        .Requery

    

    End With

Open in new window

masterlist.zip
0
 
LVL 27

Accepted Solution

by:
MikeToole earned 250 total points
ID: 21767865
<SELECT [LookupStatus].[StatusID], [LookupStatus].[Status] FROM LookupStatus; I have set my bound column to 2>
The numeric ID needs to be the bound column, that's what is wanted to filter your subform's recordsource, in my post I showed the two fields the other way round:
<Select StatusText, StatusID From StatusLookUp >
It doesn't matter much which way round you put it so long as you set the bound column to point to the correct field - the numerid ID

Your code produces the following as recordsource:
SELECT ProjectID, WBSNO, Description FROM ProjectList WHERE ProjectList.ARPOther ='A' AND ProjectList.Status='r' AND ProjectListDepartmentID='EQI' AND ProjectList.AccuracyID ='30' ORDER BY ProjectID;

The reason that Access prompts for ProjectList.Status is that there is no such field in the ProjectList table. It should be StatusID.
As I already pointed out, the fields you want to filter on are all defined as Numeric in ProjectList and can't be selected on string values.

So,
1) Make sure that the bound column of the combos is the numeric ID
2) Check the column names in the where clause of your SQL are right
3) Remove the single quotes around the values you are adding to the Where clause - they are only needed for text values
4) Put a debug.print statement after assigning the recordsource so that you can see the SQL statement you're asking Access to use:
     debug.print .RecordSource
0
 

Author Comment

by:Graceho
ID: 21775020
Thanks my cmdFilter is working :D  it filters everything nicely except for the cboFilterARPOther for ARPOther, Either choosing A or O will give the whole list of the ProjectList. I have checked its combo box properties. I don't know where the problem arises. Thanks so much for your attention. :D :D :D
0
 

Author Comment

by:Graceho
ID: 21775156
Oh Its when i Select "A" form the combo, all data are listed whether they are A or O, but when i select "O", nothing appears.
0
 

Author Comment

by:Graceho
ID: 21775269
i managed to get it work already. Thanks for your assistance :D
0

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Have you ever wanted to restrict the users input in a textbox to numbers, and while doing that make sure that they can't 'cheat' by pasting in non-numeric text? Of course you can do that with code you write yourself but it's tedious and error-prone …
Article by: Martin
Here are a few simple, working, games that you can use as-is or as the basis for your own games. Tic-Tac-Toe This is one of the simplest of all games.   The game allows for a choice of who goes first and keeps track of the number of wins for…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now