[Solved] Why Object Variable Not Set?

[evwool]

I don't understand why I'm getting an error message 'Object Variable Not Set' on the last line of this code when the date variable 'MyText' appears without error in the Message Box (I'm running this code from a button in my form). I'm guessing it's something to do with the way Base treats dates. Both table and Date control have the same format (DDMMYYYY)

Code: Select all

Sub WriteToField(oEvent As Object)
'both my table and the control
'have the same format DDMMYYYY
Dim MyForm As Object
Dim MyField As Object
Dim MyText As Date
Dim MyFieldName As String
MyText = Date
'The current date
MyFieldName = "BDate"
'the name of the actual table field
'on which the control is based
'not the name of the control, which is txtCNote 
MsgBox MyText
'test if the current date is read and displayed correctly - it is
MyForm = ThisComponent.Drawpage.Forms.getbyName("MainForm")
MyField = MyForm.Columns.GetByName(MyFieldName)
'refer to the field 'BDate'
MsgBox MyField.Name
'check if the correct field is referred to - it is.
MyField.Value = MyText
'The row above is highlighted with the error message
'Object Variable Not Set
End Sub

Any suggestions?

[evwool]

OK, got it. The 'DATE' function in Base gets the system date as a string, not as a date so the data has to go into a text box, not a date field (unless I can then change the string into a date before entering) so I've changed the code to refer to a text field and used code something like this:

Code: Select all

Sub WriteToField(oEvent As Object)
'both my table and the control
'have the same format - Text 
Dim MyForm As Object
Dim MyField As Object
Dim MyText As String
Dim MyFieldName As String
MyText = Date
MyFieldName = "CNote"
'the name of the actual table field
'on which the control is based
'not the name of the control, which is txtCNote 
MsgBox MyText
'test if the current date is read correctly - it is
MyForm = ThisComponent.Drawpage.Forms.getbyName("MainForm")
MyField = MyForm.Columns.GetByName(MyFieldName)
MsgBox MyField.Name
'check if the correct field is referred to - it is.
MyField.Value = MyText
End Sub

[evwool]

Looks like it's not as simple as turning the string into a date. I've used the CDate function which is meant to turn text into a date. It seems to work because the MsgBox line which says
'MsgBox MyRealDate + 1' does indeed give me the current date plus one day BUT the code still gives an error message 'Object Variable not Set' on the last line. Any ideas? Is it something to do with the way Base reads dates? Access for example reads dates as MM/DD/YYYY in coding and this has to be taken into account when using variables.

Code: Select all

Sub WriteToField(oEvent As Object)
'both my table and the control
'have the same format DDMMYYYY
Dim MyForm As Object
Dim MyField As Object
Dim MyRealDate As Date
Dim MyFieldName As String

MyRealDate = CDate(Date)
'change the string into a date with CDate
MyFieldName = "BDate"
'the name of the actual table field
'on which the control is based
'not the name of the control, which is txtCNote 
MsgBox MyRealDate + 1
'test if the current date is read correctly - it is read as a date
MyForm = ThisComponent.Drawpage.Forms.getbyName("MainForm")
MyField = MyForm.Columns.GetByName(MyFieldName)
MsgBox MyField.Name
'check if the correct field is referred to - it is.
MyField.Value = MyRealDate
'But this still doesn't work!
End Sub

[Villeroy]

http://extensions.services.openoffice.org/project/MRI

Code: Select all

MyField = MyForm.Columns.GetByName(MyFieldName)
REM MsgBox MyField.Name
globalscope.BasicLibraries.loadLibrary("MRILib")
MRI MyField

'check if the correct field is referred to - it is.
MyField.Value = MyRealDate 'this can not work

UNO is language independent and you try to assign a Basic date to a field's "Value" without inspecting the object.
http://user.services.openoffice.org/en/ ... 5&p=104390

[evwool]

Sorry, That's over my head Villeroy (though I know about embroidery and kittens) but I did find that if I defined the 'date' variable MyRealDate as Long, I did get a date value entered into my field. Unfortunately, it was the wrong date - it was 2 days after the current system date.

Code: Select all

Sub WriteToField(oEvent As Object)
'both my table and the control
'have the same format DDMMYYYY
Dim MyForm As Object
Dim MyField As Object
Dim MyRealDate As Long
'even though I was hoping for a date value
Dim MyFieldName As String

MyRealDate = CDate(Date)
'change the string into a date serial with CDate
MyFieldName = "BDate"
'the name of the actual table field
MsgBox Day(MyRealDate)
'Correctly identifies  the current day number
MsgBox Month(MyRealDate)
'correctly gives current month number
MsgBox Year(MyRealDate)
'correctly identifies the current year number
MyForm = ThisComponent.Drawpage.Forms.getbyName("MainForm")
MyField = MyForm.Columns.GetByName(MyFieldName)
'check if the correct field is referred to - it is.
MyField.Value = MyRealDate
'enters a date OK, but it's 2 days later than the real date!
End Sub

[Villeroy]

Why do you use MsgBox to show what you already know? Did you install MRI? It would show you that your database columns supports http://api.openoffice.org/docs/common/r ... updateDate which expects a struct http://api.openoffice.org/docs/common/r ... /Date.html
Did you try to run and understand http://user.services.openoffice.org/en/ ... =45&t=1169 Run it in step mode.

[evwool]

I've installed MRI but when I click on the field in my database and go to Tools, AddOn, MRI
selection ,it doesn't mention GetDate or even columns. I'm probably doing something wrong but it
doesn't make much sense to me.
I don't know what you mean when you say 'expects a struct'
and the link is like some foreign language
I use message boxes to see if the value I'm getting is what I thought it ought to be.
I tried using UpdateDate in my code as follows, but it just highlights that line and gives Object variable not set

MyForm = ThisComponent.Drawpage.Forms.getbyName("MainForm")
MyField = MyForm.Columns.GetByName(MyFieldName)
MyField.UpdateDate (CDate(Date))
MyField.UpdateDate (CDate(Date))

sames happens if I replace the last line with
or MyField.UpdateDate (Date)

[evwool]

If I use the following code I can enter the current date as a serial number whose Data Type is
Integer. If I copy this number into a Calc cell and format that cell as a date, it gives me the
correct date.This leads me back to my suspicion that my problem is the way Base handles dates.
I tried formatting the number 1 as a date with the format DD/MM/YYYY. In Calc, Excel and Base I
formatted the cells and query columns. In Access I used the Format function Format(MyNumber,
"DD/MM/YYYY") The results are:
Calc = 31/12/1899
MS Access = 31/12/1899
Excel = 1/1/1900 (!)
In Base however, if I format any number, no matter which, I get 03/01/1. ie BASE does not seem to
treat dates as serial numbers by the time it gets into a database, even thought OfficeBasic does.
Interestingly in MS Access and Calc, entering -1 gives me 29/12/1899;in Excel I get a row of #
symbols - it can't handle these earlier dates. So if you're a historian who wants to create
statistical data, it is Calc rather than Excel which you need.

Code: Select all

Sub WriteToField(oEvent As Object)
'IntField is defined in my table as an Integer data type
Dim MyForm As Object
Dim MyField As Object
Dim MyDate as string
Dim MyRealDate As Long
'even though I was hoping for a Date value
Dim MyFieldName As String
MyRealDate = CLng(Date)
'change the string into a date serial with 
'the CLng function
MyFieldName = "IntField"
'the name of the actual table field
MyForm = ThisComponent.Drawpage.Forms.getbyName("MainForm")
MyField = MyForm.Columns.GetByName(MyFieldName)
MyField.Value = MyRealDate
'enters today's date as a serial number
'which in Calc, or even MS Access, but not in Base,
'can be formatted as the current date
'by just formatting the cell/field that contains it
End Sub

[RPG]

Hello

study the tutorials about forms and basic programming
http://www.geocities.com/rbenitez22/OOo/index.html

and I did remember me this link

Romke

[evwool]

Having trawled through the links and the various documents you recommended, the only real clue to my problem was in RPG's last link - where Villeroy states that Base stores dates in the format 20090915
The answer to my original question could have been expressed simply as follows:
You get Object Variable Not Defined because the Date function gives a String, not a Date value.
To clarify further:
For anyone coming from an Access background, we are accustomed to dates beings stored as long Integers but not as a sort of String, which is what happens in BASE (in Access the digit 1, formatted as a Date would be 31/12/1989 - in Base that date would read 19891231 which though it is made up of digits, is actually a string). To add to the confusion OOBasic reads dates exactly as Access and Calc does which is why my MsgBoxes correctly gave the Day, Month and Year of the current Date when I turned it from a string into a date with CDate. Thus, what I would need, is to find a function that will turn my Date serial number into a date string. An immediate solution to my problem, which I can do already, is to store my dates in 2 fields, one as a Stringwhile the other field will contain the date serial. I can then sort and calculate by this latter field.

[Villeroy]

Base does not store anything at all. HSQLDB or any other type of database stores your data and the UNO-API allows for 2 methods to store a date in a date field regardless of the underlying database type:
oDateColumn.updateDate( com.sun.star.util.Date )
or
oDateColumn.updateString( Y-M-D-string )

The com.sun.star.util.Date required by method updateDate() is a language-independent UNO-structure with 3 numeric elements Year, Month and Date.
Basic dates are completely useless outside of the Basic language. Basic dates have to be converted to strings or UNO-dates before you pass them over to the UNO-API which is completely unrelated to Basic (as a matter of fact, all the other scripting languages work better than Basic).
In the other link I demonstrate how to convert a Basic date to various date-strings using Basic runtime function CDateToISO and how to convert a Basic date to a com.sun.star.util.Date using Basic runtime functions Year, Month and Day.
No, this is not Access. Access is strictly proprietary and comes with tons of convenience for the database developer, including it's own programming language tailored to that particular application.

[evwool]

oDateColumn.updateDate( com.sun.star.util.Date )

Thank you for your persistence, Villeroy. I did read your link but all this stuff about structs doesn't make alot of sense to me. It looks like a 'dictionary' for someone who already understands the lingo (whichever lingo it is).
Do I replace the words com.sun.star.util.Date with something else eg a reference to my field?
Should the results go into a date field or a string field?

[Villeroy]

You know VBA types or variables on a class-module? Uno structs are the same thing. Think of objects without methods, only properties (that's the unset object variable). Most frequently used structures are c.s.s.util.PropertyValue which can be seen as named arguments (pairs of names and values).
When you pass one c.s.s.util.Date to some routine, you pass 3 integer numbers for the year, month and day in one structure and the API instantly recognizes which is what. All this is language independent!
Two routines doing the same thing when bound to a form control which itself is bound to a date field in a database. It does not set the control's value. It writes directly to the bound field.

Code: Select all

Sub WriteToField_A(oEvent As Object)
oControl = oEvent.Source 'the caller
globalscope.basiclibraries.loadlibrary("MRILib")
' MRILib.Module1.Mri oControl
oDBField = oControl.BoundField
' MRILib.Module1.Mri oDBField
basDate=Date()
unoDate = createUnoStruct("com.sun.star.util.Date")
unoDate.Year = Year(basDate)
unoDate.Month = Month(basDate)
unoDate.Day = Day(basDate)
oDBField.updateDate(unoDate)
End Sub

Sub WriteToField_B(oEvent As Object)
oControl = oEvent.Source 'the caller
globalscope.basiclibraries.loadlibrary("MRILib")
' MRILib.Module1.Mri oControl
oDBField = oControl.BoundField
' MRILib.Module1.Mri oDBField
basDate=Date()
strDate = year(basDate)&"-"& Month(basDate) &"-"&Day(basDate)
REM ISO string "2009-10-21"
oDBField.updateString(strDate)
End Sub

A date control's string is unusable for a macro programmer. It may be anything, depending on the currently used locale and number format.
The date control has only one unambiguous value: Property "Date" which is an integer number, but not a day-number as in a spreadsheet or something like cINT(Date) in Basic. It is the concatenation of YYYYMMDD as integer number. Today's date in a date control's integer property "Date" is number 20091021 (twenty-million-ninety-one-thousand-and-twenty-one).
However, setting a control's value or text does not update the underlying bound field automatically.

[evwool]

Thanks Villeroy, That worked. Fantastic
This is what the whole code looked like when I adapted it to my Database

Code: Select all

Sub WriteToField_A(oEvent As Object)
Dim oDBField As Object
Dim MyForm As Object
Dim MyFieldName As String
Dim oControl As Object
Dim basDate As String
Dim unoDate As Variant
MyFieldName = "BDate"
MyForm = ThisComponent.Drawpage.Forms.getbyName("MainForm")
oDBField = MyForm.Columns.GetByName(MyFieldName)
oControl = oEvent.Source 'the caller
globalscope.basiclibraries.loadlibrary("MRILib")
' MRILib.Module1.Mri oControl
' MRILib.Module1.Mri oDBField
basDate=Date()
'read the system date as a string
unoDate = createUnoStruct("com.sun.star.util.Date")
unoDate.Year = Year(basDate)
unoDate.Month = Month(basDate)
unoDate.Day = Day(basDate)
'change it from a string into a format 
'which will go into a Base date field
oDBField.updateDate(unoDate)
End Sub

May I ask?- The line which says
globalscope.basiclibraries.loadlibrary("MRILib")
Does it mean that this code only works because I installed that MRI Addon?
Or is it completely unrelated to that?

[Villeroy]

May I ask?- The line which says
globalscope.basiclibraries.loadlibrary("MRILib")
Does it mean that this code only works because I installed that MRI Addon?
Or is it completely unrelated to that?

It's a friendly reminder about the tool which helped me finding the right methods, properties and structs.
Remove the quote and if MRI is installed you'll see.

Code: Select all

Sub WriteToField_A(oEvent As Object)
  oControl = oEvent.Source 'the caller
  globalscope.basiclibraries.loadlibrary("MRILib")
  MRILib.Module1.Mri oControl

[evwool]

Thanks for clearing that up, Villeroy