none
Filtering data in Form RRS feed

  • Question

  • Hello guys.

    I am quite new to access and I need some help.

    I have the following table:

    MatchID (Primary Key) Game_Date ID Team OID Opponent Venue Competition CompetitionID Analyst_Home_Team Analyst_Away_Team Post-Matcher Checker Second_Checker Collection_Manager
    861390 26.08.16 156 FC Bayern München 171 SV Werder Bremen Allianz Arena German Bundesliga 22            
    888193 29.09.16 125 Fiorentina 3107 FK Qarabag Artemio Franchi UEFA Europa League 6            
    904847 30.04.17 1156 Shimizu S-Pulse 5512 Vegalta Sendai IAI Stadium Nihondaira Japanese J-League 20            

    I am now trying to create a Form to input the data in the empty columns but I would like to filter the data by Competition and Date before the input. When using the record find combo box I can't manage to get the Combo Box to show unique values for these two categories, as they are linked to the Primary Key.

    I created now two unbound combo boxes and I linked them but I can't get them to both filter the data:

    Private Sub CboComp_AfterUpdate()
       Me.CboDate.Requery
    End Sub
    Private Sub CboDate_AfterUpdate()
       If IsNull(Me.CboDate) Then
          Me.FilterOn = False
       Else
          Me.Filter = "[Competition] = "Me.CboComp" AND [GameDate] = "me.CboDate
          Me.FilterOn = True
       End If
    End Sub
    

    What am I not doing correct?

    Thanks in advance for the help.

    Radu.        
    Tuesday, July 4, 2017 10:23 AM

Answers

  • The values of the controls should be concatenated into the string expression.  As the Competition column is of text data type, its value needs to be delimited with literal quotes characters, each of which is represented in the expression by a pair of contiguous quotes characters.  Assuming that the GameDate column is of date/time data type, its value needs to be delimited with the # character.  A date literal must be in US date format (mm/dd/yyyy) or an otherwise internationally unambiguous format, however.  In the code below it is formatted in the ISO standard for date notation of YYYY-MM-DD:

    Private Sub CboDate_AfterUpdate()

       If IsNull(Me.CboDate) Then
          Me.FilterOn = False
       Else
          Me.Filter = "[Competition] = """ & Me.CboComp & """ AND " & _
              "[GameDate] = #" & Format(Me.CboDate,"yyyy-mm-dd") & "#"
          Me.FilterOn = True
       End If

    End Sub

    However, I assume that the ID and OID columns are foreign keys referencing a Teams table, and the CompetitionID column is a foreign key referencing a Competitions table, in which case you do not need the text Team, Opponent or Competition columns.  More importantly the table should not contain the these text columns as they are each transitively dependant on the key, and the table is consequently not normalized to Third Normal Form (3NF), leaving it open to update anomalies.

    The team and competition names can be shown in combo boxes bound to the foreign key value by hiding the bound first column.  In the case of the team combo box for instance, the control would be set up as follows:

    ControlSource:    ID

    RowSource:     SELECT ID, Team FROM Teams ORDER BY Team;

    BoundColumn:   1
    ColumnCount:    2
    ColumnWidths:  0cm

    The opponent and competition bound combo boxes would be set up similarly.  The unbound cboComp  combo box would be set up in the same way.

    It follows from the above that the form would now need to be filtered on the numeric CompetitionID column rather than the text column, so the code would now be:

    Private Sub CboDate_AfterUpdate()

       If IsNull(Me.CboDate) Then
          Me.FilterOn = False
       Else
          Me.Filter = "[CompetitionID] = " & Me.CboComp & " AND " & _
              "[GameDate] = #" & Format(Me.CboDate,"yyyy-mm-dd") & "#"
          Me.FilterOn = True
       End If

    End Sub

    As the CompetitionID column is a number data type the value no longer needs to be delimited with literal quotes characters.

    Ken Sheridan, Stafford, England




    Tuesday, July 4, 2017 4:57 PM

All replies

  • Hi, Have you considered using a datasheet form? Just curious...
    Tuesday, July 4, 2017 1:28 PM
  • Hello.

    No, I haven't considered that to be honest.

    I checked this now and it looks similar to a Query isn't it? That wouldn't be so fast to use for others, they would have to make manual filters wouldn't they?

    I was thinking something like a continuous form which would be populated with the data matching the competition and date filter.

    Tuesday, July 4, 2017 2:30 PM
  • Hi,

    I was thinking a datasheet gives the user more flexibility for filtering. Using a form and a couple of combos limit the user to only two options. Besides selecting items from the two combos will take two actions from the user. Using a datasheet will take as much actions for the same set of filters, so you're not really saving any steps by not using a datasheet.

    However, if you really want to use a form, we'll need to see what it looks like to be able to help you.

    Tuesday, July 4, 2017 2:46 PM
  • The values of the controls should be concatenated into the string expression.  As the Competition column is of text data type, its value needs to be delimited with literal quotes characters, each of which is represented in the expression by a pair of contiguous quotes characters.  Assuming that the GameDate column is of date/time data type, its value needs to be delimited with the # character.  A date literal must be in US date format (mm/dd/yyyy) or an otherwise internationally unambiguous format, however.  In the code below it is formatted in the ISO standard for date notation of YYYY-MM-DD:

    Private Sub CboDate_AfterUpdate()

       If IsNull(Me.CboDate) Then
          Me.FilterOn = False
       Else
          Me.Filter = "[Competition] = """ & Me.CboComp & """ AND " & _
              "[GameDate] = #" & Format(Me.CboDate,"yyyy-mm-dd") & "#"
          Me.FilterOn = True
       End If

    End Sub

    However, I assume that the ID and OID columns are foreign keys referencing a Teams table, and the CompetitionID column is a foreign key referencing a Competitions table, in which case you do not need the text Team, Opponent or Competition columns.  More importantly the table should not contain the these text columns as they are each transitively dependant on the key, and the table is consequently not normalized to Third Normal Form (3NF), leaving it open to update anomalies.

    The team and competition names can be shown in combo boxes bound to the foreign key value by hiding the bound first column.  In the case of the team combo box for instance, the control would be set up as follows:

    ControlSource:    ID

    RowSource:     SELECT ID, Team FROM Teams ORDER BY Team;

    BoundColumn:   1
    ColumnCount:    2
    ColumnWidths:  0cm

    The opponent and competition bound combo boxes would be set up similarly.  The unbound cboComp  combo box would be set up in the same way.

    It follows from the above that the form would now need to be filtered on the numeric CompetitionID column rather than the text column, so the code would now be:

    Private Sub CboDate_AfterUpdate()

       If IsNull(Me.CboDate) Then
          Me.FilterOn = False
       Else
          Me.Filter = "[CompetitionID] = " & Me.CboComp & " AND " & _
              "[GameDate] = #" & Format(Me.CboDate,"yyyy-mm-dd") & "#"
          Me.FilterOn = True
       End If

    End Sub

    As the CompetitionID column is a number data type the value no longer needs to be delimited with literal quotes characters.

    Ken Sheridan, Stafford, England




    Tuesday, July 4, 2017 4:57 PM
  • Hey ultrasradu,

    I too am an Access noobie, but I got myself into building a very complex database and I was at the same roadblock you are now in. I found it very helpful and easy to navigate data by creating a query on the table you want to filter, then creating a form and linking the query with a listbox. You will be able to browse all of your data readily and also create filters through your query or simply writing code to filter data through your listbox in dynamically. If you want some code examples I can give you some.

    Also, you can create a query and make the filters there then place a subform on a blank form with the query in it as I believe others have suggested. Otherwise, if you want to send me a copy of your database I can take a look and better help you.

    Best of luck!



    • Edited by InnVis Tuesday, July 4, 2017 10:12 PM
    Tuesday, July 4, 2017 10:07 PM
  • Thanks, Ken.

    This worked perfect:

    Private Sub CboDate_AfterUpdate()

       If IsNull(Me.CboDate) Then
          Me.FilterOn = False
       Else
          Me.Filter = "[Competition] = """ & Me.CboComp & """ AND " & _
              "[GameDate] = #" & Format(Me.CboDate,"yyyy-mm-dd") & "#"
          Me.FilterOn = True
       End If

    End Sub

    Thanks again!

    Thursday, July 6, 2017 8:07 PM
  • Thank you for the help.

    I managed it for the moment, thanks to Ken. I couldn't add anyway any photos for the moment, my account is not confirmed yet. 

    Thursday, July 6, 2017 8:11 PM
  • Thank you, Alexander.
    Thursday, July 6, 2017 8:11 PM