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
754 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

 
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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
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…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

717 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