Null Problem....

smelly

Registered User.
Local time
Today, 23:28
Joined
May 23, 2002
Messages
44
Hi,
I have a form where I am passing the values on a click routine to another form. If the fields aren't filled in upon passing I get an error message that the field is null. Can I fix this so the values being passed can be null? THank you for any input!! Here is my code.
Private Sub Check68_Click()
Dim CN As String
Dim CSID As String
Dim BBCNum As Long
Dim Mx As String

CN = Me.CompanyName2
CSID = Me.ClientSampleID
BBCNum = Me.BBCID
Mx = Me.MATRIX

DoCmd.OpenForm "Sheet12"
[Forms]![Sheet12].Text3 = CN
[Forms]![Sheet12].Text5 = CSID
[Forms]![Sheet12].Text1 = BBCNum
[Forms]![Sheet12].Text7 = Mx
DoCmd.PrintOut acPrintAll, , , acHigh, 1, True
DoCmd.Close acForm, "Sheet12"
End Sub
 
You can't set a string to NULL but you can use the "Nz" or Null-To-Zero function to convert the Null value to a default value such as "" when the value is null
 
What are you trying to do with this code?

You open a new form, then you try to populate some of its controls. As it happens, the code in the current sub keeps running, so the fields would be populated assuming you get past your null error. Then you print the current form. Then you close the form that you just opened before it ever gets focus.
 
You could maybe use some validation, ie

IF IsNull(Me.CompanyName2) Then
MsgBox "Please enter the company name!"

ElseIf IsNull(Me.ClientSampleID) Then
MsgBox "Please enter Client Sample ID"

ElseIF IsNull(Me.BBCID) Then
[...and so on for each field...]

Else
'All fields are filled in so continue code..

CN = Me.CompanyName2
CSID = Me.ClientSampleID
BBCNum = Me.BBCID
Mx = Me.MATRIX

[..rest of code..]
end if


This way the form will force users to make sure they have filled in al the fields to begin with.
 
..or alternatively, you can use the code

If IsNull(Me.FieldName) then
Me.FieldName = 0

or Me.FieldName = "" (if its a string)

ElseIF
...
Endif

Although then you probably might want to use the "Nz" function as mentioned above
 
Handling Nulls

It's a good idea to decide on a strategy for coping with Nulls early on in the development process, and then stick to it.

Your options are: to convert all Nulls to something valid, i.e. empty-strings for string-variables or zeros for numerics; to use nulls throughout (my preference).

When assigning a value to a textbox control in VB you have two properties that you can use:

1. Text
2. Value

Text is a string and cannot accept Nulls, but Value is a variant and can. So if you recode like this you should have no problems with Nulls:

Dim CN As Variant

CN = Me.CompanyName2

[Forms]![Sheet12].Text3.Value = CN 'CN can be Null

Of course with bound controls the underlying field must be configured to accept Nulls.

A lot of people don't like Variants as they have been told that they are inefficient. While this can be the case, for instance in a function that iterates a million times or so, you will not notice any effect on most interface operations.

=======================================

Bonus Tip: A little known method for converting Nulls into empty-strings is to use the Format$ function...

Examples...

strWhatever = Format$(oRecordset!FieldThatCanBeNull)

Forms!Sheet12.Text3.Text = Format$(CN)
 
Last edited:
You can also convert a Null value to an empty string like this:

Examples...

strWhatever = oRecordset!FieldThatCanBeNull & ""

Forms!Sheet12.Text3.Text = CN & ""
 
Wow,
Thanks for all the info. I appreciate it!!!
 

Users who are viewing this thread

Back
Top Bottom