[Solved] Set date in field at mouse release in form

[pengel200]

Hi. I'm new to Open Office Base, but have pretty deep experience with Access and VBA, and am an experienced VB.Net OO programmer. I'm having trouble translating the object events from Access to Base.

I need to update a date field on my form upon a mouse release based on the value of the clicked field. I have found some examples that allow you to update a field on a form with the current date using Form Load event. I have tried to modify it to fire on a field mouse release event on a field, but I'm just spinning my wheels. Here's what I'd like to do:
Form Name: OpenOrdersForm
Field Name of clicked field: OrderPickedUp (it's a checkbox, so values will either be true or false)
Field Name of date field to be updated: DatePickedUp

On mouse release, I'd like the macro to check to see if the OrderPickedUp field is true or false (check or not)
If checked, put today's date in the DatePickedUp field
If unchecked, clear the DatePickedUpField

It's embarrassing to have to ask for assistance, but I'm getting frustrated and wasting a lot of time. I promise, once I've mastered this new environment, I'll give back to the forum. Any help would be greatly appreciated.

[JeJe]

If you post what you've got already and where its not working...

[MrProgrammer]

Hi, and welcome to the forum.

It's embarrassing to have to ask for assistance, but I'm getting frustrated and wasting a lot of time

While I don't have the background to help you with OpenOffice macro programming and your form, I wanted to say that I realize that your project is frustrating. Although you have experience with Access, VBA, and VB.Net, the only way that helps is with understanding how to write and debug programs. The major challenge to OpenOffice macro programming is understanding the API (Application Programming Interface). The OpenOffice API is completely different than the VBA API used in MS office. Your experience with Microsnot's software is of essentially no help in understanding the OpenOffice API. It is quite clever, but it uses concepts of services, interfaces, etc. that will be new to you. Even experienced programmers should expect to spend a week or more learning these concepts. And it can be challenging to determine how to use the API to accomplish one's goal.

The difficulty is compounded if the OpenOffice component you're using (Base) is also new to you. Trying to write macros before getting a firm foundation with Base will be hard. We have tutorials to help with that:
[Tutorial] MS Access and OOo Base
Board index → Getting started → Tutorials → Base
Board index → Getting started → Tutorials → Base → Database Examples

You will want the link the the API documentation, of course, but it is impractical to do macro programming without an object inspector, either MRI or XRAY.
[Tutorial] Introduction into object inspection with MRI
X-Ray version 6

New macro programmers can learn a great deal from Andrew Pitonyak's OpenOffice Macro Information.

I promise, once I've mastered this new environment, I'll give back to the forum.

We'll appreciate that. Sorry to overwhelm you with links today, but it's the best assistance I can provide.

[pengel200]

Thanks. I’ll check these out.

[Villeroy]

The attached file tries a solution free of macros based on the limited capabilities of the Base component. Of course, this can not apply when working with an existing database where tables and relations are set up differently.

The "checked" status is implemented by a one-to-one relation where the "passive side" (TBLB inheriting its primary key values from TBLA) has one matching record with time stamp or not. TBLB stores a time stamp and a boolean value and possibly some more attributes related to the "checked" status (editor ID).

In order to add such a time stamp record to TBLB related to the selected record in TBLA, you click the check box which starts a new record in TBLB with the same unique ID as in TBLA. Then you hit Enter (trigger the OK button) in order to store this new record. There is also a cancel button to undo any unsaved check mark. Once the record is saved, the database engine saves a time stamp automatically.
In order to remove the "checked" status, you should delete the related record from TBLB using the "Uncheck" button.
A user may also untick the check box and save the record. This requires that you write your queries so they ignore all records where the boolean field is false.

[Villeroy]

And this is a possible macro solution. Same database, but with the boolean field nullable. I use the MRI extension extensively. And I avoid manipulating form controls. When possible, I manipulate the form's record set.

P.S. I was in a hurry yesterday. Base macros take a lot of time.
1) I did NOT use the mouse event. I react on the status change of the control value which is indicated by the Selected property of the passed event struct. Therefore the routine name "checkbox_MouseReleased" is misleading.
2) First I assigned the mri routine to the status change event. "Selected" shows the new status, "Source" is the calling object. Then I navigated to Source.Model.Parent which is the form. Therefore "oForm" might be a better variable name than "oParent". If the checkbox were ebedded in a table control, ev.Source.Model.Parent.Parent would get the form. I called findColumn with the name of the timestamp column and used that index to set that column to Null. This way, MRI generated most of that code.
3) I renamed some variables (forgot oParent and the routine name) and added the branching with the type conversion from Basic to UNO struct from my own tutorial viewtopic.php?t=82181
Important note: the resulting timestamp has no fractions of seconds which is a limitation of the StarBasic language.

The following helper routine gets whatever parent object object from the given object model up until the embedding form document:

Code: Select all

Function getParentObject(byval m, srvName$)
do until m.supportsService(srvName)
	m = m.getParent()
loop
getParentObject = m
End Function

With this helper routine getParentOpbject((ev.Source.Model, "com.sun.star.form.component.DataForm") gets the caling object's form no matter if the calling object is column of a table control or not.
getParentObject(ev.Source.Model, "com.sun.star.document.OfficeDocument") gets the embedding form document.

[pengel200]

@villeroy that was exactly what I needed. You did a great job explaining how and why it worked. I like the change from the mouse release to the status changed event. Makes more sense. I did a quick edit to fit my form and fields and it worked perfectly. This is a great leg up.