Filtering data in Form

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.

Answer 1

Hi, Have you considered using a datasheet form? Just curious...

Answer 2

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.

Answer 3

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.

Answer 4

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

Answer 5

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!

Answer 6

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!

Answer 7

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. 

Answer 8

Thank you, Alexander.