Default to Todays Date

[RapidSuccessCoach]

Hi ...

How do I get the default date for date field control to be todays date?

... Ron

[Bhikkhu Pesala]

You cannot change the default date field. You must use insert Fields, Other, Date, and chose a non fixed date code. You can set up an Autotext entry to insert this slightly more quickly, using a long date format if you wish.

[DrewJensen]

Hi ...

How do I get the default date for date field control to be todays date?

... Ron

Hi, you have two choices here.

One - You can set the default at the table level to CURRENT_DATE. You do this by using an ALTER TABLE command in the SQL window. For example - Table named Table1 and column named EntryDate - then you would open the SQL window TOOLS>SQL and enter this command

ALTER TABLE "Table1" ALTER COLUMN "EntryDate" DEFAULT CURRENT_DATE

Using this technique you do not enter any value for the field and when it is created in a new record is filled with the current date - you will not see this value however until after the record is saved.

Two - use a macro to auto fill either the bound column or the date control with the current date. I have an example of one of these macros and what I will do is put a copy of it in the code snippet forum later today and post back here when I do. They are quite simple to use.

[RapidSuccessCoach]

Two - use a macro to auto fill either the bound column or the date control with the current date. I have an example of one of these macros and what I will do is put a copy of it in the code snippet forum later today and post back here when I do. They are quite simple to use.

That would be extremely cool if you could post a code snippet ... I've been trying to figure out how to access the control objects via macros and a sample would be great.

[DrewJensen]

Alright so for being in a hurry here - but I am falling behind on a few things. What follows is a copy of email from one of the dba mailing lists - back about a month of so ago...it shows how to update a data control to the current date.

If you have questions on this please ask...

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/wik ... e/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.

[voobase]

Hi Drew,

I have a question about the "method 1" you specified above for creating todays date as a default date. I notice that this works fine when utilized in a main form, however when utilized in a sub form it does not work consistently. If your sub form is linked so that you can have many sub form entries for every main form then it will not work for the first sub form record that is entered for each of the main form entries. This is except the very very first time you enter a sub form record.

Do you think this is a bug and is there any way around it? (I guess I am considering trialing the method 2 you talk of because of this problem).

The problem can be duplicated every time (I set up a dummy database just to check). Please let me know what you think. I'm using OOo 2.30 on windows vista.

Cheers

Voobase

[Dukane]

Where do you put the macros

setColumnToDate( Mainform, "Initial Contact Date" )
setColumnToDate( Mainform, "Date Entered" )

in the form??

[Villeroy]

Where do you put the macros

setColumnToDate( Mainform, "Initial Contact Date" )
setColumnToDate( Mainform, "Date Entered" )

in the form??

Well, this is a very good question. Version 3 will allow for macros stored within the database document which would be a good place, since the above macro relies on certain names for forms and controls that are specific to the individual database. All versions 2.x allow for macros in forms and reports since forms and reports are normal Writer documents. However, if you store the macros in the forms you'll get a warning about macro-security every time you open a form containing macros. The normal way to turn off macro warning for this well trusted document would be to move the respective document to a trusted directory. This does not work because this Writer document is embedded in the Base document rather than a directory. No matter what you want to do with Base macros, the current structure forces you to tear apart the database.
1. You could store the forms outside the database as standalone Writer (or even Calc) in trusted directories. You would need to register the database in order to let the form find it's data source. This was the way how forms where implemented in OOo1 (and personally I think it was the better design in the first place).
2. Alternatively you can store the macros in your general macro container "My Macros" where it is always availlable, although it has been designed to operate on a distinct form in a database. Next time you want to reuse the code with another database, you should try to use equally named objects. Otherwise you would need a copy of the macro where the respective names are adjusted to the new situation.

I could write hours why Base is broken. However, the leading developers are convinced to be on the right way and that we shall overcome some day.
OK, to make things short and come back to the issue:
What's wrong with a date-control, bound to a field with default CUR_DATE together with a label that says "Date (default: today)" or "Date (leave blank for today)" Honestly, this is how I would solve this.

[j8197756]

Hi DrewJensen,

How can I go to the SQL window to enter the commend when I am using the base at table level?

Cheers!

Joe

Hi ...

How do I get the default date for date field control to be todays date?

... Ron

Hi, you have two choices here.

One - You can set the default at the table level to CURRENT_DATE. You do this by using an ALTER TABLE command in the SQL window. For example - Table named Table1 and column named EntryDate - then you would open the SQL window TOOLS>SQL and enter this command

ALTER TABLE "Table1" ALTER COLUMN "EntryDate" DEFAULT CURRENT_DATE

Using this technique you do not enter any value for the field and when it is created in a new record is filled with the current date - you will not see this value however until after the record is saved.

Two - use a macro to auto fill either the bound column or the date control with the current date. I have an example of one of these macros and what I will do is put a copy of it in the code snippet forum later today and post back here when I do. They are quite simple to use.

[DrewJensen]

Hi Joe,

I'm sorry but I'm not quite following.

If you use the ALTER TABLE command then any and all new records will have the desgnated field filled with the current date.

But I'm guessing that I'm missing something in your question?

Thanks

[j8197756]

Hi DrewJensen,

I finally find the way to go to the SQL window. I create the table1 with field "EntryDate". Opent he SQL window, paste your command "ALTER TABLE "Table1" ALTER COLUMN "EntryDate" DEFAULT CURRENT_DATE" and execute. However, it returns the maessage "1: Wrong data type: DEFAULT in statement [ALTER TABLE "Table1" ALTER COLUMN "EntryDate" DEFAULT]".

Please advise what has been wrong in my steps.

Cheers!

Joe

[Villeroy]

ALTER TABLE "Table1" ALTER COLUMN "EntryDate" DATE DEFAULT CURRENT_DATE; //full declaration with type and default
ALTER TABLE "Table1" ALTER COLUMN "EntryDate" SET DEFAULT CURRENT_DATE; // modify the default only

[j8197756]

Thanks DrewJensen and Villeroy! I make it!

Cheers!

Joe