By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,662 Members | 1,765 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,662 IT Pros & Developers. It's quick & easy.

Continuing an Access Macro with Conditions

P: 5
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.
Oct 29 '08 #1
Share this Question
Share on Google+
3 Replies


P: 93
Hi

Could you paste your code?

Regards
Emil
Oct 29 '08 #2

P: 5
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.
Oct 29 '08 #3

ADezii
Expert 5K+
P: 8,607
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:
Expand|Select|Wrap|Line Numbers
  1. Dim qdf As DAO.QueryDef
  2. Dim MyDB As Database
  3. Const conDIR_TO_TRANSFER_TO As String = "C:\Test\"
  4.  
  5. Set MyDB = CurrentDb
  6.  
  7. For Each qdf In MyDB.QueryDefs
  8.   If Left$(qdf.Name, 3) = "Qry" Then        'Filter only your Queries
  9.     If DCount("*", qdf.Name) > 0 Then       'Does the Query produce Records?
  10.       DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, qdf.Name, _
  11.                                 conDIR_TO_TRANSFER_TO & qdf.Name & ".xls", True
  12.     End If
  13.   End If
  14. Next
Oct 29 '08 #4

Post your reply

Sign in to post your reply or Sign up for a free account.