running multiple queries with parameters from table (1 Viewer)

[Minty]

Okay - Lets go through those;
1. Think that's just a typo on my behalf.
2. Yes you will need to delete it after it's been used but before the next loop is processed. You'll need a

DoCmd.DeleteObject acQuery, "Query1"

It might be worth changing the name of that to something more obtuse.
3. Now you have the query per broker, so let's create the spreadsheet.

You'll want each one to have a different name otherwise you'll just be overwriting the same one all the time.

Add a new couple of new Dims at the top of the code -

Dim sFilePath as String
Dim sFileName as String

Set the sfilepath before the loop.

sfilepath = "c:\somepath\OnYourPC\"

Then in your loop, before destroying the query we'll add in the filename and the transfer to spreadsheet ;

sFileName = "OpenBrokerRep_" & iAcctNo & ".xls"

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Query1", sFilePath & sFileName

 

[wynstonh]

I get "sub or function not defined" at this point:

DoCmd.DeleteObject acQuery, "Query1"

 

[wynstonh]

Here's the full code:

Private Sub Command7_DblClick(Cancel As Integer)
Dim db As Database
Dim qrydef As QueryDef
Dim strSql As String
Dim varItem As Variant
Dim iAcctNo As Long
Dim sFilePath As String
Dim sFileName As String
Set db = CurrentDb

sFilePath = "I:\Data\OMR8295\Commission Model\3a. CONTRACT\Outputs\"
If List2.ListIndex = -1 Then
'If ListIndex is -1, nothing selected
MsgBox "Nothing was selected!"
Else
For Each varItem In Me.List2.ItemsSelected
iAcctNo = List2.ItemData(varItem)
strSql = "Select * From qry_live_by_broker WHERE [Broker BP] = " & iAcctNo & " ;"
Debug.Print strSql ' Remove this once you have it working
Set qrydef = db.CreateQueryDef("Query1")

sFileName = "OpenBrokerRep_" & iAcctNo & ".xls"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Query1", sFilePath & sFileName




DoCmdDeleteObject acQuery, "Query1"


Next varItem
End If

End Sub

 

[Minty]

Try db.qrydef.delete - Sorry I'm not in a position to test this at the moment.

 

[wynstonh]

Good morning Minty, hope you had a good weekend.

All the code now seems to work except this one line:

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "query1", sFilePath & sFileName

where I am getting an error: "Query must have at least one destination field".

I'm pretty certain the syntax is correct so not sure what the error means.

 

[wynstonh]

Scratch that...just worked out I'd deleted a line of code in error.

Works perfectly now. Minty, thanks so much! You have saved me untold hassles.

 

[Minty]

You have missed out this line before the transfer command. It sets the queryto the SQL string.

qrydef.SQL = strSql

 

[wynstonh]

yeah, just spotted that myself

thanks again

 

[Minty]

Glad you have it sorted out.
Good luck with the rest of your project.

 

[wynstonh]

Hi Minty,
Sorry to drag up an old thread but I have tried copying this exact code into another database and I'm getting a "compile error: user-defined type not defined".

It's not the 1st time I've used this code but one of the databases I've copied it into was corrupted so I had to revert to a backup prior to this bit being implemented and now it won't work!!

 

[wynstonh]

sorted - my references weren't quite right