B
bavjean
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
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