Filtering based on multiple selection

[brichard]

Hello

Could you please help me with the following issue?

Please see a simplified version of my database below. (The tables are containing hundreds of records.)

STUDENTS
(record1)
id - 1
name - student1
course1 - 101
course2 - 102
course3 - 106
course4 - 109
course5 - (blank)

(record2)
id - 2
name - student2
course1 - 102
course2 - 106
course3 - (blank)
course4 - (blank)
course5 - (blank)

COURSES
101 - editing
102 - acting
103 - directing
104 - producing
105 - screenwriting
106 - documentary
107 - effects
108 - drawing
109 - relaxing


I would like to create a form, what I could use to produce a query showing all students who are attending the selected courses.
For example I would like to see all students attending the "acting" course.
I am selecting "acting" on the form, running the query from a button and getting both records.

The difficulty is that the courses are in different columns (5 columns in total) in the STUDENTS table and I would like to select multiple COURSES and get all STUDENTS attending any of the selected courses.

My idea was to create a form with checkboxes and create a filter based them, but I am lost.

(Please let me know if I have a design error, I can re-design the tables.)

Your help is much appreciated.

 

[brichard]

Thank you Pat

I knew something was wrong, thank you for pointing me in the right direction.

The tables look like this now:

tblStudents
StudentID (autonumber, primary key)
FirstName
LastName
tblClasses
tblClassID (autonumber, primary key)
ClassNum
ClassName
tblStudentClasses
StudentClassID (autonumber, primary key)
StudentID (long integer, foreign key to tblStudents)
ClassID (Long integer, foreign key to tblClasses)

Could you or anyone help me further?

 

[sparks80]

Hi,

Firstly you need to set up the relationships between those tables. Go into the relationship editor and join:
tblStudents.StudentID to tblStudentClasses.StudentID
tblClasses.ClassID to tblStudentClasses.ClassID

Now go to the properties for each of the relationships and check the option "Enforce Relational Integrity"

From your description I would probably use an unbound main form with a drop-down box listing the classes, and a subform based on a query using tblStudents and tblStudentClasses.

Create a new form, say called frmStudentClasses and don't change the record source. Add a combobox called cboClasses. Change the number of columns to 3, the first column width to 0 and the bound column to 1. Set the row source for the combobox to tblClasses (note that the ClassID field needs to be in the first column, otherwise you will need to change the bound column and column widths to hide the ID).

Now create a subform, say called sfmStudentClasses, listing the students in the selected class, based on the following query:

SELECT *
FROM tblStudents INNER JOIN tblStudentClasses ON tblStudents.StudentID = tblStudentClasses.StudentID WHERE (((tblStudentClasses.ClassID)=[Forms]![frmStudentClasses]![cboClasses]));;

Now insert the subform onto the main form.

Lastly you need some code to update the subform when the combobox selection changes. This code can go in the AfterUpdate event of the ComboBox.

Private Sub cboClasses_AfterUpdate()
  Me!sfmStudentClasses.Requery
End Sub

 

[brichard]

Hello Sparks80

Thank you for your prompt response.

Can I make multiple selections in a drop-down box?

I would like to be able to select multiple courses from the available 70, need to have an option to select 0 to all courses and get all students attending any of the selected courses.

I will save the selection in excel and use it to send a mail merge to the students about the changes in their courses.

 

[sparks80]

Hi,

Sorry I hadn't appreciated the need to select multiple courses at once.

Instead of using a combobox you could use a listbox, which allows multiple selections. I will write some code for you that will set the row source of the subform accordingly.

 

[sparks80]

OK, if you set the subform record to the following by default, this will load the subform without errors, and no records selected:

SELECT *
FROM tblStudents INNER JOIN tblStudentClasses ON tblStudents.StudentID = tblStudentClasses.StudentID WHERE (((tblStudentClasses.ClassID)=0));;

Instead of using a combobox use a listbox called "lstClasses" and set the multi-select option to true (it's on the "Other" tab I think). Now add this code to the listbox AfterUpdate event.

Private Sub lstClasses_AfterUpdate()
    Dim I As Integer
    Dim strSQL As String
    
    strSQL = "SELECT * FROM tblStudentClasses INNER JOIN tblStudents ON " & _
        "tblStudentClasses.StudentID = tblStudents.StudentID WHERE "
    
    With Me!lstClasses
        For I = 0 To .ListCount - 1
            If .Selected(I) Then
                If Len(strSQL) > 116 Then
                    strSQL = strSQL & " OR "
                End If
                strSQL = strSQL & "tblStudentClasses.ClassID=" & .Column(0, I)
            End If
        Next
    End With
    Me!sfmStudentClasses.RecordSource = strSQL
End Sub

Hopefully you will now see a list of all students/classes for your selection in the listbox.

 

[brichard]

It's a bit complicated for me, so I have created this template database (attached) and once I do understand how is it working, I will move on to the main one.

Where should I insert this?
SELECT *
FROM tblStudents INNER JOIN tblStudentClasses ON tblStudents.StudentID = tblStudentClasses.StudentID WHERE (((tblStudentClasses.ClassID)=0));;

Thank you

 

[sparks80]

Hi,

I'll have a look at this in the evening (UK) as I only have Access 2007 at home

 

[sparks80]

Hi,

Pat is correct, you can view all the records by using a main form based on classes, and a subform based on StudentClasses and Students.

The disadvantage of this set-up is that you can only view records for one class at a time.

I have modified the sample database to show you my method of doing this, and see which you prefer.

 

[brichard]

This is amazing.

My brain is too flat for today, will try to implement it tomorrow morning and get back to you.

 

[sparks80]

Hi Pat,

Yes, if you look at post #7 it reads "I would like to be able to select multiple courses from the available 70". I hadn't appreciated this at first which is why I initially suggested a ComboBox, rather than ListBox.

Good advice about the clear button.

It is also worth using a command button to update the subform, so that the user can make the selection, and then update the form, rather than after each individual list item.

I've made the changes to the sample database, including extra buttons for "Select All" and "Invert Selection". Have a look at the updated version attached.

 

[brichard]

Thank you for helping me with this.

All of your versions are great, but for my task the one in #11 is the most suitable.

I really don't want to abuse your time, but moving forward I am facing even more issues and would be grateful if you could have a look at the following:

Some students have multiple email addresses. Need to send it to all of their addresses to make sure they are receiving it.
I have created a new table with the emails.
sfmStudentClasses should show all records per email address (multiple names).
Instead of the emails I am receiving #Name?

I also have an exclusion list with students not affected by these mailings (they are in another payment package).
Would like to exclude these students from the mail merge.

As last step I would like to export sfmStudentClasses to xlsx but getting an empty spreadsheet.
Is this related to some "after update"?

 

[sparks80]

That's ok, I have started helping on this forum because I quite enjoy Access, and it is a good way to learn myself!

I'll take a look tonight.

It shouldn't be too hard to select the data you want to export, both for multiple email addresses, and students that don't need to be included.

I have a fair amount of experience of getting Access and Excel to communicate, so that shouldn't be a problem. Can you remind me what version of Access and Excel you have.

Thanks

 

[brichard]

Thank you
Office 2010, everything

 

[sparks80]

Hi,

I have removed the Exclusions table from the database. There is potentially a single exclusion for each student, therefore you would have a 1:1 relationship between your students table and the exclusions table.

I think this will potentially make the application slower, particularly when there are large numbers of records.

The solution is to include the exclusion information in your students table. I have added a yes/no field to the students table called ExcludeStudent. The query for the subform will only display records where the value of ExcludeStudent is False.

I have written some VBA code behind the Export button that will open Excel and export the data into a new workbook.

Because I have Access and Excel 2007 you will find that the reference for the Excel library is missing. To fix this press ALT+F11 to open the VBA editor. Go to the References option on the Tools menu. Scroll down the list and check the box next to "Microsoft Excel x.0 Object Library". I think for Office 2010 the version number is 14.0.

Finally I have changed the code to use filters rather than modify the code of the subform. Hopefully the subform recordset will make more sense to you this way.

 

[brichard]

In my main database I got the following error when trying to select items:
"Runtime error '3464' Data Type mismatch in criteria expression"
for the line:
.Form.FilterOn = True

I suspect this is because my IDs are texts, no numbers.
Is there a way around or do I need to change them to numbers?

Thank you

 

[sparks80]

Hi,

When you say "my IDs are text" are you referring to the primary key of the table?
As a general design principle it is best to keep the primary key of the table as a autonumber field, that will uniquely identify each record.

When you link to another table (say between students and studentclasses using the StudentID) this is most efficient when linking a number. If you link using a text field Access will be slower linking the two tables together. Not a problem for small numbers of records, but for larger databases this will become significant.

In the example form I helped modify the line which generates the criteria for the subform is this:

strFilter = strFilter & "tblClasses.ClassID=" & .Column(0, I)

If your class ID is a text string, and assuming the classID is in the first column of the listbox, then you would need to change this to:

strFilter = strFilter & "tblClasses.ClassID=[COLOR=Red]'[/COLOR]" & .Column(0, I) & "[COLOR=Red]'[/COLOR]"

To enclose a text string in your query definition you need to enclose it in single or double quotation marks. I have highlighted the single quotation marks above in red so you can see.

To summarise the best fix would be to change the ID from text to number, but as a temporary fix using quotation marks in the SQL code should work.

 

[brichard]

It works!
But if de-selecting everything to zero selected value, I have the same error message for
.Form.Filter = strFilter

Thank you for both of you for the advice, I will consider a new design.

I am wondering if I can add an other listbox following the same procedure or they would counteract in some way.

 

[sparks80]

Hi,

You have to do the same with the code that is used when there are no records selected. Here is how the code should be changed:

Else
  strFilter = "tblClasses.ClassID=''"
End If

What is the other listbox you need?
You can use two listboxes to select records on the subform, but the criteria will need to be modified to incorporate the second listbox, and the subform will need to be updated on the AfterUpdate event of both.

It is also worth bearing in mind Pat's advice from before. The disadvantage of using SQL code from VBA is it can cause the database to bloat. Therefore it is worth compacting the database regularly to ensure the file size remains reasonable!

If you need any more help then let me know.

 

[brichard]

Now it's perfect, thanks a lot!

I have an other table
tblLanguage
LangID - 1, 2, 3
Language - English, French, German

No other values, only this three.

LangID is a foreign key to tblStudents

Would like to add this same way as we did the classes.

Also one question about the export to excel.
I have a quiet fast desktop (8 gig ram, 3.1GHz 4 core, ssd) but when exporting, it seems that it takes 7-10 seconds to create a sheet of ~500. I can see the open sheet filling up with lines.
The only issue with this, that may I start working with it before exporting been completed.
Could this be related to my design or this is normal when exporting?
Is there a way to run it minimized and display a msg box once finished or similar to avoid any data loss in excel?
Not a major issue but if there is an easy solution it would be great.