VBA code issue...Would love 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

This is a post in another forum, sorry for that. Thanks in advance!

 

[John W. Vinson]

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?

I'm not sure why it's working in one place and not the other, but do be aware
that DATE and YEAR are reserved words (for the builtin date/time functions),
and that Access can get confused. Consider using
'tbladageinventoryusage900report.[Date] with the square brackets, or (better)
renamimg both fields.
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com

 

[Ryan]

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?

I'm not sure why it's working in one place and not the other, but do be aware
that DATE and YEAR are reserved words (for the builtin date/time functions),
and that Access can get confused. Consider using
'tbladageinventoryusage900report.[Date] with the square brackets, or (better)
renamimg both fields.
--

             John W. Vinson [MVP]
 Microsoft's replacements for these newsgroups:
 http://social.msdn.microsoft.com/Forums/en-US/accessdev/
 http://social.answers.microsoft.com/Forums/en-US/addbuz/
 and see alsohttp://www.utteraccess.com

If I change those and I get the same error, then what could it be?
Thanks for the reply.

 

[John W. Vinson]

If I change those and I get the same error, then what could it be?
Thanks for the reply.

I'm finding the code confusing. In particular, a couple of places you have
lines like

If IsNull(Me.RecordSource = "QryAdageInventoryUsage900reportsum")

The IsNull() function returns TRUE if its argument is NULL, False otherwise.
But the expression

Me.RecordSource = "QryAdageInventoryUsage900reportsum"

can never be null - it's either true or it's false. What's the purpose of this
statement?

All I can suggest is that you set a breakpoint (mouseclick in the grey bar to
the left of an executable statement) and see what strSQL is actually being set
to.

--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com

 

[Ryan]

I'm finding the code confusing. In particular, a couple of places you have
lines like

    If IsNull(Me.RecordSource = "QryAdageInventoryUsage900reportsum")

The IsNull() function returns TRUE if its argument is NULL, False otherwise.
But the expression

Me.RecordSource = "QryAdageInventoryUsage900reportsum"

can never be null - it's either true or it's false. What's the purpose ofthis
statement?

All I can suggest is that you set a breakpoint (mouseclick in the grey bar to
the left of an executable statement) and see what strSQL is actually being set
to.

--

             John W. Vinson [MVP]
 Microsoft's replacements for these newsgroups:
 http://social.msdn.microsoft.com/Forums/en-US/accessdev/
 http://social.answers.microsoft.com/Forums/en-US/addbuz/
 and see alsohttp://www.utteraccess.com

John,

I got help on this code a couple of years ago and I made some
modifications to adjust for recordsource changes on the form. I
honestly can't tell you the purpose for that code. All I would like is
to export the filtered results to excel with the naming convention.

Ryan

 

[Ryan]

I'm finding the code confusing. In particular, a couple of places you have
lines like

    If IsNull(Me.RecordSource = "QryAdageInventoryUsage900reportsum")

The IsNull() function returns TRUE if its argument is NULL, False otherwise.
But the expression

Me.RecordSource = "QryAdageInventoryUsage900reportsum"

can never be null - it's either true or it's false. What's the purpose ofthis
statement?

All I can suggest is that you set a breakpoint (mouseclick in the grey bar to
the left of an executable statement) and see what strSQL is actually being set
to.

--

             John W. Vinson [MVP]
 Microsoft's replacements for these newsgroups:
 http://social.msdn.microsoft.com/Forums/en-US/accessdev/
 http://social.answers.microsoft.com/Forums/en-US/addbuz/
 and see alsohttp://www.utteraccess.com

John,

I removed the section you suggested the data gets exported to excel
just fine but its the entire table and not the filtered section, would
you know how I fix that? I turned the Order By section into comments
and took that code out because there is not an order by in the query.

If Len(Me.Filter) > 0 Then
Set dbCurr = CurrentDb

' Get the SQL for the existing query
strSQL =
dbCurr.QueryDefs("QryAdageInventoryUsage900reportsum").SQL

' 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)


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

Much Thanks!