Solved msgbox if a control on continuous subform has different values (1 Viewer)

Ihk

Member
Local time
Tomorrow, 00:24
Joined
Apr 7, 2020
Messages
280
Main form has subform. This subform is filtered some filter parameters on main form via query.
Subform is continous form.
One of the field on a subform is "AccountNo", In a single search result, if in all records under "AccountNo" are not same, I want a msgbox.
eg, Four different account number found against this search.
How to check, if value in a specific control wont match.
thanks
 

jdraw

Super Moderator
Staff member
Local time
Today, 19:24
Joined
Jan 23, 2006
Messages
15,364
What exactly links the main form to subform? Can you post the query and/or related code?
 

Ihk

Member
Local time
Tomorrow, 00:24
Joined
Apr 7, 2020
Messages
280
What exactly links the main form to subform? Can you post the query and/or related code?
Subform is unbound, not linked. (Updated Sentence:: Meaning no Master Child relationship), but its source object is Query)
Query has filer like between dates ( from main form) and one combobox filter (from main form).

Based filter selection, record is displaced in subform. One of many field is Account number.
This account number is already (or will be ) saved by the user.

Now i want at the time of filter and subform requery, if displayed result (in subform) has different account numbers (values inside AccountNo field/control), i want to notify user..
As message box.....
In this case user will decide whether should move further to print report or 1st he/she should go back to change /to make same account numbers.
 
Last edited:

Ihk

Member
Local time
Tomorrow, 00:24
Joined
Apr 7, 2020
Messages
280
Subform is unbound, not linked.
Query has filer like between dates ( from main form) and one combobox filter (from main form).

Based filter selection, record is displaced in subform. One of many field is Account number.
This account number is already (or will be ) saved by the user.

Now i want at the time of filter and subform requery, if displayed result (in subform) has different account numbers (values inside AccountNo field/control), i want to notify user..
As message box.....
In this case user will decide whether should move further to print report or 1st he/she should go back to change /to make same account numbers.
Let me correct here my 1st sentence.
**subform is not linked to main form (as Master child Relationship), but its source object is Query
 

Ihk

Member
Local time
Tomorrow, 00:24
Joined
Apr 7, 2020
Messages
280
Here I have demo db, @jdraw , it will open with Mainform with a button to another form.
This new form has filters (date, combos)
Example1: filter it as
Date= today
Company = Company1
Category = CategoryA
You will see results, under these results there is on filed "AccountNo" . For this I am looking for, if there are different values which wont match each other then user should get a message. Afterwards user will decide what next.
Example2: filter it as
Date= Today
Company = Company2
Category = categoryB
with this filter you will all search results have same Account number, so no msgbox to user and user will continue without paying attention.
Picture below has different AccountNo, so I want message here
Screenshot 2022-04-25 153151.png

Below has same AccountNo. so no message
Screenshot 2022-04-25 153228.png
 

Attachments

  • Sample Db.accdb
    2.5 MB · Views: 186

jdraw

Super Moderator
Staff member
Local time
Today, 19:24
Joined
Jan 23, 2006
Messages
15,364
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.
 

bob fitz

AWF VIP
Local time
Today, 23:24
Joined
May 23, 2011
Messages
4,717
<ihk>

How do you determine which of the account numbers needs a message.
Consider the following list:
125253031
225253030
325253031
425253030
525253032
Which rows would need a message and why?
 

Ihk

Member
Local time
Tomorrow, 00:24
Joined
Apr 7, 2020
Messages
280
<ihk>

How do you determine which of the account numbers needs a message.
Consider the following list:
125253031
225253030
325253031
425253030
525253032
Which rows would need a message and why?
user wont have to determine here on this stage, Just user should be prompted that there is variation in "account No" field.
After user will realize that, before he / she process this further, must will go back to change Account number to make it identical against that company and category and date.
 

Ihk

Member
Local time
Tomorrow, 00:24
Joined
Apr 7, 2020
Messages
280
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.
 
Last edited:

Gasman

Enthusiastic Amateur
Local time
Today, 23:24
Joined
Sep 21, 2011
Messages
14,048
So walk through the recordsetclone and if account not equal previous account, stop processing the recordset and post your message.
 

bastanu

AWF VIP
Local time
Today, 16:24
Joined
Apr 13, 2010
Messages
1,401
Or instead of a message simply use conditional formatting to change the fore or back color of the Account field if there is more than 1 (using dCount() or built-in size bar as attached).


Cheers,
 

Attachments

  • Sample DbAccount.accdb
    652 KB · Views: 180
Last edited:

Ihk

Member
Local time
Tomorrow, 00:24
Joined
Apr 7, 2020
Messages
280
Or instead of a message simply use conditional formatting to change the fore or back color of the Account field if there is more than 1 (using dCount() or built-in size bar as attached).


Cheers,
Thank you very much, this is good idea. I observed if search result "Account Field" has no difference, then also still it colours as full.
For example searching by this criteria, where there is no difference in account, everything is correct. Then user will get used to it and will ignore this as routine because 95% of time, there will be no difference in accounts.
Date= Today
Company = Company2
Category = categoryB
 

bastanu

AWF VIP
Local time
Today, 16:24
Joined
Apr 13, 2010
Messages
1,401
Than use a dCount>1 as suggested and it will only be formatted when there are two or more,

Cheers,
 

bastanu

AWF VIP
Local time
Today, 16:24
Joined
Apr 13, 2010
Messages
1,401
Here is an updated sample using dCount with a totals query grouping by AccountNo.

Cheers,
 

Attachments

  • Sample DbAccountVer2.accdb
    656 KB · Views: 162
  • Love
Reactions: Ihk

Pat Hartman

Super Moderator
Staff member
Local time
Today, 19:24
Joined
Feb 19, 2002
Messages
42,979
Is this an exercise to clean up data? If so, I would change the interface so that the first form groups the AccountNo and includes a count. Then you click on a button where you choose the correct AccountNo for the group run an update query to make everything conform.

Once you clean up the data, are you going to fix the schema to prevent future occurrences? Seems like the AccountNo belongs in the parent record rather than in the child record.
 

Ihk

Member
Local time
Tomorrow, 00:24
Joined
Apr 7, 2020
Messages
280
Here is an updated sample using dCount with a totals query grouping by AccountNo.

Cheers,
@bastanu your greatness.
Problem solved even in a more better way. Meaning formatting as well as message.
I used the same statment on filtering record.
Code:
If DCount("FlagAccounts","qryAccountCount")>1 then
msgbox "my message"
So now it give colour change as well as message. very very thankful. Kind regards,
 

Users who are viewing this thread

Top Bottom