How to use a macro or vb code to do a if then statement

[ebey]

I have a database where I use macros to link data and put into tables for further manipulation. My problem is is that some of the data in the tables does not contain any data and the macro stops there and does not complete. There are no compile errors. So how can I write something up that will look for data and if there is data proceed with the rest of the macro, and if there is no data in the linked table skip the macro and go on to the next. Thanks for your help

 

[scking]

You're outta luck. Macros do not have any way to check for errors, VBA code does. That's why most developers use VBA.

One thing you could do is develop an update query to enter the missing data then run the macro. Another thing would be to convert the macro to VBA using the conversion wizard, then do error checking in the VBA code to handle the case of missing values. Whatever you do you should correct the design to ensure that no nulls are inserted in the database.


----------------------
scking@arinc.com
Life is filled with lessons.
We are responsible for the
results of the quizzes.
-----------------------

 

[Steve101]

Well I agree with scking that its not desirable to do this sort of thing in macros; in fact you'd do yourself a big favour by not using macros and investing in learning VBA.

However,

You can test for the existance of data in a table from a macro, and do conditional processing. In the macro design view, ensure that the View, Conditions option is showing, and in the condition column, add the following:

DCount("*","tblYourTableName&quot > 0

In this example, the action associated with this condition will only occur if records exist in the nominated table.

Cheers,

Steve Lewy
Solutions Developer
steve@lewycomputing.com.au

 

[scking]

Nice goin Steve. I've got to admit I never use macros, always opting for VBA, and therefore have not worked through some of the obvious problems associated with using them. Lots of people are more familiar with macros than I am.

Having said that, I believe that the stated problem was somewhat different from your solution. How to check that a single record in a populated table is valid, read has no nulls. I would handle this in code by developing a function to return a boolean value for whether the record is valid. I assume that the same could be done using macros and something like the following.

1) Not IsNull(FieldValue)
And let the returned value determine what processing steps to take.
2) IIf(Not IsNull(FieldValue), DoSomething, DoSomethingElse)
And specify what steps to take.
----------------------
scking@arinc.com
Life is filled with lessons.
We are responsible for the
results of the quizzes.
-----------------------

 

[Steve101]

Yeah, I know I probably did'nt answer the question exactly; but probably could have done so using Dlookup function; assigning to a variable etc. Always was a lousy listener. Anyway, that response was about my quota of macros for the next 3 years. I probably hate them more than you do.
Cheers,

Steve Lewy
Solutions Developer
steve@lewycomputing.com.au