Export to Excel, empty recordset (1 Viewer)

[antifashionpimp]

Hello,

I have a form that performs a search, according to criteria that a user enters into the text boxes.

When the user clicks on „Search“, a SQL string (say strSQL) is built up with the criteria. Then a list box RowSource property is set to this strSQL, to display the results of the search.
StrSQL is a global variable within this Form’s code module, i.e. in the class module.
This all works fine so far.

I then have a button „Export…“, that exports the results displayed in the listbox to an Excel spreadsheet using automation. It also uses strSQL to build a recordset, and then uses this recordset to use the CopyFromRecordset method of the Excel object.

This all works fine, until I have the user filling out a specific criteria. The problem SQL is as follows:

SELECT A.VPNummer, A.Nachname, A.Vorname, A.Abteilung, A.Telefon, A.Sex AS Geschlecht, A.VFG, A.Körperhöhe, A.StammlProp FROM qryAlleVPmEndwerte A
WHERE (A.Sex) = 'm' AND (A.Körperhöhe) >= 1750 AND (A.Körperhöhe) <= 1850 AND (A.StammlProp) Like '*MM*' AND (A.VFG) = True
ORDER BY A.VPNummer;

Only when I have “(A.StammlProp) Like '*MM*' “ as part of the SQL syntax, the following problem occurs.

This displays correctly in the listbox, but when exporting to Excel, there is a blank page, only the headings appear. I use the following code to create a recordset for Excel, and this only seems to create an empty recordset:

Function CreateRecordset(rstD As ADODB.Recordset, _
    rstC As ADODB.Recordset, _
    strSQL As String, _
    strListName As String)
    On Error GoTo CreateRecordset_Err
    'Create recordset that contains count of records in query
    rstC.Open strSQL
    'test
    Debug.Print strSQL
    'If more than 500 records in query result, return false
    'Otherwise, create recordset from query
    'CAUSE RUN_TIME ERROR
    '--------------------
    'If rstCount!NumRecords > 500 Then
        'CreateRecordset = False
    '--------------------
    If rstC.RecordCount > 500 Then
        CreateRecordset = False
    
    Else
        rstD.Open strSQL
        CreateRecordset = True
    End If
    
CreateRecordset_Exit:
    Set rstC = Nothing
    Exit Function
    
CreateRecordset_Err:
    MsgBox "Fehler # " & Err.Number & ": " & Err.Description
    Resume CreateRecordset_Exit
End Function

Note: rstD and rstC are empty recordsets when passed to this function
When I execute line 9 of the above, I get runtime error 3021 „No current record blablabla“.
What this tells me is that this is an empty recordset, but I don’t understand why? When copying this strSQL code into a query, it works fine, and also the search is fine, but it doesn’t create a recordset in the above function.

Any ideas are very welcome. Thanks in advance.

Regards,

Jean