You tried to assign the null value to a variable that is not a variant data type error message on Linked Combo Boxes

I have attached the database I am working on.  

On frmProfile, Profile I have a combo box (cmbExemptStatus) that when selected, filters a combo box (cmbBenefitsStatus) on frmProfile, Benefits.

The filter currently works correctly, however, when I attempt to make a selection in cmbBenefitsStatus, I get the error message:  "You tried to assign the null value to a variable that is not a variant data type."

I've looked through the handful of posts on this topic but to no avail.  
IMED-TEST-2.mdb
LVL 1
njmattAsked:
Who is Participating?
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Connect With a Mentor Database ArchitectCommented:
Here ... try this code now - and remember to change the combo box settings to what I described:

Private Sub cmbExemptStatus_Change()
Dim sql
If cmbExemptStatus = "1" Then
    sql = "SELECT [benefits_dependant_status.benefits_dependant_status_key],  [benefits_dependant_status].[status_desc] FROM [benefits_dependant_status] WHERE [benefits_dependant_status].[status_desc] LIKE 'Salaried*'"
    Me.frmProfile_benefits_subform.SetFocus
    Me.frmProfile_benefits_subform.Form.cmbBenefitsStatus.RowSource = sql
ElseIf cmbExemptStatus = "2" Then
    sql = "SELECT [benefits_dependant_status.benefits_dependant_status_key], [benefits_dependant_status].[status_desc] FROM [benefits_dependant_status] WHERE [benefits_dependant_status].[status_desc] LIKE 'Hourly*'"
    Me.frmProfile_benefits_subform.SetFocus
    Me.frmProfile_benefits_subform.Form.cmbBenefitsStatus.RowSource = sql
ElseIf cmbExemptStatus = "3" Then
    sql = "SELECT [benefits_dependant_status.benefits_dependant_status_key], [benefits_dependant_status].[status_desc] FROM [benefits_dependant_status] WHERE [benefits_dependant_status].[status_desc] LIKE 'Hourly*'"
    Me.frmProfile_benefits_subform.SetFocus
    Me.frmProfile_benefits_subform.Form.cmbBenefitsStatus.RowSource = sql
End If
Me.ProfileTab.Pages(0).SetFocus

End Sub
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
It's because you are trying to assign a Text value from the Combo to a Numeric field in the table Benefits.

mx
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
Change the combo box SQL to this:

SELECT benefits_dependant_status.benefits_dependant_status_key, benefits_dependant_status.status_desc
FROM benefits_dependant_status
WHERE (((benefits_dependant_status.status_desc) Like 'Salaried*'));

Then set these combo box settings:

Column Count  2
Column Widths  0;2
Bound Column   1        Not 2

mx
0
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
see attached MDB
IMED-TEST-2-MX01.zip
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
SELECT Table1.*
FROM Table1 LEFT JOIN Table2 ON Table1.FIELD1 = Table2.FIELD2
WHERE (((Table2.FIELD2) Is Null));


Where FIELD2 is your ID field(s)

mx
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
Sorry ... ignore the last post - WRONG question ...

mx
0
 
njmattAuthor Commented:
I appreciate your response.  However, the example you sent does not work correctly for me.  And when I edit the column settings according to your first post, the fields in the combo boxes are pulling the ID Key, not the descriptions.
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
On the Benefits tab, I can select a Benefits Plan without any error ... and, it displays the Description in the combo.  no problem.

What is happening on your end ?  Are you using IMED-TEST-2_MX01.mdb ?

mx
0
 
njmattAuthor Commented:
Yes, I'm using your Database file.  When I change the Exempt Status on the Profile Tab, the Benefits Plan drop down is blank...
0
 
njmattAuthor Commented:
I changed If cmbExemptStatus = "1" to  If cmbExemptStatus = "Exempt", however, when Non-Exempt and Non-Employee are selected, the only option in the Benefits drop down is for "Salaried", it doesn't change to "Hourly" options.
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
Ok ... I see the problem ... you need to fix the SQL's here:

Private Sub cmbExemptStatus_Change()
Dim sql
If cmbExemptStatus = "1" Then
    sql = "SELECT [benefits_dependant_status].[status_desc] FROM [benefits_dependant_status] WHERE [benefits_dependant_status].[status_desc] LIKE 'Salaried*'"
    Me.frmProfile_benefits_subform.SetFocus
    Me.frmProfile_benefits_subform.Form.cmbBenefitsStatus.RowSource = sql
ElseIf cmbExemptStatus = "2" Then
    sql = "SELECT [benefits_dependant_status].[status_desc] FROM [benefits_dependant_status] WHERE [benefits_dependant_status].[status_desc] LIKE 'Hourly*'"
    Me.frmProfile_benefits_subform.SetFocus
    Me.frmProfile_benefits_subform.Form.cmbBenefitsStatus.RowSource = sql
ElseIf cmbExemptStatus = "3" Then
    sql = "SELECT [benefits_dependant_status].[status_desc] FROM [benefits_dependant_status] WHERE [benefits_dependant_status].[status_desc] LIKE 'Hourly*'"
    Me.frmProfile_benefits_subform.SetFocus
    Me.frmProfile_benefits_subform.Form.cmbBenefitsStatus.RowSource = sql
End If
Me.ProfileTab.Pages(0).SetFocus
End Sub


So that they include the benefits_dependant_status_key - like so (generic):

SELECT benefits_dependant_status.benefits_dependant_status_key, benefits_dependant_status.status_desc
FROM benefits_dependant_status
WHERE (((benefits_dependant_status.status_desc) Like 'Hourly*'));

mx
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
see this attached mdb
IMED-TEST-2-MX02.zip
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
How are we doing ?

mx
0
 
njmattAuthor Commented:
Thank for all your help MX.  Was editing the column setting on the Exempt Status combo box, instead of the Benefits Status combo box.  That works perfectly!
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
You are welcome.  Sorry I missed the code the first time around.

mx
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.