Access form date field data from base macro

[dougcb68]

I am designing a form in which the user will enter beginning and ending dates. I will use these dates in a macro to create an sql SELECT string. Unfortunately, I have been unable to determine how to access the data entered in the date field from my macro. My best guess at the code is:

Sub DateTest
DIM oForm as OBJECT
DIM oStartBox as OBJECT 'This is a date field on oForm
DIM dDate as DATE

oForm = ThisComponent.Drawpage.Forms.getByName("sqlForm")
oStartBox= oForm.getByName("StartBox") 

dDate = oStartBox.getCurrentValue()     
msgbox dDate 

end sub

“StartBox” is a date field located on the “sqlForm”. I execute the macro using a push button on the form after entering a date in the date field.

I get the error message:
BASIC runtime error.
Incorrect property value.

This seems like it should be a very simple operation, and it probably is, but I am missing something.

[Ratslinger]

Hello,

You can find the answer in the LO documentation - LibreOffice Base Handbook in Chapter - 9 - Macros. Look in section - Converting dates from a form into a date variable (Page 37).

This post is about the same → Time&Datefield in Dialogue saving text instead of time&date.

[LOBster26]

@Ratslinger

You can find the answer in the LO documentation - LibreOffice Base Handbook in Chapter - 9 - Macros. Look in section - Converting dates from a form into a date variable (Page 37).

This post is about the same → Time&Datefield in Dialogue saving text instead of time&date

The link is “forbidden” the second link is to Date in dialogs. I need to figure out the syntax to get the date from a datefield control in a basic macro. I can get the object from my sub form but the date text is eluding me. I can find zero reference to the method to get the date displayed in the datefield form control. I have been able to get endless variety of not found property etc. any help will be immensely appreciated.

[CRDF]

@LOBster26

I can find zero reference to the method to get the date displayed in the datefield form control.

Like this?

	MsgBox "year = " & Dt.Date.Year & Chr(10) & Chr(13) & "month = " & Dt.Date.Month

To declare a date struct variable:

Dim ds As New com.sun.star.util.Date 

[RobertG]

Clicked the first link and it works well here. You could also get it here: https://documentation.libreoffice.org/en/english-documentation-ii/

Short answer for the problem: Date isn’t saved directly as a value. It is saved in a struct. So you have to get currentValue.Year, currentValue.Month and currentValue.Day

[LOBster26]

Thank you Robert!!! The link take me to a page of pdf books. I have BG73-BaseGuide.pdf if you have a page #?

I am not using a dialog and don’t need the system date. I just need the date a user selected from a dropdown datefield control for a report.

I can get all the way to the datefield (and every other control) but the date text is elusive.

Dim oRootForm As Object
oRootForm = GetRootForm()
If IsNull(oRootForm) Then Exit Sub

Dim oTradeForm As Object
oTForm = GetSubForm(oRootForm, “sf_t_editable”)

Dim oStartDate As Object 'd_startDate
If IsNull(oStartDate) Then
MsgBox “Date controls could not be found.”, 48, “FormSummary”
Exit Sub
End If

Dim sDisplayedText As String
oStartDate = GetControl(oTradeForm, “d_startDate”)

REM These do NOT work:
REM sDisplayedText = oStartDate.Text
’ sDisplayedText = oTradeForm.getControl(“d_startDate”).Text
’ sDisplayedText = oStartDate.getValue()
’ sDisplayedText = oStartDate.Text

[CRDF]

@LOBster26

I can get all the way to the datefield (and every other control) but the date text is elusive.

There is no date text. It is a struct as said above.
Event = click on date control.

Option Explicit
Sub ShowDate(Evt As Object)
	GlobalScope.BasicLibraries.LoadLibrary("MRILib")
	Dim Dt As Object
	On Error GoTo Erro
	Dt = Evt.Source.Model
	MRI Dt
	MsgBox "year = " & Dt.Date.Year & Chr(10) & Chr(13) & "month = " & Dt.Date.Month
	Exit Sub
	Erro: MsgBox "ERRO " & Err & Chr(10) & Chr(13) & "na linha " & Erl
End Sub

[LOBster26]

@CRDF

There is no date text. It is a struct as said above.

I have several date controls and I don’t see a reference for a control in your example. There is a record of start and end dates which I will compare with now().
I am new to Basic but have many other language in my belt. (Python, PHP, a bit of C and others)

I dont know how to retrieve the text which ultimately is an SQL element displayed for the user in a dropdown datefield.

If the intent of this sub routine is to extact the date from an object passed: dateText = showDate(oStartDate) let me know I am on the right track.

[CRDF]

@LOBster26

I don’t see a reference for a control in your example.

Dim Dt As Object ' declare variable to reference the date control
Dt = Evt.Source.Model ' set reference to date control

 

@LOBster26

I dont know how to retrieve the text which ultimately is an SQL element displayed for the user in a dropdown datefield.

I’ll elaborate an example using a dropdown (ListBox)…
 

'declare variables for the date fields contents (the dates inserted there):
	Dim ds As New com.sun.star.util.Date, de As New com.sun.star.util.Date
'get a reference to the form:
	oFM = evt.Source.Model.Parent
'get references to the date controls:
	oDFS = oFM.getByName("dfS")
	oDFE = oFM.getByName("dfE")
'get the dates on then:
'check for presence of dates:
	If IsEmpty(oDFS.Date) Or IsEmpty(oDFE.Date) Then
		MsgBox "Dates for the Period missing!"
		Exit Sub
	Else		
		ds = oDFS.Date
		de = oDFE.Date
	End If	

[Villeroy]

@CRDF

I’ll elaborate an example using a dropdown (ListBox)…

A listbox needs to be bound to some column in order to represent a foreign key:
SELECT "string", "ID" FROM "somewhere" ORDER BY "string"
An unbound listbox supports only one column.
Therefore, you need a filter table to store the filter criteria. Having a filter table, you don’t need any macros.
PowerFilter.FB.odb (72.0 KB) (2 listboxes, 2 dates, no macro)

[CRDF]

@Villeroy

A listbox needs to be bound to some column

Sure, but as I interpret it, question is how to get the displayed date into a script (“macro”)

@LOBster26

to get the date from a datefield control in a basic macro.

[LOBster26]

I outline the problem at the start of the thread.
Basically:
in a libreOffice Base sub form I have two date controls:

sf_trades_editable/d_endDate                       ODateModel (name='d_endDate', text='25-12-19')
sf_trades_editable/d_startDate                     ODateModel (name='d_startDate', text='25-12-19')

this assignment produces an error:

sStartDate = oStartDate.getCurrentText()

While the date is stored in SQL the form has dozens of other element I can extract and update via a Basic Macro. The Date control has me totally stumped.
I am looking for the single (or pair of lines) to capture the date as text for several objects.
[RobertG] offered a sub that I think may work like this:

dateText = showDate(oStartDate)

A sub like that is also a great solution.

[Villeroy]

@CRDF

Sure, but as I interpret it, question is how to get the displayed date into a script (“macro”)

Sub Control_Event(ev)
d = ev.Source.Date 'it's a property

[CRDF]

@Villeroy

d = ev.Source.Date 'it's a property

always learning…

	Dim d As New com.sun.star.util.Date
	d = Evt.Source.Date
	MRI d

[CRDF]

@LOBster26

in a libreOffice Base sub form I have two date controls

 

@LOBster26

this assignment produces an error:
sStartDate = oStartDate.getCurrentText()

[Villeroy]

@LOBster26

I am looking for the single (or pair of lines) to capture the date as text for several objects.

Text is pointless, unless you are dealing with text (columns of type VARCHAR). You need to get dates from date controls, times from time controls, numbers from numeric controls.

[LOBster26]

Ok I think I have it figured out.
This is the code I put together from CRDF example and clues from RobertG:

Dim Dt As Object ' declare variable to reference the date control 
oStartDate = GetControl(oTradeForm, "d_startDate")
Dt = oStartDate ' set reference to date control
MsgBox "year = " & Dt.Date.Year  & " month = " & Dt.Date.Month &  " day = " & Dt.Date.Day

Displays: year = 2025 month = 12 day = 3 which is correct.

The key is the dot name format for the date object which I was not aware of until you brilliant folk.

This approach is inline with how I have been addressing all the other form controls and by simply using the dot notation appears to bypass all the uno and com.sun.star.util.Date complexities.

Based on this revelation I was able to track down “Reading and using records” on page 401 of BG73-BaseGuide.pdf

Not sure I would have figured it out from this discussion but it was there fro me to find!

Using forms
The current record and its data are always available through the form that shows the relevant
data (table, query, SELECT). There are several getdata_ type methods, such as:
Dim ID As Long
Dim sName As String
Dim dValue AS Currency
Dim dEntry As New com.sun.star.util.Date
ID = oForm.getLong(1)
sName = oForm.getString(2)
dValue = oForm.getDouble(4)
dEntry = oForm.getDate(7)

on 402:

Date values can be defined not only with the data type Date, but also (as above) as
util.Date. This makes it easier to read and modify year, month and day.
on 404-5
Dim unoDate As New com.sun.star.util.Date
unoDate.Year = Year(Date)
unoDate.Month = Month(Date)
unoDate.Day = Day(Date)

[CRDF]

Dim oStarDate As Object ' declare variable to reference the date control 
oStartDate = GetControl(oTradeForm, "d_startDate")
MsgBox "year = " & oStartDate.Date.Year  & [...]

And please take notice that there is also Basic’s Date type. For doing calculations in code.