Continuing an Access Macro with Conditions

[Marisol2]

I have a macro where I do a transfer spreadsheet from some db queries. I only want the macro to create the spreadsheet if there are results in the query. If not, I want the macro to skip that transfer spreadsheet and move on to the next transfer spreadsheet command.

The way I tried fails when there are no records. Basically I did the following on the macro conditon:

DCount("*","query name was here")>0

This works on the queries with records until I hit a query that doesn't have any records. The macro stops and doesn't move on to the next command.

I understand why, I told it to stop, but don't know how to tell it, move on :)

Any ideas? Thanks.

[emsik1001]

Hi

Could you paste your code?

Regards
Emil

[Marisol2]

Hi--

I'm using a macro that looks like this:

Condition: DCount("*","qry1")>0
Action: OpenQuery
Query Name: Qry1
View: PivotTable
Data Mode: Read Only

Action: Run Command
Command: PivotTableExportToExcel

Action: Close
Object Type: Query
Object Name: Qry1
Save: No

Condition: DCount("*","qry2")>0
Action: OpenQuery
Query Name: Qry2
View: PivotTable
Data Mode: Read Only

Action: Run Command
Command: PivotTableExportToExcel

Action: Close
Object Type: Query
Object Name: Qry2
Save: No

Condition: DCount("*","qry3")>0
Action: OpenQuery
Query Name: Qry3
View: PivotTable
Data Mode: Read Only

Action: Run Command
Command: PivotTableExportToExcel

Action: Close
Object Type: Query
Object Name: Qry3
Save: No


The macro works if all 3 queries have results. But say if qry2 didn't then the macro stops. I need something that tells it, don't produce the export, but move on to the next action with a condition.

Thanks.

[ADezii]

Hi--

I'm using a macro that looks like this:

Condition: DCount("*","qry1")>0
Action: OpenQuery
Query Name: Qry1
View: PivotTable
Data Mode: Read Only

Action: Run Command
Command: PivotTableExportToExcel

Action: Close
Object Type: Query
Object Name: Qry1
Save: No

Condition: DCount("*","qry2")>0
Action: OpenQuery
Query Name: Qry2
View: PivotTable
Data Mode: Read Only

Action: Run Command
Command: PivotTableExportToExcel

Action: Close
Object Type: Query
Object Name: Qry2
Save: No

Condition: DCount("*","qry3")>0
Action: OpenQuery
Query Name: Qry3
View: PivotTable
Data Mode: Read Only

Action: Run Command
Command: PivotTableExportToExcel

Action: Close
Object Type: Query
Object Name: Qry3
Save: No


The macro works if all 3 queries have results. But say if qry2 didn't then the macro stops. I need something that tells it, don't produce the export, but move on to the next action with a condition.

Thanks.

What you attempting to do may be a little hairy with a Macro. Assuming the Queries you wish to process are prefaced with 'Qry', why not use a more efficient code based approach, something similar to:

Dim qdf As DAO.QueryDef
Dim MyDB As Database
Const conDIR_TO_TRANSFER_TO As String = "C:\Test\"
 
Set MyDB = CurrentDb
 
For Each qdf In MyDB.QueryDefs
  If Left$(qdf.Name, 3) = "Qry" Then        'Filter only your Queries
    If DCount("*", qdf.Name) > 0 Then       'Does the Query produce Records?
      DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, qdf.Name, _
                                conDIR_TO_TRANSFER_TO & qdf.Name & ".xls", True
    End If
  End If
Next