Allow Null

[MichHart]

Good Morning everyone:

I have a simple question, and it is probably already in a Forum, but the search engine is down here this morning and I need to get this answered.

When I retrieve a record from a table to be displayed on a form, I want fields that are empty in the table to appear empty in the appropriate controls on the form

such as a field named MiddleName
if there is no Middle Name, the textbox on the form will appear empty

I am getting error Invalid use of Null. ??

How can I fix this ?

MichHart

 

[CmPaiva]

Do as follows, before assigning:

...
if not isnull(rs.YourField) then
Yourcontrol.text=rs.YourField
end if
...

I hope this helps you
Carlos Paiva

 

[jeepxo]

What I am assuming here is you create a recordset, then you blindly display the the value of the record in a table...like
txtMiddleName.Text = recordset("MiddleName&quot.value

The invalid use of null could be from a couple of reasons, either in your database you have the value defined as a numeric (integer, long double etc) and you have no value in the database for that record
or you have defined the value as a string (text or memo in access) and you have changed it to "Do not allow Null" after you had some data already input.


A quick fix on it would be to convert it to a string when you display it with the Cstr command.

it would then be

txtMiddleName = Cstr(recordset(middlename).value)

 

[MichHart]

Thanks it worked, but is there a way to do it that will apply to all fields on the form or will I have to do it for each field??

Thanks again for the help

MichHart

 

[jeepxo]

you have to do it for all the fields that you want to verify...whether you use the Cstr function that I have said or the isNull that Carlos suggested

 

[MichHart]

Ok thanks for your help, both of you

MichHart

 

[PraveenMenon]

MichHart,

try this

txtMiddleName.text = rst.fields("MiddleName&quot & ""

I think this will reduce the code to some extent. All the Best
Praveen Menon
pcmin@rediffmail.com

 

[CCLINT]

>txtMiddleName.text = rst.fields("MiddleName&quot & ""

This has always been a "good" method.
Or better:
txtMiddleName.text = rst.fields("MiddleName&quot & vbNullString

However, you need to assign the field value to a string, variant or textbox.text, as it will not work when assigning it directly to a number or date variable.

Therefore, it is probably best to create a small function where the field is passed to, the type is checked, based on the type the value gets formated, and then passed back to the caller.

Or use a data class and do the formating automatically on all fields, setting the formated values to private class variables, and letting the caller get it's data from a property Get proceedure. Once built it is very simple to use and then ALL of your recordset objects are easily created with this method. Even the smallest of ones are created just as fast and "worry" free.
[/b][/i][/u]_{*******************************************************
General remarks:
If this post contains any suggestions for the use or distribution of code, components or files of any sort, it is still your responsibility to assure that you have the proper license and distribution rights to do so!}

 

[PraveenMenon]

Nice job CCLINT! All the Best
Praveen Menon
pcmin@rediffmail.com

 

[MichHart]

Hi everyone and thanks so much for all the input.

I am getting an error now though pointing to this line of code

If Not IsNull(rs!MiddleName) Then

txtMiddle.Text = rs!MiddleName

End If

Runtime Error 91
Object variable or With Block Variable not set

Any suggestions!!

MichHart " Never regret yesterday because it cannot be changed;
Instead make sure today does not become a regret of tomorrow."

 

[dvrobin]

Why not use a NZ (Null to Zero) function to wrap around your field value? Like this:

Public Function NZ(SomeValue as Variant, Optional DefaultValue as Variant = "&quot as Variant

If IsNull(SomeValue) Then
NZ = DefaultValue
Else
NZ = SomeValue
End If

End Function

This function will work with most data types. Call it like this:

txtMiddle.Text = NZ(rs!MiddleName)

or

txtWeight.Text = NZ(rs!Weight,0)

When you need to put data back into the database, you can create the reverse function, ZN.
Dave Robinder, MCSD
Consultant
Booz Allen Hamilton
Colorado Springs, CO
(719) 590-6041

 

[PraveenMenon]

u missed the rs.open statement!!! All the Best
Praveen Menon
pcmin@rediffmail.com

 

[SemperFiDownUnda]

"txtMiddleName.text = rst.fields("MiddleName&quot & ..."

BAD BAD programmers 8P

Try not to use default properties.

you could also do

IIF(ISNULL(rst.fields("MiddleName&quot.value),"",rst.fields("MiddleName&quot.value)
or
IIF(ISNULL(rst.fields("MiddleName&quot.value),vbNullString,rst.fields("MiddleName&quot.value)

reason I say not to use default properties is because in some cases other programmers looking at your code quickly might think you are using the actual ADODB.Field object and not the Value property of the ADODB.Field object.

 

[MichHart]

Hi eveyrone:

Actually Thanks CCLINT it was your suggestion that worked on the textboxes, but for the combo boxes where I am running a SELECT statement to find the value that should appear in the combo box upon selection of the player, it isn't refreshing when I select another player.

I have several combo boxes and a select statement for each.

here is the code for two of the combo boxes

Set rs = db.Execute("SELECT Countries.[Index], Countries.[Long], Players.[SCountry]" & " FROM Countries, Players WHERE Players.[SCountry]= Countries.Index&quot
cboNation.Text = rs.Fields("Long&quot & vbNullString
rs.Close
Set rs = Nothing


Set rs = db.Execute("SELECT Contacts.[Index], Contacts.[LastName], Contacts.[FirstName], Players.[Agent] " & " FROM Contacts, Players WHERE Players.[Agent] = Contacts.Index&quot
cboAgent.Text = rs.Fields("FirstName&quot & " , " & rs.Fields("LastName&quot & vbNullString
rs.Close
Set rs = Nothing


Any suggestions??

Mich Hart " Never regret yesterday because it cannot be changed;
Instead make sure today does not become a regret of tomorrow."