Filtering based on multiple selection

[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.

 

[Pat Hartman]

Adopting a meaningful naming scheme will help you. In my databases a field ending in ID is always an autonumber or FK Integer pointing to an autonumber. A NUM is always a numeric code, usually from another application but sometimes generated internally by adding 1 to the previous maximum value. Examples would be InvoiceNum or OrderNum generated internally or CustomerNum which probably comes from another application. And finally CD is a code. Usually it is text abbreviation but sometimes it is numeric.

 

[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.

 

[sparks80]

Hi,

I have made some simple modifications to the export code which will prevent the application from displaying until the data has transferred. It should also execute faster as it is not updating the screen with each iteration.

Do you have students with multiple languages, or only 1?

Private Sub cmdExport_Click()
    Dim xlapp As Excel.Application
    Dim xlbook As Excel.Workbook
    Dim xlsheet As Excel.Worksheet
    Dim rst As DAO.Recordset
    Dim lngRow As Long
    
    On Error GoTo ErrorcmdExport_Click
    
    Set rst = Me.sfmStudentClasses.Form.RecordsetClone
    
    If rst.EOF Then
        MsgBox "No classes have been selected", vbInformation, "No Classes Selected"
    Else
        Set xlapp = New Excel.Application
        Set xlbook = xlapp.Workbooks.Add
        Set xlsheet = xlbook.Worksheets(1)
        
        ' Get header names
        ' Do not export last field because this is an ID
        For I = 0 To rst.Fields.Count - 2
            xlsheet.Cells(1, I + 1).Value = rst.Fields(I).Name
        Next
        
        lngRow = 2
        
        Do While Not rst.EOF
            ' Do not export last field because this is an ID
            For I = 0 To rst.Fields.Count - 2
                xlsheet.Cells(lngRow, I + 1).Value = rst.Fields(I).Value
            Next
            lngRow = lngRow + 1
            rst.MoveNext
        Loop
    End If
    
ExitcmdExport_Click:
    xlapp.Visible = True
    xlapp.ScreenUpdating = True
    Set xlsheet = Nothing
    Set xlbook = Nothing
    Set xlapp = Nothing
    Set rst = Nothing
    
    Exit Sub
        
ErrorcmdExport_Click:
    MsgBox "Error number: " & err.Number & vbCrLf & _
        "Error description: " & err.Description
    GoTo ExitcmdExport_Click
End Sub

 

[brichard]

Only one, it's their primary language.

Thank you for the modified export code, implementing it now.

 

[sparks80]

Hi,

You shouldn't need another listbox if I have understood correctly.

Just add another field in your main student table called PrimaryLangID (type - number, long integer). Now modify the relationships for the database and link tblLanguage.LangID to tblStudents.PrimaryLangID.

Now modify the record source for the subform by adding tblLanguage (linked up as described above), and add the field 'Language'. You will need to include the field before ClassID or it will not be exported correctly.

Now display this field on the subform if you wish to view the language on the form.

 

[brichard]

Sorry, I wasn't clear enough.

The context of the mail merge is related to the following:
-class of the student
-primary language of the student
-financial category of the student

Need to filter the database to only get the students
-attending certain class/es (done)
-their primary language is *
-not included in an other list (irrelevant payment category)

That's why I need to
-filter for classes (done)
-filter for language
-exclude everyone who is in an other list (unfortunately not possible to use tick boxes in the student table due to the design and sources of payment data)

Export this list and email the affected students with a tailored email. (done)

Regards

 

[brichard]

I have changed my IDs to numbers instead of text as of your recommendation (50+ relationships).

Now my queries are leaping up with a touch, like frogs in a dynamite pond.

 

[sparks80]

Hi,

I understand.

You can add another listbox to the form with each language option based on your languages table.

As an example the selection criteria for your subform will now need to be something like this:

(classID=2 OR classID=3 OR classID=6) AND (languageID=1 or languageID=2)

The code to dynamically create the filter for selecting the class is already in place. If no classes are selected then the filter will default to classID=0, in other words no records are returned.

Next the code needs to add the " AND " in the middle:

strFilter = strFilter & " AND "

Next the code needs to copy the loop for the class listbox to create the filter definition for the language.

I will have a look when I am next at home for you.

The ability to dynamically create a filter string like this is very useful at times, but is difficult to explain!

 

[sparks80]

Hi,

See example attached