Run Multiple queries using code and only export those with true values to excel

[Summer123]

Hello, learning access and VB Code as I have this DB that I have inherited. I have multiple queries (about 60) that run on different tables and have different values on each query. I want to run these queries (using checkbox) and then export query (using button) to excel (each query on single sheet). I have this piece working and i am using transferspreadsheet command to do so, what I now want to do is run each query and ONLY export the queries that have a value (some sort of results) to excel and not all 60 queries... can this be done? is there an easier way?

 

[CBrighton]

Could you not base the TransferSpreadsheet off the same checkboxes so they are only exported if selected?

Or are you trying to counter queries which were selected but have no records?

 

[Summer123]

yes so to give you an example.. if the user checked query 1,2 and 3 and only query 1 and 2 have results then I would like to export just query 1 and 2 to different sheets in excel.

 

[CBrighton]

You could open a recordset and use that to open each of the selected queries and check if there are records.

Something like:

dim db as database
set db = currentdb
dim rst as recordset
 
If chkReport1 = true then
   set rst = ("qryReport1")
   If not rst.eof then
      'Insert TransferSpreadsheet code
   End if
   set rst = nothing
End if
 
If chkReport2 = true then
   set rst = ("qryReport2")
   If not rst.eof then
      'Insert TransferSpreadsheet code
   End if
   set rst = nothing
End if
 
'etc...

 

[Summer123]

cool... when i try to implement that in my code it gives me an error and highlights he following code

error "type mismatch"

Private Sub run_reports_Click()
Dim db As Database
Set db = CurrentDb
Dim rst As Recordset
If Missing_name_on_file.Value = True Then
   Set rst = ("[COLOR=red]1b- Missing_name_on_file[/COLOR]")  [COLOR=red]'highlights the query name when it errors
[/COLOR]   If Not rst.EOF Then
      DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "1b- Missing_name_on_file", fileIn, True, "missing name errors"
   End If
   Set rst = Nothing
End If

 

[CBrighton]

Sorry...

Set rst = db.openrecordset ("1b- Missing_name_on_file")

 

[Summer123]

Same issue again on the same line code "Set rst = db.openrecordset ("1b- Missing_name_on_file") " - runtime error 13 "Type Mismatch"...i am using 2007 ...could that be the reason??? does it matter?

 

[Summer123]

Ok so i went and corrected the issue, however when it goes through the code it still spits out the query that does not have any errors... whats wrong??

Private Sub run_reports_Click()
Dim db As DAO.Database
Set db = CurrentDb
Dim rst As DAO.Recordset
If Missing_name_on_file.Value = True Then
   Set rst = db.OpenRecordset("1b- Missing_name_on_file") 
   If Not rst.EOF Then
      DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "1b- Missing_name_on_file", fileIn, True, "missing name errors"
   End If
   Set rst = Nothing
End If
If Missing_Data_on_01.Value = True Then
   Set rst = db.OpenRecordset("2- Missing_Date_on_01")
   If Not rst.EOF Then
      DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "2- Missing_Date_on_01", fileIn, True, "Missing date errors"
   End If
   Set rst = Nothing
End If
END SUB

missing name errors - should not be reported as there are no outputs for this query

Missing date errors - should be reported in excel as there are errors

 

[vbaInet]

How do you identify the name of the query to export from the checkbox? Does the TAG property of the checkbox contain the name of the query?

You are better off using a multi-select listbox.

 

[Summer123]

the if then else statement runs the query when the checkbox is clicked...is that what you're asking?

 

[vbaInet]

The Value of a checkbox is a Number which can start from 0 to whatever...

The name of the query i suspect is written in the label attached to the checkbox. So by looping through all the checkboxes, how do you know what query that checkbox is linked to?

 

[Summer123]

hi vbaInet, ok so i feel really dumb right now... since i cant understand what u r refering to .. are you saying that the name of the checkbox and the query name need to match? if you look at my if else statement, the lable name is "Missing_name_on_file" and the query name is "1b- Missing_name_on_file"...so if the name is true (which means if its checked) then it will run the 1b-missing query... is that how it should work???

 

[Summer123]

ok so i went back to ur original question and even if i put the query name in the tag property of the checkbox, it still exports both the queries instead of just one query...

 

[CBrighton]

I think vbaInet is trying to say it would be less code to loop through each control and check each checkbox to see if they are true. By storing the query name in the control name you can refer it it by ControlName.Name.

This means that instead of having an if statement to check each control a For Each Loop could be used to check them all.

Something like:

Dim db as DAO.Database
set db = CurrentDB
dim rst as DAO.Recordset
dim MyForm As Form
dim ctl As Control
set MyForm = Forms!FormName
 
For Each ctl In MyForm.Controls
    Select Case ctl.ControlType
    Case acCheckBox
        set rst = db.OpenRecordset (ctl.Name)
        If not rst.eof then
            DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, ctl.Name, fileIn, True, "missing name errors"
        End if
        rst.close
        set rst = nothing
    End Select
Next ctl

:edit:

The above is assuming the names of the queries are used as the names of the check boxes.

 

[Summer123]

thanks CBrighton. question does this mean when my run report button is clicked it will go through each checkbox even if its not clicked?

 

[vbaInet]

With 60 queries, it's easier to manage the selections using a multi-select listbox. There's no doubt that in the future you're going to have to create new ones and delete some, so why use checkboxes?

Use a listbox, highlight the queries you would like to export, loop through the ItemsSelected collection and export it. Simples!

You can get the full list of queries in your database by using this as the rowsource of the listbox:

SELECT MSysObjects.Name
FROM MSysObjects
WHERE ((Left([Name],1)<>"~") AND ((MSysObjects.Type)=5))
ORDER BY MSysObjects.Name;

 

[Summer123]

so i runs through each query but only exports the last query that had results in the first tab... i was thinking it would go through and give me all the query that have results and post it in different sheets... is it because the transfersheet refrences "missing name errors"? how can i make it so that each query with result is posted in different sheet under one excel spreadsheet?

 

[CBrighton]

Yes, to alter this try:

For Each ctl In MyForm.Controls
    Select Case ctl.ControlType
    Case acCheckBox
        If ctl = true then
            set rst = db.OpenRecordset (ctl.Name)
            If not rst.eof then
                DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, ctl.Name, fileIn, True, "missing name errors"
            End if
            rst.close
            set rst = nothing
        End if
    End Select
Next ctl

 

[CBrighton]

You can specify which Worksheet you want to use within a file by using the Range argument of TransferSpreadsheet.

 

[Summer123]

when you say "by using range argument of transferspreadsheet" isnt that the name of the sheet? so "missing name errors" is that the range? or do i put something else here?