Filtering based on multiple selection

[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