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
748 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

Expert Comment

by:DatabaseMX (Joe Anderson - Access MVP)
Comment Utility
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

Expert Comment

by:DatabaseMX (Joe Anderson - Access MVP)
Comment Utility
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

Expert Comment

by:DatabaseMX (Joe Anderson - Access MVP)
Comment Utility
see attached MDB
IMED-TEST-2-MX01.zip
0
 
LVL 75

Expert Comment

by:DatabaseMX (Joe Anderson - Access MVP)
Comment Utility
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

Expert Comment

by:DatabaseMX (Joe Anderson - Access MVP)
Comment Utility
Sorry ... ignore the last post - WRONG question ...

mx
0
 
LVL 1

Author Comment

by:njmatt
Comment Utility
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

Expert Comment

by:DatabaseMX (Joe Anderson - Access MVP)
Comment Utility
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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 1

Author Comment

by:njmatt
Comment Utility
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
Comment Utility
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

Expert Comment

by:DatabaseMX (Joe Anderson - Access MVP)
Comment Utility
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
Comment Utility
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

Expert Comment

by:DatabaseMX (Joe Anderson - Access MVP)
Comment Utility
see this attached mdb
IMED-TEST-2-MX02.zip
0
 
LVL 75

Expert Comment

by:DatabaseMX (Joe Anderson - Access MVP)
Comment Utility
How are we doing ?

mx
0
 
LVL 1

Author Comment

by:njmatt
Comment Utility
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

Expert Comment

by:DatabaseMX (Joe Anderson - Access MVP)
Comment Utility
You are welcome.  Sorry I missed the code the first time around.

mx
0

Featured Post

Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

Join & Write a Comment

QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
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…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

743 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

18 Experts available now in Live!

Get 1:1 Help Now