I have a form, Case Entry which contains a combo box called cmbInvestigator and a Case Number field (textbox). Also on the Case Entry form is a subform called Details Sub, which contains a Case Name field (textbox). In a perfect scenario, when an investigator opens a case, they select their name from the combo box and a Case Number is assigned; they then move to the subform and type in a Case Name.

Occasionally, the investigator forgets to enter their name and they go right to the Case Name field and enter that information. While this method assigns the Case Number, the investigator's name is still missing from the combo box and it has to be entered manually via the supporting table.

What I need is a way to ensure that the combobox is used prior to any other information being entered. I tried setting the Required field to Yes and a Validation rule of Is Not Null in the table supporting the Case Entry form. Unfortunately, this doesn't work.

I also tried this code in the BeforeUpdate event of Case Entry but still no luck.


Another thing I tried was using the GotFocus event on the Case Name textbox in the Details Sub subform using


However, when I click in the Case Name textbox, I get an error of
"Run-time error 424:, Object required" and I have no idea what this means.

Is there a way I can set this up so that no matter what the user tries, they still have to select the Investigator's name from the combo box BEFORE they do anything else?

Any ideas?

Kevin

Have you considered locking the controls until they've selected a name from the combo box?

Hi Doug,

Thanks for looking at this.

Locking the controls, not sure how to do that. Would I have to lock every control on the subform or could I lock the subform itself?

Kevin

Locking the subform would certainly be easiest.

Doug,


With that being the case, how do I lock the subform? My VBA skills are limited to what I've seen on this forum, and even that is hit or miss when I try to use them.

Kevin

Associate the following code with the Current event of the parent form:



(Replace NameOfSubformControl with the correct name. Note that depending on how you added the form as a subform, the name of the subform control may be different than the name of the form being used as a subform!)

Associate the following code with the AfterUpdate event of the combo box:

Doug,

Thank you so much for your help with this!! Your code worked perfectly; I didn't know it was that simple.

Thank you for your patience.

Kevin