Combo Box Error - Microsoft Access Forum


	Back to Access Forum Comments List

Combo Box Error

William Dowler

4 years ago

Hi guys,
I have three tables PersonT, TeamT and PersonTeamT.
PersonTeamT is the resolution of a many to many relationship between PersonT and TeamT.
I have a Form PersonF containing a subform PersonTeamListF.
These forms are joined by the common field PersonT.PersonId.
On the subform I have a combo field based on the TeamT table to select a Team for a particular Person.
All this works fine, however, if I select an entry in the combo box and then try cancel that entry (highlight and delete or just fill with spaces" and tab out of the combo box I get the following error message:
"You tried to assign the null value to a variable that is not a Variant data type"
I have no mandatory fields, no variables defined, no input masks or default values.
Worst of all I can't trap this error in any of the events for the combo box.
Any help would be vastly appreciated!!!!

	Adam Schwanz @Reply 4 years ago
What if you just push the escape key? Does it undo it? Is there any other code like any fields being assigned values with the combobox after update event?

	William Dowler OP @Reply 4 years ago
Adam, I still get the error message and the escape key populates the combo with the original value. There is no other code associated with the subform.

	Adam Schwanz @Reply 4 years ago
Just checking your setup. Main form is based on PersonT and subform is based on PersonTeamT and combo bound to PersonTeamT TeamID field? Linked field is PersonID? The junction table has PersonTeamID (primary key) and PersonID and TeamID as foreign keys?

William Dowler OP @Reply

4 years ago

Adam,
Almost. The subform is based on a query PersonTeamSubQ that joins the PersonTeamT table to the TeamT table so that I can bring in the TeamName and not just the TeamId. The combo box row source is TeamT and control source is TeamId (and I think the problem lies here somewhere). Do I have to specifically point the Control Source to the PersonTeamT table or the PersonTeamSubQ query somehow. I have a mental block on this one!! Thanks for the help....

	Adam Schwanz @Reply 4 years ago
Can you try with just using the table instead of the query and see if it works? Can you also post the query SQL here? Wondering if you have some JOIN that is causing issues when you try to change it.

William Dowler OP @Reply

4 years ago

Adam,
Using the PersonTeamT table as the Control Source cured the problem.
This was the SQL I used before:
SELECT PersonTeamT.TeamId, TeamT.TeamName, PersonTeamT.EffectiveDate, PersonTeamT.EndDate, PersonTeamT.IsPrimary, PersonTeamT.IsLead, PersonTeamT.PersonId
FROM TeamT INNER JOIN PersonTeamT ON TeamT.TeamId = PersonTeamT.TeamId;

I don't understand what the problem was using the query but thanks for all the help, I truly appreciate it.

This thread is now CLOSED. If you wish to comment, start a NEW discussion in Access Forum.

Next Unseen