Solved Cannot handle 2 colums combobox error "You tried to asign the Null value to a variable that is not a Variant data Type."

[Romio_1968]

Hello guys,

I have an annoying error that bothers me:

On a continuos subform i set a Combo Box named "Author_ID" with the Row Source on a query:
SELECT Author.Author_ID, Author.Author_Name FROM Author ORDER BY Author.Author_Name;
The Column Property is set to 2, and the Column Withs are 0,5

When landing on the combo, the user chooses value from a list or he may enter a new one, as well.
With each value he is choosing,a record is saved in a table, and the continuous form grows

The problem comes if the user is trying to delete (Backspace or del) the content of the Control. It doesn't mater if it is a prior saved field or the new one, thet is not yet saved in the table. This delition may be accidental. The form have a mecanism to delete saved records or undo de last.

If he clears the contros and basicaly the value stored in id is Null, or empty, or zero len (i don't know, probably Null, given the error that is trigerred), the message "You tried to asign the Null value to a variable that is not a Variant data Type." is trigered. The only way to get out from that trap is to hit twice the Esc. This will Undo the operation he is trying.

I want to get out from the trap in other way then hitting the Essc key.
If the record was saved, it must remain as it was. Is it is a new record, he wrote something in the control and he changed his mind, the control should remai also as it was.

Thank you


Late edit... it seems that I was indicating a wrong Error code. By trapping 3162 the issue was fixed.
Thank you

Solution:


Private Sub Form_Error(DataErr As Integer, Response As Integer)

If DataErr = 3162 Then
If Me.ActiveControl.Name = "Author_ID" Then
If Len(Me.Author_ID) Then
MsgBox "No null value allowed", vbExclamation, "Atentie!"
Response = acDataErrContinue
Me.Undo
End If
End If
End If

End Sub

 

[ebs17]

The form have a mecanism to delete saved records or undo de last.

You should analyze this.

SQL and thus table fields and controls linked to them derived from them can handle NULL very well. VBA is different. Only variables of the Variant data type can be assigned NULL there.

 

[Romio_1968]

You should analyze this.

SQL and thus table fields and controls linked to them derived from them can handle NULL very well. VBA is different. Only variables of the Variant data type can be assigned NULL there.

The solution is in the post as late edit.
Thank you

 

[Pat Hartman]

This is a training issue. When a user types something in a control and wants to remove it - using the backspace is the WRONG technique. Use the esc key. The issue is when you use the backspace, the control remains dirty but it contains a ZLS which may be invalid or at least it should be. But when you use the esc key, Access removes all evidence of modification and the control is NOT left in the dirty state.

In the BeforeUpdate event of the form, you can change any ZLS values to null, with or without an error message if that makes sense.

 

[Romio_1968]

It may be a training issue indeed. But trust me, whe the user can do a mistake, he will do it twice. And then he will repeat the procedure twenty times, crying that "this stupid thin does not work, Boss!" So an untrapped error wil finally chase you...

 

[ebs17]

Assigning NULL to a variable (<> Variant) isn't something that just happens out of the blue, it was brought about programmatically. You can check something like this before an error occurs (there are drivers who not only drive on hearing, but also on sight).

You can also annoy a user with messages in the result of a test.

 

[Romio_1968]

ebs17, the issue was fixed with the code inside the post. The thread is marked as Solved. I tried a lot before asking for help, even did a test like that, but I messed the Error Code so that was the main reason I coldnțt capture the error. The issue comes from the fact that the Combo is based on two columns of the query and they are not of the same data type. One is numerical and one is text. The numerical don't like the Null values. No Variant is used.

 

[Pat Hartman]

It may be a training issue indeed. But trust me, whe the user can do a mistake, he will do it twice. And then he will repeat the procedure twenty times, crying that "this stupid thin does not work, Boss!" So an untrapped error wil finally chase you...

I guess you didn't read my entire response.

 

[Romio_1968]

Pat Harman, true.
See... the bad behaviours are stronger the us.
Skipped the last sentence. I did not use any Msg

"In the BeforeUpdate event of the form, you can change any ZLS values to null, with or without an error message if that makes sense."
This just helped me somewhere else. Thank You.

 

[gemma-the-husky]

It may be a training issue indeed. But trust me, whe the user can do a mistake, he will do it twice. And then he will repeat the procedure twenty times, crying that "this stupid thin does not work, Boss!" So an untrapped error wil finally chase you...

But you could check that the value entered is a zls, and give the user appropriate advice and resolution, on the lines that @Pat Hartman mentioned.