Filter a Continuous form

Question

Hello all.

I have a continuous form which lists all the up and coming courses that we are running so I can book people on whichever course they need. However, there are a lot and I would like to be able to filter this down by 2 seperate filters; course name and month. The course name is taken directly from a table so this can be referred to in the code. The month filter will need to refer to date fields (set up as date format) which are manually entered and show the full date of the course.

I want to be able to filter these options by 2 seperate combo boxes which I can put in the Form Header. What I don't know is how to create and enter the code that will filter what is needed.

Can anyone help me with this?

Thanks all,

Alastair

Answer 1

First, Just because you have already set up something does not mean you should continue with poor design. And I'm sorry but that's what you have. It would not be difficult to use APPEND queries to move your data into a more normalized design. 

Second, I see another design flaw; "Course_Name in the register form is the FK". Foreign keys will normally be the PK of the related table. Using Course_Name as a FK is wrong. First it is a waste of space, second text data types don't perform as well as numeric types in joins. 

That's also why your filter may not be working. If Course_Name is the FK, then doing a filter on Course_ID won't do anything. Is Course_ID even in the Register table? And how is Course_ID being populated if you are filtering for the selection in cboCourse? The code won't **** anything but filter for the currently selected record. What is the RowSource, Bound Column and ControlSource of cboCourse? Can you show the SQL of your query?

Answer 2

You are assigning the expression to the strFilter variable, but not actually applying that variable as the filter criterion.  You also seem to be using the name of the Course_ID column rather than of the unbound combo box control cboCourse in the form header.  Try this:

Dim strFilter As String

If Not IsNull(Me.cboCourse) Then

     strFilter = "Course_ID = " & Me.cboCourse

     Me.Filter = strFilter

     Me.FilterOn = True

Else

    Me.FilterOn = False

End If

If a value has been selected in cboCourse the form should be filtered to the selected courses; if the combo box has been cleared to Null the filter will be turned off and all rows returned.

The unbound combo box should be set up along these lines:

RowSource:     SELECT Course_ID, Course_Name FROM Course_Names ORDER BY Course_Name;

BoundColumn:   1

ColumnCount:    2

ColumnWidths:  0cm

where Course_ID and Course_Name are columns (the former being the key) in a referenced table Course_Names which has one row per course.  If your units of measurement are imperial rather than metric Access will automatically convert the unit of the last one to inches.  The important thing is that the dimension is zero to hide the first column.

Answer 3

Thanks Scott for your input and thanks Ken for the Caveat. As my coding experience is very limited and as I had already set up tables which include the course dates, I have decided to scrap that part of the project because I would get myself into all sorts of mess!! Thank you very much for that information anyway. It should come in use in a later project I have planned!

I have, however, decided to continue withe the filter for the course and this is what I have. I already have a table with Course_Name and Course_ID (PK) set up. This table is related to the main register form and Course_Name in the register form is the FK.

I have followed what you have suggested and have come up with the following code.

Private Sub ApplyFilter_Click()

Dim strFilter As String

strFilter = ""

If Not IsNull(Me.cboCourse) Then

     strFilter = "Course_ID = " & Me.Course_ID

DoCmd.RunCommand acCmdApplyFilterSort

End If

End Sub

However, when I activate the filter button, either nothing happens or there is a problem with the strFilter line. I have doublechecked the names of the fields and ensured they are referred to within the form but to no avail.

For additional information, the form is based on a query with the link from CourseNameID (Main Register table) to the Course_ID (Register Sub table) with a query join property #2 selected so all records are shown and Course_ID (Course name table) to the Course_Name (Main Register table). The reason for the query is so that the form only shows those courses that have not already happened.

Is there anything that I have done wrong which needs amending?

Thanks

Alastair

Answer 4

One caveat:  if you have rows in the form's recordset for more than one year you will need to include the year in the expression or rows for the same month in different years will be returned.

Answer 5

First, you should have at least 2 tables here:

tblCourse

CourseID (PK)

CourseTitle

tblCourseDates

CourseDateID (PK)

CourseID (FK)

CourseDate

Your form should be bound to tblCourseDates It should have a combobox to select CourseID and a textbox for the date.

In the header of the form you have 2 comboboxes for Course and month. The Rowsource for the Course combo should be:

SELECT CourseID, CourseTitle FROM tblCourse ORDEr by CourseTitle;

For the month, just have a listbox that displays 1-12.

Add 2 buttons cmdFilter and cmdClear. The code behind cmDfilter would look like this:

Dim strFilter As String

strFilter = ""

If NOT IsNull(me.cboCourse) Then 

     strFilter = "CourseID = " & Me.CourseID

End If

If strfilter <> "" Then

    If NOT IsNull(Me.cboMonth) Then

        strFilter = strFilter & " AND Month(CourseDate) = " & Me.cboMonth

    End If

Else

    strFilter = "Month(CourseDate) = " & Me.cboMonth

End If

DoCmd.RunCommand acCmdApplyFilterSort

For the Clear button:

Me.cboCourse = Null

Me.cboMonth = Null

This should all you to filter the form for either course, month or both.

Disclaimer, This is air code off the top of my head, but should give you enough to work it out.