[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2749
  • Last Modified:

Overcoming error 'You tried to assign the Null value to a variable that is not a variant data type' either by modifying design or changing form data input?

Per Image A - at the Floor combo box - when I backspace and remove the information per Image B, I get the above error message - 'You tried to assign the Null value to a variable that is not a variant data type'.  Yet if I were to click on the combo box per Image C, I go to the top and can select the blank one.  

How can I make Access understand when I backspace I'm trying to assign the blank value for now and will come back to assigning what it should be?  Is my design wrong per Image D?  Should I break the relationships so that a null value can be entered?

Please note I have the Access database sample below in a zip file...Room-Data.zip
Image-A.png
Image-B.png
Image-C.png
Room-Data.zip
Image-D.png
0
stephenlecomptejr
Asked:
stephenlecomptejr
  • 8
  • 7
  • 3
1 Solution
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
If you have a Variable Dimmed as a String, and you try to assign a Null to this variable, that is one way you will get that message.  Check your variable assignments.

mx
0
 
Dale FyeCommented:
At what point do you get the message (as soon as you delete, when you tab out of the control, when you try to save the record)?
0
 
stephenlecomptejrAuthor Commented:
fyed,

when I tab out of the control (after hitting the backspace key)

DatabaseMX,

I'm not sure what you mean because I don't have a variable dimmed as a string in the code behind.  I'm only pressing the backspace key and then tabbing forward.
0
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
Post any code behind for any events, like the Before Update event for example.

mx
0
 
Dale FyeCommented:
What is the data type for the Floors field?

If you go into the design view of tblActuals, what are the values of the "Required" and "Allow Zero Length" properties

What is the SQL string that is used as the RowSource for Floors, what is the bound column and how many columns are you displaying?
0
 
stephenlecomptejrAuthor Commented:
This is the only code behind stuff for cboFloor...
Do you also want me to post the entire code behind for the form itself though?
Private Sub cboFloor_AfterUpdate()
  If IsNull(cboFloor) = True Then
    cboFloor = 1
    DoEvents
  End If
End Sub

Private Sub cboFloor_DblClick(Cancel As Integer)
On Error GoTo Err_Something
  DoCmd.OpenForm "sfrmRoomListing", acFormDS
Exit_Something:
  Exit Sub
  
Err_Something:
  Call Error_Action(Err, Err.Description, "frmEntry @ cboFloor_DblClick", Erl())
  Resume Exit_Something
End Sub

Private Sub cboFloor_NotInList(NewData As String, Response As Integer)
On Error GoTo Err_Something

  Dim i As Integer
  Dim strSQL As String
  Dim Msg As String
  If NewData = "" Then Exit Sub     ' exit this sub if the combo box is cleared
  Msg = "'" & NewData & "' is not currently in the list." & vbCr & vbCr
  Msg = Msg & "Do you want to add it?"
  i = MsgBox(Msg, vbQuestion + vbYesNo, "Unknown Path...")
  If i = vbYes Then
    strSQL = "INSERT INTO Floorfinish Class ([floorfin]) values ('" & NewData & "')"
    CurrentDb.Execute strSQL
     Response = acDataErrAdded
  Else
    Response = acDataErrContinue
  ' Display a customized message.
    MsgBox "Please try again."
  End If
Exit_Something:
  Exit Sub
  
Err_Something:
  Call Error_Action(Err, Err.Description, "frmEntry @ cboFloor_NotInList", Erl())
  Resume Exit_Something
End Sub

Open in new window

0
 
stephenlecomptejrAuthor Commented:
I tried deleting them all and it still gives me the error.
Do you have time to look at the below sample and try the backspace itself?
Room-Data.zip
0
 
stephenlecomptejrAuthor Commented:
fyed,

Hope you can see the images...
required.png
required2.png
0
 
stephenlecomptejrAuthor Commented:
Perhaps floor table could help.
floor-table.png
0
 
Dale FyeCommented:
put a breakpoint at the top of the NotInList code, and try it again.

My guess is that it has to do with this line of code:

If NewData = "" Then Exit Sub     ' exit this sub if the combo box is cleared

Do you encounter the same problem if you highlight the entire contents of the combo and hit Delete, then tab out?
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
Guys ... there error has nothing to do with the AU or Not In List even. The error is being rendered in the Form Error event (see image) ... error 3162.  Now why, I don't know yet.  And it happens in several combo boxes.

mx
Capture1.gif
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
TYPOS:

"there error has nothing to do with the AU or Not In List even. "
>the error has nothing to do with the AU or Not In List event.
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
You issue is resolved by setting the Form's Recordset Type to Dynaset (Inconsistent Updates) - see image.

You immediately need to rethink the form Record Source, which has upwards of FIFTEEN tables!  You goal s/b to have ONE table.  This is why Inconsistent Updates 'fixes' the problem, although this is a somewhat dangerous setting.

mx
Capture1.gif
0
 
stephenlecomptejrAuthor Commented:
DatabaseM,

Please note that this database has a lot of base tables... so you are saying in this case for the design its not good to have it not normalized because of the so many?

Image-D.png
0
 
stephenlecomptejrAuthor Commented:
DatabaseMX,

Also, when I change setting the Form's Recordset Type to Dynaset (Inconsistent Updates) - I notice it works but then as I update the combo box- the text box next to it fails to update.

So for each combo box I would have to do an AfterUpdate event updating the text boxes.
That's a lot of work!  =(

But if I have to do it - I will.
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
Well, I'm saying that you main form (or any form really) should have only one table ... the main table tblActuals in this case. There is no need for all those other tables.  If you are trying to display data from those tables, there is *always* another way to do so.  But, that's another subject really.

You can temporarily resolve the current issue ... as I showed above.

mx
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
"I notice it works but then as I update the combo box- the text box next to it fails to update."

Well, again ... any multiple table driven RecordSource is going to be problematic ... in general.

Sadly, I  don't have time at the moment to suggest a revamping ... but as you can see, the current design is causing ... issues :-)

mx
0
 
stephenlecomptejrAuthor Commented:
Much appreciation.
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

  • 8
  • 7
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now