invalid call procedure or argument

[ryan.fitzpatrick3]

How do I fix this error? It used to work but it doesn't now. It refers
to this code below. I added this to it.

' Get the SQL for the existing query
If Me.RecordSource = "4QryAdageVolumeSpendYearsum" Then
strSQL = dbCurr.QueryDefs("4QryAdageVolumeSpendYearsum").SQL

If Me.RecordSource = "3QryAdageVolumeSpendsum" Then
strSQL = dbCurr.QueryDefs("3QryAdageVolumeSpendsum").SQL
End If
End If


If you ask why I added it because on the form I have it defaulted to a
record source that pulls a query, but a user can select to sum up the
query results into a summed version of the same info; this is another
query, so basically when a user run's the query it'll prompt them "do
you want to sum up the results" if yes then the form goes to the
query2 record source if no then it stays on the default record source.
The code up above 'should' make it where depending on what query
record source is selected it'll export that information to excel.



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 Me.RecordSource = "4QryAdageVolumeSpendYearsum" Then
strSQL = dbCurr.QueryDefs("4QryAdageVolumeSpendYearsum").SQL

If Me.RecordSource = "3QryAdageVolumeSpendsum" Then
strSQL = dbCurr.QueryDefs("3QryAdageVolumeSpendsum").SQL
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\All Users\Desktop\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\All Users\Desktop\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

 

[ryan.fitzpatrick3]

Also for some reason when I choose YES to sum up the information it
resets the query, why? Here's an example

on default when you open the form data looks like this

plant item vend quantity
1111 1234 5644 1000
1112 1235 5644 2541
1232 2523 4512 10000
1232 2523 4512 15423
etc

now i have several combo boxes that can filter this information to the
users choosing, i.e. combo box on plant, item vend, etc. on the button
i made to execute the query that goes off the combo box selections I
have a prompt saying what I said in the last post, do you want to sum
up the information? yes/no.

for example if you clicked yes it would look like this (lets say with
the information about we filtered by plant 1232 only)

the results would come back as

1232 2523 4512 25423

if you clicked no results would come back as

1232 2523 4512 10000
1232 2523 4512 15423

basically clicking no shows you all PO's issued while clicking yes
does an aggregate total.

now this code below is the export to excel. in the post above I added
some code (which i'm sure is not correct) to be able to export the
information since there is 2 record sources this form pulls from
depending if information is summed or not. ( above I explain) When I
click yes to sum the information and export the information to excel,
it works fine, but when I click No to sum up information, (I want to
show all PO's made) and export the information, the data gets exported
onto excel properly but the information on the form gets reset to the
default form view where the information looks like

plant item vend quantity
1111 1234 5644 1000
1112 1235 5644 2541
1232 2523 4512 10000
1232 2523 4512 15423
etc

I would like it to where when you export your filtered search is still
shown on the form, this used to work but now it doesn't and I'm not
sure why. Can anyone help? I really appreciate this and thank you in
advance.

Ryan

 

[Clifford Bass]

Hi Ryan,

What line is it that causes the error? To find out place a break point
on the line immediately following "Get the SQL for the existing query". Do
this by clicking in the left margin of the editor code window. You will get
a large dot. When you run the process, it will stop at that line and display
the code window. Use Shift-F8 to step through each line until it jumps to
the error handler section. Then you will know the offending line.

By the by, I think you can simplify the entire If Me.RecordSource
section through the second End If with this line:

strSQL = dbCurr.QueryDefs(Me.RecordSource).SQL

Hope that helps,

Clifford Bass

 

[Dennis]

What's the ".SQL" for on the end of your "strSQL =" statements?

 

[Dennis]

Never mind. I understand. But I don't think you can do that particular
assignment to a string variable.

 

[ryan.fitzpatrick3]

I figured it out I did this and it works fine.

If IsNull(Me.RecordSource = "3QryAdageVolumeSpendsum") Then
strSQL = dbCurr.QueryDefs("3QryAdageVolumeSpendsum").SQL
Else:
If Me.RecordSource = "3QryAdageVolumeSpendsum" Then
strSQL = dbCurr.QueryDefs("3QryAdageVolumeSpendsum").SQL
Else:
If IsNull(Me.RecordSource = "4QryAdageVolumeSpendYearsum")
Then
strSQL = dbCurr.QueryDefs
("4QryAdageVolumeSpendYearsum").SQL
Else
If Me.RecordSource = ("4QryAdageVolumeSpendYearsum")
Then
strSQL = dbCurr.QueryDefs
("4QryAdageVolumeSpendYearsum").SQL
End If
End If
End If
End If

 

[Clifford Bass]

Hi Ryan,

You will need to do some indepth debugging to figure it out. I would
suggest you liberally sprinkle break points in your code everywhere things
like the report's filter gets changed and also in the export code. Also, add
some watches of things like the report's filter (Me.Filter) so you can see
when it changes. Then trace through the code, watching what happens in the
code and what/when something happens to the filter.

Good Luck,

Clifford Bass