[Base] Access2Base - version 0.9.1 released

[JPL]

@gelinp

I respond to mysqlf because I've got a solution from other forum

Could you please edit your post with the URL referencing your solution ?

Thanks.
JPL

[gelinp]

Hi,

I can't find into your documentation with OpenReport function how to fix values opening a report with params ? Also for Open Query ...

By the way I ask an other question : Is it possible to add a functionality to get controls inside a section of a formula ? I'm using a lot sections because it's a way to adapt subform with values entered by the user...

Thank you for your help.
Patrick

[JPL]

@gelinp,

I understand that you are requesting at once 3 enhancements to the Access2Base library:

(1) OpenReport with parameters
(2) OpenQuery of a query with parameters
(3) Controls inside sections

My answers:
(1) The reports are not yet implemented in A2B. This was originally due to the existence of 2 different Report Writers, the standard one and the Sun extension, each with its own API.
If I start working on that, only the Sun extension, which is integrated in LibreOffice, will be supported. At that time, probably (because I don't even know yet how to do it ...), the OpenReport command will be extended, like in MSAccess, with the Filter and Where arguments. Impossible to me, by the way, to give the LO release in which it could be implemented.

(2) Queries with parameters are not supported so far. It is also on my TODO list. Was not a priority. Development is not that easy and the effort seemed to me disproportional with the benefit (but I can revise my mind ... ). Indeed it is quiet easy to instead modify programmatically the RecordSource of a form or to add the WhereCondition argument to the OpenForm statement.

(3) I really don't understand your request ... ?

Sorry for this a bit disappointing reply ...

JPL

[gelinp]

Hi,

I've got a problem to detect if current record is a new one :
There is Form.Recordset.EditMode property, but A2B documentation show this property as Interger with 3 values, and Debug variable display only a boolean value...

Code: Select all   Expand viewCollapse view

REM Edit mode
Const dbEditNone = 0
Const dbEditInProgress = 1
Const dbEditAdd = 2

When I test my new record into a subform, I've got EditMode = false, and What I was attempting to get is EditMode=2 ...

I'musing LIbreOffice 5.4.2.2. With Access2Base_Version = "1.7.0"

Thank you for your help

[JPL]

@gelinp,

you're completely right, it's a bug in Access2Base: EditMode returns erroneously a boolean i.o. the promised integer.

This bug will be corrected in LibreOffice 6.1, the next major release.
In the meantime, you can use _EditMode (the corresponding internal variable) if you want, but, strictly, as a temporary workaround.

However ...
The EditMode property does not reveal if the current record in a form is a new one, it indicates if the recordset is (or not) being updated thru the AddNew, Edit, ... methods.

Hoping this will help.

JPL

[gelinp]

Thank you very much for your response. So, I'm looking for an other solution to determine if current enreg is a new One...

I've got an other problem, may be a bug or not I don't know. It's about OptionGroup into a subform. I put 2 radio buttons with same name "RadioButtonFormatCote", and there is also a group name property in Edit mode, so I put the same name "RadioButtonFormatCote".

I don't understand if I need the same name with a group control, I think no.

But when I run my basic code below :

Code: Select all   Expand viewCollapse view

[...]
Set A2BCtrlForm = _oTAdaptA2BForm.Controls("SubFormRecords", "subform)"
oA2BFormRecords = A2BCtrlForm.Form
[...]
Set ocOptionGroup = oA2BFormRecords.OptionGroup("RadioButtonFormatCote")
[...]


Then I've got an error in line 238 into _OptionGroup, about a calcul to determine radio button position ...

Could you verify it's ok with a subform ? Or give me a little Base file with code sample ? (I need to understand how to fix in edit form properties...)

[JPL]

@gelinp,

I agree that OptionGroups, RadioButtons, GroupBoxes, ... are a bit confusing. Let's try to explain:

First, a distinction has to be made between RadioButtons in forms or subforms vs. RadioButtons in dialogs. (These are design choices made by LO/AOO, not by myself ...)

What is common ?
- a GroupBox is purely a graphical element. It has its own name. It does not interfere with the behaviour of the contained radio buttons. So, let's forget about them. They have nothing to do with this topic.
- an OptionGroup is an artificial entity (defined in MSAccess and subsequently in Access2Base) grouping the inter-related radio buttons. Its main property is "Value" to specify which button is currently selected.

For Forms/Subforms:
- all RadioButtons share the same name whatever their relative position in the form.
- the corresponding OptionGroup has also the same name

For dialogs:
- each RadioButton has its own name. The buttons must have their TabIndex property in sequence without holes.
- the OptionGroup has as name that of the first RadioButton, i.e. that with the lowest TabIndex property.

Hoping this clarifies what should be clarified.
JPL

[gelinp]

Thank you for your explanation, now it's working. My problem was about a different name (a blank character at the end) about Group Names properties for radios buttons.

Also you say :

an OptionGroup is an artificial entity (defined in MSAccess and subsequently in Access2Base)

but there is also a group property with Radios Buttons and It need to use again same name as radios buttons in order to success. You can have a look at property "Nom du groupe" (Group Name) into capture attachment... (It's LibreOffice 5.2.7.2).

Finally I found out a way to fix this Bug, it was not a problem with same name sharing... : Access2Bass library use X-Y position to determine index of RadioButtons into the group. Then, I understanded that the order will be top to bottom and left to right... But my radio buttons may be not exactly aligned with Y (Top/Bottom ?) for horizontal alternative (or with X left for vertical alternative). So may be the order calculated by Access2Base will not be the same as I expected looking my screen. So I decided to use Align button bar to fix exactly the same Ytop for two Radios Buttons and then I fix the same order for activated sequence parameter into radio button. Then this way is fine, no bug at all ... So it's look like a bug to the order calcul and order expectation with IDE parameters ...

Expected will help to fix this little bug.

Thank you again

[gelinp]

I'm asking myself about the way to save data into the database (Form/SubForm -> Database) : When you said :

The Refresh method immediately updates the records in the underlying record source for a specified form to reflect changes made to the data by you or other users in a multiuser environment.

It looks like a requery not a save ...

By the way I've got error trying to use Refresh to a subform :

L'erreur #1 (Une exception s'est produite : ) s'est produite à la ligne 534 dans SubForm.Refresh

SubForm code is :

Code: Select all   Expand viewCollapse view

      
[...]
[533] Case UCase("RecordSource")
[534] If Not Utils._CheckArgument(pvValue, iArgNr, vbString, , False) Then Goto Trace_Error_Value
[535] DatabaseForm.Command = Application._CurrentDb(_DocEntry, _DbEntry)._ReplaceSquareBrackets(pvValue)
[536] DatabaseForm.CommandType = com.sun.star.sdb.CommandType.COMMAND
[537] DatabaseForm.Filter = ""
[538] DatabaseForm.reload()
[...]


And this A2B code looks like a reload (similar to requery ?), and not a save ....

I find out Form.update method (not listed with Form methods but Recordset). But my problem is that after initial load, my Uno form indicate a new value sate (IsNew = true), may be because a calculated field, but I'm questioning myself again about this problem. I can see also after moving next/prev record...

So there is some questions how to update current record with A2B code :

• Recordset property is not listed into A2B Form object documentation ... I know a Form use Resultset, not Recordset to access Table, but It looks like a Form with SQL code use a Recordset... But how to code from a Form builded with Base IDE ? how to know about current form recodset is ready ?
• If I want to create a Recordset associated with my current Form in order to update, this recordset will be a readonly state ... So I can't use it to update ...

I suggest to add an update method to form/subform because there is already here Refresh and Requery ... Elthewhere, could you give me a snippet code to display how to update a Form already in changed state ?

thank you for your help !

[JPL]

@gelinp,

could you give me a snippet code to display how to update a Form already in changed state ?

I'm not sure about what you intend to do exactly ? Do you want to update the form or the database ?
If the database needs to be updated, then the update is in most cases implicit by moving to the next record. If the user wants to make the update manually, he uses the Save button.
The equivalent code should be similar to

Code: Select all   Expand viewCollapse view

DoCmd.RunCommand("RecSave")

Hoping this will help.
JPL

[gelinp]

YES , it's exactly what i was looking for. Thank you again

[AWBL]

Can you confirm whether or not VARCHAR_IGNORECASE is supported by the Field object? I was successfully modifying Field(x).Value, but the same code began throwing an error ("Error #12 (Variable not defined) occurred at line 776 in Field.setValue") when I changed text columns from VARCHAR to VARCHAR_IGNORECASE. Changing the value of Decimal and Date data types continues to work ok.

[JPL]

@AWBL,

Hi,

Access2Base uses the data types described in http://www.openoffice.org/api/docs/common/ref/com/sun/star/sdbc/DataType.html for AOO orhttps://api.libreoffice.org/docs/idl/ref/namespacecom_1_1sun_1_1star_1_1sdbc_1_1DataType.html for LO.

Equivalences are also listed in http://www.access2base.com/access2base.html#DataType. There you can read that VARCHAR_IGNORECASE should be treated identically as VARCHAR.

However you demonstrated that this is not true ... and this is indeed a bug.

The good news is that I could reproduce the problem and that I know how to solve it. The bad news is that it is too late to have the bug corrected still in LO 6.1 (to be released in the coming days) unless you post a bug in bugzilla (https://bugs.documentfoundation.org/). I will anyway correct it for LO 6.2 (early 2019).

In the meantime, you better don't use VARCHAR_IGNORECASE fields ... or, as a dirty workaround, ... patch Access2Base yourself by commenting in line 704 in its Field module:

Code: Select all   Expand viewCollapse view

If Len(pvValue) > _Precision Then Goto Trace_Error_Length

Thanks for your comments and for using A2B.

JPL

[AWBL]

@JPL

Thanks for the quick response. I submitted Bug 118680. I tried to comment the noted line, but unfortunately, the code appears to be read-only. If it's not possible to apply that workaround, I'll update data differently until the fix can be incorporated. Appreciate the work on Access2Base!

EDIT: I found the file and applied the workaround.

[DrewJensen]

Howdy,

What a great job with the basic library, thank you.

A couple of questions, first regarding use of the TT Northwind example Base file. I would like to use that as an example file for migrating from HSQLdb to Firebird data engines. To that end I've first migrated the file by hand (or should say am in the middle of that now) using LibreOffice 6.1 and then will do it as a test case for the upcoming Migration Assistant slated for LibreOffice 6.2. Both the hand migration and the assisted migration would be used for blog posts about the subject.

So my first question is, do you mind if I use that file for that purpose?

Another question; In doing so have come across a few issues, at the moment one is dealing with DLOOKUP which generates an SQL error when used with Firebird. I would like to know if using the issue tracker at Libreoffice would work for such or if you prefer I contact you here or an other way? I would not want to only report the issue, but also offer code to fix the issues, where I can.

Anyway, wanted to touch base with you and looking forward to learning more about the Access2Base library.

As an aside; I watched a video of your presentation at this years FOSDEM regarding Base Documenter. Interesting and I'm looking forward to working with that when it is ready.

Best wishes,

Drew

[JPL]

The previous post from Drew Jensen has been replied with a private message.

JPL

[Rorschach]

Dear Sirs,

In http://www.access2base.com/basedocument ... html#00239 we can see:

Code: Select all   Expand viewCollapse view

Function BankersRound(ByRef pDecimals As Integer)
'Rounds pValue to pDecimals using the “bankers rounding”: rounding to the nearest even number.
'Rounds a value to a given number of decimals using the “bankers rounding”, that is rounding to the nearest even number.
'Input:
'-- pValue: the value to round.
'-- pDecimals: the number of decimals for the rounding.
'Output: The rounded number.
'Usage
'BankersRound(1,5, 0) -> 2
'BankersRound(2,5, 0) -> 2
[...]

but it looks like it's not correct, there it's talked about two parameters (pValue and pDecimals) although only one is seen in the function name. But instead, In https://wiki.documentfoundation.org/ima ... s_v0af.odt we can see

Code: Select all   Expand viewCollapse view

Function BankersRound(ByRef pValue As Double, pDecimals As Integer) As Double
Rounds a value to a given number of decimals using the “bankers rounding”, that is rounding to the nearest even number.
Input
    • pValue: the value to round.
    • pDecimals: the number of decimals for the rounding.
Output
The rounded number.
Usage
BankersRound(1.5, 0) → 2
BankersRound(2.5, 0) → 2
See also Round().


which makes sense. The same happens with `(1,5, 0)` which is a correct `(1.5, 0)` in the second web page.

Thanks. It seems that there's outdated information in http://www.access2base.com?

[JPL]

@Rorschach,

your post shows only that there is a bug (revealed by BaseDocumenter, author = myself) within the Basic Primitives library (author = Jean-François Nifenecker). There is indeed a major difference between the documentation (text) of the primitive "BankersRound" in the odt file you refer to and its implementation (macro).

BaseDocumenter obviously produces a documentation from macros not texts.

BTW the access2base site is not outdated because it illustrates functionalities of a piece of software (i.e. BaseDocumenter) based upon a - possibly outdated - version of another library.

Thanks for your remark.

JPL