Getting "you tried to assign the null value to a variable that is not a variant data type" error wit

[Jess]

Hello:

I am very new to using Access, and have just recently built a simple
contact management database from a template downloaded through MS
(http://office.microsoft.com/en-us/templates/TC010178391033.aspx?CategoryID=CT011366681033).

This error occurs even in the template version. When a user tries to
input a call log entry without first entering a subject the error: "you
tried to assign the null value to a variable that is not a variant data
type" occurs; clicking on help gives me the following: "You tried to
assign the Null value to a variable that is not a Variant data type.
(Error 3162)
You tried to assign a Null value to a variable that is not a Variant
data type. Use the Dim statement to declare the variable as a Variant,
and then try the operation again."

I know there are many other threads concerning the same error in
similar situations, however, my limited knowledge of Access makes it
difficult to implement any of the proposed solutions. Any help at all
is appreciated. Thank you!

 

[Guest]

The templates you download are sometimes buggy out of the box.
The error is caused by just what is says. Only Variant data type variables
can be assigned a Null value. In that it occurs when someone does not enter
a subject means that the code is assigning the value of the text box where
the subject is entered to a variable. A control on a form that has had
nothing entered has the value Null.

The best way to cure that is to use the text box's Before Update event to
check for data. That is the text box where the subject should be entered.
Since I don't know the control's name, I'll make one up and you can change to
be the correct name:

Private Sub txtSubject_BeforeUpdate(Cancel As Integer)

If IsNull(Me.txtSubject) Then
MsgBox "A Subject is Required"
Cancel = True
End If

End Sub

 

[Jess]

Hi Klatuu:

Thanks very much for your response. I tried what you recommended,
unfortunately it didn't work. Not a bad idea to just have a different
alert message. I would prefer having it set so that the subject field
wasn't required, but the personalized message box is a good
alternative.

The only change I made to your suggested code was to take out the "txt"
so that it reads as:

Private Sub Subject_BeforeUpdate(Cancel As Integer)


If IsNull(Me.Subject) Then
MsgBox "A Subject is Required"
Cancel = True
End If


End Sub

But still no go, any further suggestions are appreciated. Thanks again!

Jess

 

[Guest]

I just used my own naming convention because I did not know the name of the
control. But that should not stop it from working correctly. That could
mean that subject is not really null. Try this, and if it does not work,
please post back with the error number and description, and when it happens.

If Len(Trim(Nz(Me.Subject,""))) = 0 Then

 

[Jess]

Hey Klatuu,

Thanks for helping me out with this. I tried what you suggested but I'm
still getting the same error. If you have time to download that
template
(http://office.microsoft.com/en-us/templates/TC010178391033.aspx?CategoryID=CT011366681033)
the error occurs when you try to enter a call log note before entering
the subject. The error # is 3162.

Thanks again,

Jess

 

[Guest]

I took a look at it. What is happening is that since you are moving away
from one form to another, it is trying to save the record, but the Contact ID
is required. I have not been able to find where or why that is happening.

 

[eos]

AUTO-REPLY From George Levitt

Please allow this to confirm a system receipt of your e-mail.

I am out of the office until Wednesday morning (1/12/05) and will not be
reviewing or responding to email or voicemail until that time.

I look forward to replying to your message on Wednesday.

Thanks and warmest regards, George

 

[eos]

AUTO-REPLY From George Levitt

Please allow this to confirm a system receipt of your e-mail.

I am out of the office until Wednesday morning (1/12/05) and will not be
reviewing or responding to email or voicemail until that time.

I look forward to replying to your message on Wednesday.

Thanks and warmest regards, George

 

[eos]

AUTO-REPLY From George Levitt

Please allow this to confirm a system receipt of your e-mail.

I am out of the office until Wednesday morning (1/12/05) and will not be
reviewing or responding to email or voicemail until that time.

I look forward to replying to your message on Wednesday.

Thanks and warmest regards, George

 

[eos]

AUTO-REPLY From George Levitt

Please allow this to confirm a system receipt of your e-mail.

I am out of the office until Wednesday morning (1/12/05) and will not be
reviewing or responding to email or voicemail until that time.

I look forward to replying to your message on Wednesday.

Thanks and warmest regards, George

 

[eos]

AUTO-REPLY From George Levitt

Please allow this to confirm a system receipt of your e-mail.

I am out of the office until Wednesday morning (1/12/05) and will not be
reviewing or responding to email or voicemail until that time.

I look forward to replying to your message on Wednesday.

Thanks and warmest regards, George

 

[eos]

AUTO-REPLY From George Levitt

Please allow this to confirm a system receipt of your e-mail.

I am out of the office until Wednesday morning (1/12/05) and will not be
reviewing or responding to email or voicemail until that time.

I look forward to replying to your message on Wednesday.

Thanks and warmest regards, George