Tobias Weiblen wrote:
As of OOo 2.2, this neat feature got removed... I have to enter the date
manually now for every single entry, which is not really convenient.
Unfortunately, I am not firm in OOo Basic.
Well, actually I might as well just go over this. I am pretty sure I
know the answer to that question I asked.
You say you aren't real firm with Basic and macros, so I wont really
bore you with going over each line. Also, following along you are gong
to say - jees, it was sure a lot easier when the setting was on the
control and I didn't have to do anything. True, but I am going to expand
on the question just a little to show that with the macro approach here
you gain a lot of flexibility for working with date fields. Also, the
example will show that you can use the functionality in the database
engine to handle your default date fields and when that is the best way
to go. So here we go.
The couple of routines here are ones that will be part of a database
utils library I am getting ready to publish, but for today here is what
you need to do:
From any OpenOffice.org window select
Tools > Macros > Organize Dialogs
In the dialog box that opens select the tab
Libraries
Click on New. In the input box that opens enter the name dbUtils and
click ok.
Click Edit. This opens the Basic and creates the default module, Module 1
Great - now copy and past the following into the editor.
REM ---- start to copy here -------------
function DateTodbDate( aDate as date )
dim dbDate as new com.sun.star.util.Date
dbDate.Month = Month( aDate )
dbDate.Day = Day( aDate )
dbDate.Year = Year( aDate )
dateTodbDate = dbDate
end function
function dbDateToDate( adbDate as new com.sun.star.util.Date )
dbDateToDate = DateSerial( adbDate.year, adbDate.Month, adbDate.Day )
end function
sub setColsToDate( aDataForm as new com.sun.star.form.component.DataForm, _
aryColumnNames as array, _
OPTIONAL adbDate as new com.sun.star.util.Date )
dim cntr as integer
for cntr = LBound( aryColumnNames ) to UBound( aryColumnNames )
if ismissing( adbDate ) = FALSE then
setColumnToDate( aDataForm, aryColumnNames( cntr ), adbDate )
else
setColumnToDate( aDataForm, aryColumnNames( cntr ) )
end if
next
end sub
sub setColumnToDate( aDataForm as new com.sun.star.sdbc.ResultSet, _
aColumnName as String, _
OPTIONAL adbDate as new com.sun.star.util.Date )
dim dbDate as new com.sun.star.util.Date
if not ismissing( adbDate ) then
dbDate = adbDate
else
dbDate = DateTodbDate( now )
end if
with aDataform
.updateDate( .FindColumn( aColumnName ), dbDate )
end with
end sub
REM ---- END COPY HERE -----
Alright to be sure that you got these copied without any problem simply
do this:
On the toolbar line just above the editor text wndow, just to the right
of the drop down combo box on the left is the "compile" tool button,
click and absolutely nothing should happen. That is a good thing, it
means it all copied properly. Go ahead and save this library now.
Now, the part you do need to understand is how to use these routines.
Simply put, the database functions use a date that is not the same
format as the date variable type in OOBasic. Therefore there are two
simple helper funcitons:
DateTodbDate( aDate as date )
Takes a basic date variable type and returns a database date structure type.
dbDateToDate( adbDate as new com.sun.star.util.Date )
Takes a database date structure and returns a basic date variable type.
If you looked at that code in the code snippet repository that set a
date column to NULL ( nothing ) you don't need to understand that,
because you aren't actually dealing with a date. But now, since you need
to do the reverse you are. Using these two functions makes that
difference very easy to deal with, as you will see in a moment.
Notice that in the code snippet routine you find this line:
FieldParent.GetByName(ColName).BoundField.updateNull()
Well, if you tried to change this to actually set a date it would be
something like:
FieldParent.GetByName(ColName).BoundField.updateDate( SomeDateValue )
Looking at the Basic programmers manual, now available at
http://wiki.services.openoffice.org/wiki/Documentation/BASIC_Guide/Language
you might be tempted to then write
FieldParent.GetByName(ColName).BoundField.updateDate( NOW )
to get todays date. A mistake that is seen all the time at the Base user
forum. This doesn't work, because of that difference in date types I
mentioned above. But you can do this:
FieldParent.GetByName(ColName).BoundField.updateDate( DateTodbDate( NOW ) )
and it will work just fine.
OK - still with me? Wonderful.
The next thing is the two routines that actually set the date values
into database columns on a forum. Again looking at the routines in the
code snippet example they did exactly one thing. Set a date column to
NULL. Well, since you are now dealing with real dates it seemed
appropriate to make the routine a bit more flexible.
sub setColumnToDate( aDataForm as new com.sun.star.sdbc.ResultSet, _
aColumnName as String, _
OPTIONAL adbDate as new com.sun.star.util.Date )
You see that this routine takes three parameters, but not the same types
of parameters as the code snippet routine. Also notice that the third
parameter "adbDate" is OPTIONAL. Meaning that you do not have to supply
it, and if you do not then the routine defaults to setting the current date.
How you could utilize the routine is pretty much the same as the code
snippet example.
Fine then, how to use these routines with your database forms?
The first thing you need to understand is that there are two similar
terms in use with this next part. The terms are Form and DataForm. A
form for this purpose is the windowed document you see on screen. A
dataform is a hidden control embedded in that form that connects the
controls on the form to the database.
For the example here I will work with a Form entitled Client Activity.
The forum uses two tables connected to the Form with two DataForm
controls named MainForm and Subform. ( The names created by the Form
wizard )
The main MainForm uses separate controls not n a grid and contains two
fields Initial Contact Date and Date Entered.
The SubForm displays a table grid control and includes the following
fields Contact Date, Scheduled Callback Date, Date Entered
So, without looking at where you put this yet, how would you use the
routines to set the Client fields Initial Contact Date and Date Entered
to todays date. Well you could use two lines of Basic like this:
setColumnToDate( Mainform, "Initial Contact Date" )
setColumnToDate( Mainform, "Date Entered" )
or you could use the last of the routines and do it in one line as
setColsToDate( Mainform, Array( "Initial Contact Date", "Date Entered" ) )
But what would a database be without business rules.
When a new Client record is entered Initial Contact Date defaults to the
current date but may be edited. Date Entered defaults to the current
date and may not be edited.
When ever the sub form moves to the new record row the form should do
the following:
Contact Date and Date Entered default to the current date. Contact Date
may be changed, perhaps you are entering yesterdays calls. Date Entered
may not be edited so the form will have the date control for this field
set as read only, but we still want to update the table column.
Scheduled Callback Date should default to 14 days from the date entered
in the Contact Date field, it may be edited.
I said that these macros made working with dates more flexible. Well we
can now handle our business rules with three lines of basic code.
For the new Client record it is the one line from above:
setColsToDate( MainForm, Array( "Initial Contact Date", "Date Entered" ) )
For the new Contact record it is only two lines:
setColsToDate( SubForm, Array( "Contact Date", "Date Entered" ) )
setColumnToDate( SubForm, "Scheduled Callback Date", DateTodbDate( now +
14 ) )
In other words using the DateTodbDate function you get the easy date
addition and subtraction functions available with OOBasic date variables
for use with database date fields.
Well, it is getting late and I need to take a break. So back in a bit,
with the "rest of the story" and I'll go over how to setup and call
these routines on that Main / Sub dataform Form and even throw in the
proper way to handle the change in the Scheduled Callback date when the
Contact Date control is changed.
Till a little later
Drew
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
