We will be migrating from Ask to Discourse on the first week of August, read the details here

Ask Your Question

How do I check for NULL and/or input errors in macros?

asked 2020-11-13 22:00:36 +0200

paturdc gravatar image

I have a subform that links to an "ID" value from it's parent form. The subform has a push button that activates a macro, which reads the "ID" from the linked field, and passes it to an SQL command.

iValue = oForm.Columns.getByName("ID").Value

It can occur that the "ID" value is NULL (they are are selected by a query). This causes an error in the macro:

image description

I'd like to avoid this by error checking, but I cannot find the relevant code in the LibreOffice Base Guide.

I've discovered that adding a field "fixes" the problem. The code below:

iValue = oForm.getByName("IDField").getCurrentValue()

returns '0' in the case of NULL, and I can always check for that.

However I feel like this solution may not be robust against future updates, or when changing OS.

edit retag flag offensive close merge delete

2 Answers

Sort by » oldest newest most voted

answered 2020-11-13 23:45:38 +0200

Ratslinger gravatar image


You don't even need to go beyond checking the SubForm. At the SubForm level is Row and RowCount. If RowCount is = 0 there are no returned records. Row will tell the record which is selected and 0 if on a new record line.

If you find to be on a new or empty line, you can display message & exit sub. No need for SQL to run in this case.

edit flag offensive delete link more


Thank you ratslinger, I'll try this tomorrow. Obviously if the rowcount = 0 I'll omit the rest of the macro sequence.

paturdc gravatar imagepaturdc ( 2020-11-13 23:57:54 +0200 )edit


Don't know your process but if RowCount > 0 and Row = 0, then there are rows returned but is on a new record line in the table grid.

Be aware of case. This is not a major concern in basic, but if you were using say Python you will get errors - i.e RowCount or Row.

Ratslinger gravatar imageRatslinger ( 2020-11-14 00:52:05 +0200 )edit

if RowCount > 0 and Row = 0, then there are rows returned but is on a new record line in the table grid.

this will definitely be useful at a later time, but in this particular case I just need:

    if ( oForm.RowCount > 0 ) Then

in front of all the code.

paturdc gravatar imagepaturdc ( 2020-11-14 09:33:31 +0200 )edit


when I read a value from a field using

iValue = oForm.getByName("IDField").getCurrentValue()

an empty field return '0'. Is this by design, and should I expect it to work across versions/ OSs?

(I sometimes use macros to insert default values)

paturdc gravatar imagepaturdc ( 2020-11-14 22:15:07 +0200 )edit


Sorry! Comment on wrong question.

Nonetheless, I believe is just may be setting the variable to Variant.

Ratslinger gravatar imageRatslinger ( 2020-11-14 22:47:50 +0200 )edit

Sorry if I wasn't being totally clear. The answer you provided before is sufficient - the forms are limited to a subset of values, so I only need to check for RowCount = 0 There are further checks in the subsequent SQL code.

I was just trying to tack on the question of whether empty "Numeric Fields" always return '0' when called by getCurrentValue() because I sometimes rely on that being the case.

Thank you for all your help, and you patience :)

paturdc gravatar imagepaturdc ( 2020-11-14 23:08:39 +0200 )edit


Another look at you comment and a note here. You talk of a Numeric field. This has a default value. Of course you could set this to an extreme value and test for that, or you could use a different control such as a formatted field. Just some thoughts.

Ratslinger gravatar imageRatslinger ( 2020-11-14 23:49:33 +0200 )edit

answered 2020-11-13 23:21:32 +0200

mgl gravatar image


Instead of calling "ID", use COALESCE("ID", ""), if ID is of string type. If ID is null, COALESCE will replace it by the void string.

If ID is of numeric type, use COALESCE ("ID", '0').

Kind regards, Michel

edit flag offensive delete link more


Thanks, I'm familiar with COALESCE, so perhaps I should have thought of that myself :)

Hang on, I was too quick there. The error is not in the SQL execution, but in actually retrieving the value from the form. The offending line

iValue = oForm.Columns.getByName("ID").Value

is the one causing trouble, because it doesn't accept an empty value.

Like I said it works when the value is stored in a field; but I'm not confident that solution if future proof.

paturdc gravatar imagepaturdc ( 2020-11-13 23:23:56 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2020-11-13 22:00:36 +0200

Seen: 72 times

Last updated: Nov 13 '20