Close a query

[Andrew13]

I'm new to Access so I'm not sure if this is possible, however what my database does is run 28 individual checks from an imported excel spreadsheet and filters it to only show any errors.
The errors rarely occur and so it can be reasonably time consuming clicking through the 28 queries that have opened only to find that they are empty due to no errors being found - the database itself works correctly otherwise.

Is it possible to have a seperate query that automatically closes any of these 28 queries if they don't return any data?

 

[Marbles]

If you make a form, then create a button. In the code behind the button, here's an example for two queries:

Private Sub Command0_Click()

DoCmd.OpenQuery "CloseEmpty"
If DCount("ID", "CloseEmpty") < 1 Then DoCmd.Close acQuery, "CloseEmpty"

DoCmd.OpenQuery "CloseEmpty2"
If DCount("ID", "CloseEmpty2") < 1 Then DoCmd.Close acQuery, "CloseEmpty2"

End Sub

It's a bit clumsy, but all you have to do is make the same code for each query, one below the other, then when you press the button, the queries will all open.

 

[Marbles]

I forgot to say, only the queries with records will stay open.

 

[Micron]

You could also write a piece of code that passes each query name to a function, then creates a recordset. If the recordset count is not 0 then you could do just about anything with that info: dump the records and query name to a temp table and run a report on it, simply add the query names to a message box, create a recordset clone from those queries with records, simply don't close the query so you can view it, etc...

 

[Micron]

A bit more writing, but your approach should be to first consider creating functions that can be reused many times - in your current project or even in others down the road. So as a rough outline and in that vein, consider this in the code for, perhaps a click event:

Dim lngRecCount as Long

If lngRecCount = RunQryName "qry1" = True Then 'this calls the function AND directs what to do depending on result, OR you can handle that in the function.
  'do something
End If

If lngRecCount = RunQryName "qry2" = True Then
  'do something
End If

If lngRecCount = RunQryName "qry3" = True Then
  'do something
End If

in RunQryName function:

Public Function RunQryName (qryName as String) as Boolean
Dim db as DAO.Database
Dim rs as DAO.Recordset

RunQryName = False
Set db = CurrentDb
Set rs = db.OpenRecordset (qryName)
If rs.RecordCount  <> 0 Then 
  RunQryName = True
  'this approach doesn't open the query, it only uses it to create a recordset. The function could just as easily open the query and close it if necessary.
End If

Set rs = Nothing
Set db = Nothing
End Function

My point is that if you simply create a function to run any query, you can use it virtually anywhere, any time. If the function takes any action or returns a value, it's only as useful as the action or value applies to other projects. As for my rough example, you can make it cleaner by creating an array of query names and pass each member to the function call. I think it's even possible to pass an array to a function and run the function code on each member of the array.