ComoBox Back to Null

[shhyde62]

I have a form with a ComboBox_1 to choose a “System”, (three ComoBoxes) ComboBox_2, ComboBox_3 & ComboBox_4 to choose a practice from the System selected shown in ComoBox_1.

When the form is first opened all 4 ComboBoxes are Null – once you choose the System boxes 2, 3 & 4 will show the practices listed (dropdown) for that System.

If you choose all three practices (Boxes 2, 3 & 4 – or even one by mistake) and later change the System (which then changes the options for Practices in Boxes 2, 3 & 4 – works fine unless you need to delete one of the Practices (say you had 2 picked, and only need 1 or 3 and only need 2).

I can get the program to make the unwanted Boxes Null. But when I try to save or leave the Form I get the following message.

The Microsoft Access database engine cannot find a record in the table ‘csgn25_PracticeSystem” with key matching field(s)”.

There was a similar thread a few years back – but didn’t seem to have an answer.

Can anyone help with this problem.

Thanks,

 

[CJ_London]

Can anyone help with this problem

Would need to see your code since that is where the problem lies

 

[shhyde62]

Would need to see your code since that is where the problem lies

I have one each for Boxes 2 ,3, & 4 - so it's trying to pull from the query - when it sees there is not a blank option is when it gets confused.
Hope this is what you wanted.

 

[The_Doc_Man]

It is a matter of how you make the control empty. If you try to set an arbitrary value to the box (even a value of NULL), you run into the problem that you don't have a null value for a record in whatever you are using as a .RowSource, so of COURSE you can't find a matching record.

It is possible to implement a control.Undo action (as opposed to a form.Undo action). The UNDO for a control doesn't make the combo box value hull. Instead it makes all of that control's row .Selected flags false. It also makes the .ItemSelected property -1, indicating "no selection." The key is not to have a null selection by asserting the combo value to be null. It is to assert that there is no selection. Which - as long as nothing is set to Mandatory status - won't hurt anything.

NOTE: Not all controls support an .Undo action so look up that control type to see its condoned methods. Combos and List Boxes CAN use .UNDO but I don't recall that a text box uses it.

 

[shhyde62]

This seems to work if you haven’t closed the form. If you close the form and decide to go back and make changes, this doesn’t work.
Thanks for any ideas.
I’m at a standstill.
thanks.

 

[The_Doc_Man]

This seems to work if you haven’t closed the form. If you close the form and decide to go back and make changes, this doesn’t work.

If you close and reopen a form, it should have no selections made automatically on ANY control - primary or secondary combo boxes included - unless you defined a default value that happens to be in the range of the .RowSource for the control in question. Therefore the actual structure and relationships are unclear here.

I can get the program to make the unwanted Boxes Null. But when I try to save or leave the Form I get the following message.

The Microsoft Access database engine cannot find a record in the table ‘csgn25_PracticeSystem” with key matching field(s)”.

The question has to be "what method do you use to make the unwanted boxes null?" ... followed by asking "So ... you open the form. You try to drop one of the unwanted practices. You close the form. So... what steps are next in order for you to observe the problem, and what does the problem look like?" Don't say "Doesn't work." Tell us what you SEE (or don't see) that proves to you that it doesn't work.

What I suspect here is that you HAD some practices linked to that first combo but you found a case where you didn't want ALL of the things linked via that combo for that one specific case and you picked the wrong way to remove it. Doing the removal the wrong way led to "breaking" a relationship which in turn led to the error your reported.