Query Problem

[dforgacs]

I have a combo box linked to a table with a list of items to run through a Query. It creates separate workbooks for each item it runs the query on.

Can I make it so it creates only one workbook with data from all items it ran the query on? Instead of in my case 58 workbooks?

Would I use an append or an update query function?

Would it be easier to put all the workbooks together with code in excel?
Help!
dforgacs

 

[Norie]

Can you post your current code, if you have any?

If you want to export data to an existing workbook you just specify it's name in the TransferSpreadsheet method, if that's what you are using.

 

[dforgacs]

Here is my code:

Private Sub Command54_Click()
Dim dbs As DAO.Database
Dim qdf1 As DAO.QueryDef
Dim rst1 As DAO.Recordset
Dim strStart As String
Dim strMiddle As String
Dim strEnd As String
Dim sSQL1 As String
Dim vehtyp As String
Dim year As String
year = Me.ModelYear
vehtyp = Me.VehicleType

strStart = "R:\fcsd\SLTS Extract Database\Total Labor Time Output Files\" & vehtyp & "\" & "Out Src Labor"
strEnd = ".xls"
Set dbs = CurrentDb()
sSQL1 = Me.Combo55.RowSource
Set qdf1 = dbs.CreateQueryDef("", sSQL1)
Set rst1 = qdf1.OpenRecordset

With rst1
.MoveFirst
Do Until .EOF
strMiddle = !FullLaborOpId
Me.Combo55 = strMiddle
On Error Resume Next
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "qryTotalLaborTime", strStart & " " & year & " " & vehtyp & " " & strMiddle & strEnd, True
On Error GoTo 0
.MoveNext
Loop
End With

rst1.Close
Set rst1 = Nothing
Beep
MsgBox "The Report is Located: R:\fcsd\SLTS Extract Database\Total Labor Time Output Files\" & vehtyp & " ", vbOKOnly, ""

Command54_Click_Exit:
Exit Sub

Command54_Click_Err:
MsgBox Error$
Resume Command54_Click_Exit


End Sub

 

[Norie]

Is qryTotalLaborTime a parameter query where the value for the parameter comes from the combobox?

If it is then what you probably need to do in the code is to dynamically create a query for each value from the combobox.

Then export that query to the Excel file.

If you don't change the name of the Excel file, as you currently are, then the queries will be exported to seperate worksheets in the file.

The worksheets will have the same name as the queries.

 

[dforgacs]

Yes, the values for the parameter comes from the combo box.

How do I dynamically create a query for each value from the combobox?

So if change the path and file name to be static, will I end up with 1 workbook and 58 sheet tabs?

 

[Norie]

It depends on the actual query.

I see you are already creating a query for the combobox values.

Just use the same method, but build the SQL for the query by concatenating.

Take a look at the SQL for the existing query to help with that.

Then use CreateQueryDef, but use a name and use that name in the TransferSpreadsheet in place of qryTotalLaborTime.

Don't forget to delete the query after each export, or you'll get errors.

Yes, you should end up with 1 workbook with multiple worksheets - one for each item in the combobox.

 

[dforgacs]

Is there a better way? Than using a list from a combo box that is linked to a table?

I know the query is quite involved and long, do I use all of it in my code?

Can I make it have 1 workbook with all data on 1 sheet?

I appreciate you help!

dforgacs

 

[Norie]

The only other way I can think of would be to create a recordset based on the table.

If you want all the data on 1 sheet why not just remove the criteria?

 

[dforgacs]

If I continue with what I have already done, what criteria do I need to remove?

If I understand this correctly:
I need use my current query SQL, and enter it into my code to create and delete the query every time it goes through the loop. The SQL contains all the table and links that are necessary to pull the records out of the database? Is that correct?
dforgacs

 

[Norie]

Almost right I think.

One thing that is wrong is deleting the query, you would use code for that.

What you need to do is construct the SQL in a string then use it in the CreateQueryDef method.

If you do want seperate sheets then the SQL should include the combobox/table value as criteria.

It's hard to give specific code without seeing the SQL.

Here's something generic.

For I = 1 To 10
     strSQL = "SELECT * FROM Table1 WHERE Criteria=" & I
     Set qdf1 = CurrentDB.CreateQueryDef("Query" & I, strSQL) 
     DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "Query" & I, "C:\MyExcelFile.xls, True 
     CurrentDb.QueryDefs.Delete "Query" & I
Next I