Can i build a property to set NULL value for a control?

[rocco]

Hello...
the subject can be a bit confusing, here is my doubt>
I have a form which instantiate a class. I would like that a control in the
form get the value of a property of the class. But this value can also be
Null. This is the code for the property:
----------------
Public Property Get IDtreatment(WhichID As String) As String
Dim rstTreatment As DAO.Recordset
Set rstTreatment = CurrentDb.OpenRecordset("SELECT TX FROM TBLREGVISITS
WHERE ID='" & WhichID & "' AND VISIT='V1'")
If rstTreatment.BOF = False Then
IDtreatment = rstTreatment.Fields(0)
Else
IDtreatment = Null
End If
End Property
----------------

But it fires me an error so I guess since the property is a string it cannot
be Null, at least it can be Empty, acting like a variable, but maybe not
null. Is this right (hoping it is clear first...)?
Rocco

 

[Dirk Goldgar]

Hello...
the subject can be a bit confusing, here is my doubt>
I have a form which instantiate a class. I would like that a control in
the
form get the value of a property of the class. But this value can also be
Null. This is the code for the property:
----------------
Public Property Get IDtreatment(WhichID As String) As String
Dim rstTreatment As DAO.Recordset
Set rstTreatment = CurrentDb.OpenRecordset("SELECT TX FROM TBLREGVISITS
WHERE ID='" & WhichID & "' AND VISIT='V1'")
If rstTreatment.BOF = False Then
IDtreatment = rstTreatment.Fields(0)
Else
IDtreatment = Null
End If
End Property
----------------

But it fires me an error so I guess since the property is a string it
cannot
be Null, at least it can be Empty, acting like a variable, but maybe not
null. Is this right (hoping it is clear first...)?

Change your property definition to return a Variant instead of a String, and
all should be well. I'd recommend, though, that you close your recordset
before returning:

'----- start of revised code -----
Public Property Get IDtreatment(WhichID As String) As Variant

Dim rstTreatment As DAO.Recordset

Set rstTreatment = CurrentDb.OpenRecordset( _
"SELECT TX FROM TBLREGVISITS " & _
"WHERE ID='" & WhichID & "' AND VISIT='V1'", _
dbOpenSnapshot)

If rstTreatment.BOF = False Then
IDtreatment = rstTreatment.Fields(0)
Else
IDtreatment = Null
End If

rst.Close

End Property
'----- end of revised code -----

 

[rocco]

Thank you!!!
Yes.. I did close the recordset after I posted the code... I'm a little
obsessed about that!!

 

[rocco]

excuse me... just to learning something... why using the variant will solve
the problem?
It really solved the problem, but just want to understand what I'm doing...
just to be able to re-use when need it.
Rocco

 

[Dirk Goldgar]

excuse me... just to learning something... why using the variant will
solve
the problem?
It really solved the problem, but just want to understand what I'm
doing...
just to be able to re-use when need it.

The String data type cannot hold the value Null; it can only hold string
values -- even the value "" is a string, albeit one of zero length. The
only data type that can have the value Null is the Variant data type.