Search all Subform's Combo boxes for a string

[mcupito]

Hi all,

I have a Form with the command button 'Submit.' I have a subform inside the form where users are inputting date, company and hours. If they are unsure of the company, they are allowed to enter a value in the combo box of '?????' .. Now, there are going to be multiple records in the subform, some may have '?????' some may not.

I need a MsgBox to pop up, when they click 'Submit' to ensure all Comboboxes with '?????' are changed, this prohibiting them from being submitted.

I have the code:

If Me!MainForm!ServiceLOBCbx.Text = "?????" Then
    MsgBox ("Enter Correct LOB")

However, it only works if the record is still being edited, not if the user has clicked on a new row.

Is there a way to make the submit button search ALL records in each combobox for that value?

Note: It will always be ????? .. not '?' or '??'

Thanks!!

[jimatqsi]

So what's the point of this? " If they are unsure of the company, they are allowed to enter a value in the combo box of '?????' " How does that help them at all if you're just going to reject the entry?

Jim

[mcupito]

Jim,

If the user is unsure of the correct company at the time, they may keep the records on that form, but not yet submit. The ????? are acting as a place holder until more information becomes available. Once they hit 'Submit', though, there should not be allowed to have any values of '?????' to be submitted.

[zmbd]

You don't need the "?????" in the field at all; leave the field as an empty value.

In the code for your submit control, open a record set on the holding table that pulls only those records with the Null/Empty field - the SQL would look something like

SELECT tbl_example.[PK], tbl_example.[needdata]
FROM tbl_example
WHERE (((tbl_example.[needdata]) Is Null));
 

Check for at least one record in the recordset. If any are found then set your (sub)form's filter to pull up those records with an empty field.

[mcupito]

The '?????' was an end-user specification, I had no say in it. I assigned an ID for the '?????', searched for that value with the StaffID when Submit was clicked, and if the lot was 0, I let them proceed, if not I made them change it.