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
749 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
 
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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
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 - Access MVP) 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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

Suggested Solutions

I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

929 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now