VBA coding question.... Need expert help

[Ryan]

Hi,

I have a form that pulls off of a query which references a table, the
code that I use is for various functions I have on the form i.e.
export to excel, filter, etc. On my current form it works perfectly,
everything, but when I copy and paste the entire form with vba code to
create a new form which references a new query and table (but same
form format) the code gives me an error on the export to excel
function. The only issue is on the export function, all other
functions w/ code works.

Here is the code:

Private Sub Export_Click()
On Error GoTo Err_Export_Click

Dim dbCurr As DAO.Database
Dim qdfTemp As DAO.QueryDef
Dim lngOrderBy As Long
Dim strQueryName As String
Dim strSQL As String

If MsgBox( _
"Do you want to export to Excel?", _
vbQuestion + vbYesNo, _
"Export to Excel?") _
= vbNo _
Then
Exit Sub
End If

' You only need to go to this effort if there's a filter
If Len(Me.Filter) > 0 Then
Set dbCurr = CurrentDb

' Get the SQL for the existing query
If IsNull(Me.RecordSource = "QryAdageInventoryUsage900reportsum")
Then
strSQL =
dbCurr.QueryDefs("QryAdageInventoryUsage900reportsum").SQL
Else:
If Me.RecordSource = "QryAdageInventoryUsage900reportsum" Then
strSQL =
dbCurr.QueryDefs("QryAdageInventoryUsage900reportsum").SQL
Else:
If IsNull(Me.RecordSource =
"QryAdageInventoryUsage900reportsum") Then
strSQL =
dbCurr.QueryDefs("QryAdageInventoryUsage900reportsum").SQL
Else
If Me.RecordSource =
("QryAdageInventoryUsage900reportsum") Then
strSQL =
dbCurr.QueryDefs("QryAdageInventoryUsage900reportsum").SQL
End If
End If
End If
End If


' Check whether there's an ORDER BY clause in the SQL.
' If there is, we need to put the WHERE clause in front of it.
lngOrderBy = InStr(strSQL, "ORDER BY")
If lngOrderBy > 0 Then
strSQL = Left(strSQL, lngOrderBy - 1) & _
" WHERE " & Me.Filter & " " & _
Mid(strSQL, lngOrderBy)

Else
' There's no ORDER BY in the SQL.
' Remove the semi-colon from the end, then append the WHERE clause
strSQL = Left(strSQL, InStr(strSQL, ";") - 1) & _
" WHERE " & Me.Filter
End If

' By using the current date and time, hopefully that means
' a query by that name won't already exist
strQueryName = "qryTemp" & Format(Now, "yyyymmddhhnnss")

' Create the temporary query
Set qdfTemp = dbCurr.CreateQueryDef(strQueryName, strSQL)

' Export the temporary query
DoCmd.TransferSpreadsheet transfertype:=acExport, _
spreadsheettype:=acSpreadsheetTypeExcel9, _
tableName:=strQueryName, FileName:= _
"C:\Documents and Settings\rfitz03\Desktop\RYAN'S EXCEL
SHEETS\Adage Downloaded On" & Format(Now, "mm" & "-" & "dd" & "-" &
"yyyy" & "@" & "hh" & "nn") & ".xls", _
hasfieldnames:=True

' Delete the temporary query
dbCurr.QueryDefs.Delete strQueryName

Else

DoCmd.TransferSpreadsheet transfertype:=acExport, _
spreadsheettype:=acSpreadsheetTypeExcel9, _
tableName:=strQueryName, FileName:= _
"C:\Documents and Settings\rfitz03\Desktop\RYAN'S EXCEL
SHEETS\Adage Downloaded On" & Format(Now, "mm" & "-" & "dd" & "-" &
"yyyy" & "@" & "hh" & "nn") & ".xls", _
hasfieldnames:=True

End If

Exit_Export_Click:
Set dbCurr = Nothing
Exit Sub

Err_Export_Click:
MsgBox Err.Description
Resume Exit_Export_Click

End Sub

This code which is the new form I copied, is the exact same as the
other form except the form just references the respective query names.

The error that I get is this:

Syntax Error (missing operator) in query expression
'tbladageinventoryusage900report.Date where ([Plant]) ="8111" AND
([Item])= "301308" AND ([Year])=2011)'.

What do I need to do to fix this considering the code above is the
same as the other form and it works perfectly?

Ryan

Thanks in advance!