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

[paturdc]

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:

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.

[mgl]

Hello

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

[paturdc]

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.

[Ratslinger]

Hello,

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.

[paturdc]

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

[Ratslinger]

@paturdc,

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.

[paturdc]

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]

@ratslinger,

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)

[Ratslinger]

@paturdc,

Sorry! Comment on wrong question.

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

[paturdc]

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

[Ratslinger]

@paturdc,

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.