Solved

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

Posted on 2008-10-06
15
752 Views
Last Modified: 2013-11-28
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
0
Comment
Question by:njmatt
  • 11
  • 4
15 Comments
 
LVL 75
ID: 22653875
It's because you are trying to assign a Text value from the Combo to a Numeric field in the table Benefits.

mx
0
 
LVL 75
ID: 22653884
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
 
LVL 75
ID: 22653931
see attached MDB
IMED-TEST-2-MX01.zip
0
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
LVL 75
ID: 22653946
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
 
LVL 75
ID: 22653977
Sorry ... ignore the last post - WRONG question ...

mx
0
 
LVL 1

Author Comment

by:njmatt
ID: 22654048
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
 
LVL 75
ID: 22654075
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
 
LVL 1

Author Comment

by:njmatt
ID: 22654178
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
 
LVL 1

Author Comment

by:njmatt
ID: 22654230
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
 
LVL 75
ID: 22654232
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
 
LVL 75

Accepted Solution

by:
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform) earned 500 total points
ID: 22654245
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
 
LVL 75
ID: 22654259
see this attached mdb
IMED-TEST-2-MX02.zip
0
 
LVL 75
ID: 22654469
How are we doing ?

mx
0
 
LVL 1

Author Comment

by:njmatt
ID: 22658929
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
 
LVL 75
ID: 22661168
You are welcome.  Sorry I missed the code the first time around.

mx
0

Featured Post

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

829 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question