Conditional Macro

[Ethvia]

Good morning all!

I'm having a problem with a macro. I want to macro to run a line if a condition is true. That condition is to make sure a table has records in it (basically if the table exists). The table is called CensusRpt and below is the condition I'm using:

DCount("*","CensusRpt")=0

if this is true then I want my database to quit (macroaction=QUIT)....if it's not true (there are records in the table) then I want my macro to continue.

The problem is no matter what it doesn't seem to do anything...doesn't quit and doesn't run subsequent lines, even though there are 800 records in that table. If I remove the condition then it works just fine.

thanks for taking the time to read this.

 

[Joe4]

Welcome to the Board!

I usually use this method to count the number of records in an object:
http://msdn.microsoft.com/en-us/library/bb243791.aspx

Also, to further diagnose what the problem might be, you would need to probably tell us two other things:

1. Show your entire code (or at least the relevant section) so we can see how it is working;

2. Explain how your code is invoked.

 

[Ethvia]

Thanks Joe4 for the quick response...

Actually there isn't any code...it's just a basic Macro. Here are the lines in it:

1) Setwarnings
2) Quit (if this statement is true: DCount("*","CensusRpt")=0)
4) KillObject (this is a function that deletes the old spreadsheet on the network)
3) OutputObject (saves a table to a spreadsheet on the network)

That's it...basically I only want to delete the old spreadsheet if a new one exists. The new spreadsheet is linked to my database as the table "CensusRpt".

While I think my logic is correct...it just doesn't do anything...literally. If I remove the second step, which is the only one with a conditional statement, it works just fine.

Does that help at all?

 

[Joe4]

Which version of Access are you using?
On Access 2000, the Quit action does not have any conditions you can put on it.
How exactly are you entering the condition on this action?

 

[Ethvia]

maybe that's the issue....I'm using 97 (2003 is available to me if that's better).

DCount("*","CensusRpt")=0) is literally a copy and paste out of the condition section.

I'll give it a go using a different action then...maybe putting that as a condition to run the kill line? That way the document would get deleted if the condition was true. So would DCount("*","CensusRpt")>0 make sense?

 

[Joe4]

But where exactly are you entering this condition?
The "Quit" action does not have any arguments to enter in any sort of criteria or conditions in Access 2000 or 2003.

I would recommend highlighting your Macro, going to Tools -> Macro -> Convert Macros to Visual Basic. This will convert your query to VBA. You can then edit the VBA to enter your conditions through the use of an IF statement.

You can then use the RunCode Macro action to run the resulting code you build, if you still wish to control it through a Macro.

 

[Ethvia]

Sorry, I misunderstood what you were asking.

In the macro section there is a column that says "Conditions"...that's where I'm entering it.

Then there is an Action column and I enter in what I want that line to do...and finally a comments column.

Here's what I get when I convert it to VBA:


'------------------------------------------------------------
' test_house_census
'
'------------------------------------------------------------
Function test_house_census()
On Error GoTo test_house_census_Err

DoCmd.SetWarnings False
If (DCount("*", "CensusRpt") > 0) Then
Call deletefile
End If
If (DCount("*", "CensusRpt") > 0) Then
DoCmd.OutputTo acQuery, "qry All Patients", "MicrosoftExcel(*.xls)", "p:\er\house census\housecensus.xls", False, ""
End If


test_house_census_Exit:
Exit Function

test_house_census_Err:
MsgBox Error$
Resume test_house_census_Exit

End Function

 

[Joe4]

Can you tell that I don't use Macros that much (I usually use VBA)?
I see what you are talking about now. The Conditions column is hidden, and I had to go to View to enable it. Sorry about that.

In any event, your code could be collapsed down since both conditions are the same, both commands can be put into the same IF statement. So it would look like this:

Function test_house_census()
    On Error GoTo test_house_census_Err

    DoCmd.SetWarnings False
    If (DCount("*", "CensusRpt") > 0) Then
        Call deletefile
        DoCmd.OutputTo acQuery, "qry All Patients", "MicrosoftExcel(*.xls)", "p:\er\house census\housecensus.xls", False, ""
    End If

test_house_census_Exit:
    Exit Function

test_house_census_Err:
    MsgBox Error$
    Resume test_house_census_Exit

End Function

Assuming your DCount statment is working properly (I never use DCount or DSum, I usually use Aggregate Queries or VBA to count the records in a RecordSet), what this will do is if your count is greater than 0, it will call your deletefile code (is this VBA code?) and then write out your query to an Excel file.

Is this what it does? If not, try adding this line just above your IF statment:

MsgBox DCount("*", "CensusRpt")

This will tell you what the count is returning, to see if that is at least working correctly.