Do you have a clear statement of requirements?
I see ID_Orders, OrderNo, InvoiceNumber, ID_Invoice, AccountNo.
There must be some business rules connecting/relating these to clarify the structure.
Yes there is business rule, I did not explain this here not to confuse some one with so much detail. So just pointed out for a message if different values found from each other.
Briefly to your question:
Sorry I could not put here every thing, because solution has more than 10+Tabels, 20+forms+25+queries.
On this form, 1st of all results will be filtered based on criteria, this criteria ideally 95% of time will bring same account numbers. but sometimes may differ. ---
because next step for user is to print report (invoice), which will have this result (from subform), Each report (sheet of invoice) should be against one account.
Breifly
1) These account number have funds, those fund are also calculated.
2)Button on this Form: Filtered results of this form, by button press will be assigned an Invoice number (which will be saved in order table)
3) Button on this Form: will show a report of these results, ... Each report will be given an invoice number
4) each of these reports must have to have a single Account number. (report - a kind of order sheet) This account number will be on the header of the report, and results will be in tabulated form below. Which will have one specific category as well as specific category.
many more.... can explain later if needed.
So these all steps are after the search result is filtered on subform, If the result has different "account number" In that case I dont want that user should move further to press button to do further processing (explained above -- like assigning Invoice number etc.
Rule of thumb.... A report (invoice) should have same Account number (in header) against searched (filtered) company & category. If not, user should be prompted not to move further (to assign invoice no., to print report etc)
I hope I was able to explain a bit.