Run Time Error 3075 After Filtering to Export to Excel

[Trevor G]

I am receiving an Error Number 3075 when I run a filter on a form from several combo boxes (each field has text entires only)and the results are filtered correctly in the subform, but when I click the Export to Excel it gives me the runtime error. and the message says Syntax Error missing operator:

My code is indicated below:

Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset
Dim ws As Excel.Application
Dim strSQL As String
Dim strParam As String
Dim blnFlag As Boolean
Dim i As Long
strSQL = "SELECT *INTO ztblSearchEngine FROM qrySearchFacility"
strParam = " WHERE "
If Not IsNull(Me.cboUWYear.Value) Then
strParam = strParam & "[Underwriting Year]='" & Me.cboUWYear.Value & "'"
blnFlag = True
End If
If Not IsNull(Me.cboBroker.Value) Then
strParam = strParam & "[Broker]='" & Me.cboBroker.Value & "'"
blnFlag = True
End If
If Not IsNull(Me.cboDomicile.Value) Then
strParam = strParam & "[Insured Domicile]='" & Me.cboDomicile.Value & "'"
blnFlag = True
End If
If Not IsNull(Me.cboInsured.Value) Then
strParam = strParam & "[Insured Name]='" & Me.cboInsured.Value & "'"
blnFlag = True
End If
If Not IsNull(Me.cboPolicy.Value) Then
strParam = strParam & "[Policy Reference]='" & Me.cboPolicy.Value & "'"
blnFlag = True
End If
If Not IsNull(Me.cboTeam.Value) Then
strParam = strParam & "[Team]='" & Me.cboTeam.Value & "'"
blnFlag = True
End If

If blnFlag = True Then
strSQL = strSQL & strParam
End If
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True
DoCmd.OutputTo acOutputTable, "ztblSearchEngine", "Excel97-Excel2003Workbook(*.xls)", CurrentProject.Path & "\Search.xls", False, "", 0, acExportQualityPrint
Dim xlapp As Excel.Application
Set xlapp = CreateObject("excel.application")
With xlapp
.Workbooks.Open CurrentProject.Path & "\search.xls"
.Columns("A:AF").Columns.AutoFit
.Visible = True

End With

 

[vbaInet]

Could you please use code tags to wrap your code so it's readable?

Like this:
[c o d e]
.... My code text in here
[/c o d e]

Without the spaces of course.

Also, when you debug what line does it highlight?

 

[Trevor G]

Hi VbaInet,

I re looked at th code and realised I missed out the IF Bln Flag statement. Code now works and result is as follows:

It did highlight the final StrSql line. But it is sorted now, thank you for taking the time to look at my thread.

If Not IsNull(Me.cboBroker.Value) Then
If blnFlag = True Then strParam = strParam & " AND "
strParam = strParam & "[Broker]='" & Me.cboBroker.Value & "'"
blnFlag = True
End If
If Not IsNull(Me.cboDomicile.Value) Then
If blnFlag = True Then strParam = strParam & " AND "
strParam = strParam & "[Insured Domicile]='" & Me.cboDomicile.Value & "'"
blnFlag = True
End If
If Not IsNull(Me.cboInsured.Value) Then
If blnFlag = True Then strParam = strParam & " AND "
strParam = strParam & "[Insured Name]='" & Me.cboInsured.Value & "'"
blnFlag = True
End If
If Not IsNull(Me.cboPolicy.Value) Then
If blnFlag = True Then strParam = strParam & " AND "
strParam = strParam & "[Policy Reference]='" & Me.cboPolicy.Value & "'"
blnFlag = True
End If
If Not IsNull(Me.cboTeam.Value) Then
If blnFlag = True Then strParam = strParam & " AND "
strParam = strParam & "[Team]='" & Me.cboTeam.Value & "'"
blnFlag = True
End If

 

[vbaInet]

Excellent! Glad you got that sorted.

Do you know about the code tags though? It puts code text like this:

This is my code

 

[DCrake]

I know it reduces the number of code lines but when using If statements it i far easier to debug if you adopt the following normal practice.

If X = Y Then

    a = b

Else

    a = d

End If

 

[Trevor G]

I am aware of them, but will look to use them more, when I have run the code previously as it was behind a command button on a form, it only highlighted the:

DoCmd.RunSQL strSQL

Hope you have a good weekend.

 

[vbaInet]

I am aware of them, but will look to use them more, when I have run the code previously as it was behind a command button on a form, it only highlighted the:



Hope you have a good weekend.

And you Trevor.

 

[Trevor G]

Hi David,

Thanks for the snippit.

Hope you also have a good weekend.