If statement that loops through all records in a table and add a message if criteria

[dunlapc23]

Hello Everyone,

This is my first time posting so please bear with me.

In a table I have the following fields:

Proj_Number
Driver_ID
Exception_Reason

My construction managers use this database to scope their jobs. The construction manager will enter the Project Number and select a variety drivers but some drivers cannot be used with one another. What I am looking for is a vba code that will loop through each project and each driver in the driver_ID field and add a message to the "Reason" field when one, two or however many mutually exclusive drivers are selected.

Example:

If Driver_ID="AN01" and Driver_ID="AN02" Then
  Me.Exception_Reason = "These drivers are mutually exclusive"
End If
 

Thank you for all of your help!

[twinnyfo]

dunlapc23,

Please provide a little more explanation. What are the criteria for drivers being mutually exclusive? That they are different drivers?

Concerning your table, you you provide a sample of your data?

Is it similar to this:

Proj_Number  Driver_ID  Exception_Reason
1            AN01       None
1            AN02       Mutually Exclusive
2            AN01       None
3            AN02       None

[dunlapc23]

Hi Twinnyfo,

On a project you cannot use certain drivers together meaning if you enter AE01 you cannot use AE02 or AE05 or AE06.

Driver_ID Exception_Reason
AE01 Normally not taken with AE02/05/06_ 02/20/2013

So if the construction manager enters AE01 with a qty greater than 0 and then enter another record using AE05 with a qty greater than 0 then a message should pop up under AE01 "Normally not taken with AE02/05/06_ 02/20/2013".

Here is a list of all of my mutually exclusive drivers:

• AE01 Normally not taken with AE02/05/06_ 02/20/2013
• AE02 Normally not taken with AE01/03/05/06_ 02/20/2013
• AE03 Normally not taken with AE02/05/06_ 02/20/2013
• AE05 Normally not taken with AE01/02/03_ 02/20/2013
• AE06 Normally not taken with AE01/02/03_ 02/20/2013
• AN06 Should not used with Driver CB09_ 02/20/2013
• AN10 Normally not taken with AN01/02_ 08/22/2013
• AN12 Normally not be used with AN01 or AN02_ 02/20/2013
• AN14 Normally not be used with AN01 or AN02_ 02/20/2013
• AN19 Quantity normally not greater than 12_ 08/22/2013 ; Normally not used with AN70,AN71,AN72,AN73,AN74,AN75,AN78,AN80,AN81_03/03/2014
• AN20 Normally not be used with AN01, AN02,AN18,AN19_ 03/03/2014
• AN25 Quantity normally not greater than 6_ 08/13/2014 ; Should not be used with AN19 when an RRH, TMA or Diplexer are in the same position _08/13/2014
• AN26 Quantity normally not greater than 6_ 08/13/2014 ; Should not be used with AN19 when an RRH, TMA or Diplexer are in the same position _08/13/2014
• AN80 Should not be taken with AN70/AN71_ 11/01/2013
• CB08 Normally not taken with CB03/CB04/CB05/CB06/CB07_ 11/01/2013
• CM08 Quantity normally not greater than 1_ 02/14/2014 ; Should not be used with ZN05_11/01/2013
• CM09 Quantity normally not greater than 1_ 02/14/2014 ; Should not be used with DE01_02/15/2014
• CM10 Quantity normally not greater than 1_ 08/13/2014 ; Should not be used with GE02,GE03,GE04_08/13/2014
• CN07 Normally not be used with any conduits drivers_ 02/20/2013
• DE03 Driver quantity should not be greater than 1_ 02/20/2013 ; Normally not use with AE Drivers_08/19/2013
• DE04 Normally not use with AE Drivers_ 08/19/2013
• DE06 Driver quantity should not be greater than 1_ 02/20/2013 ; Normally not taken with DE07,DE12,DE13_02/24/2014
• DE07 Driver quantity should not be greater than 1_ 02/20/2013 ; Normally not taken with DE06,DE12,DE13_02/24/2014
• DE12 Quantity normally not greater than 1_ 02/20/2013 ; Normally not taken with DE06,DE07_02/24/2014
• DE13 Normally not taken with DE06,DE07_ 02/24/2014
• DL04 Quantity should not be greater than one on a given revision_ 02/20/2013 ; Normally not taken with DL14/DL15_11/01/2013
• DL05 Quantity should not be greater than one on a given revision_ 11/01/2013 ; Normally not taken with DL14/DL15_11/01/2013
• DL06 Quantity should not be greater than one on a given revision_ 11/01/2013 ; Normally not taken with DL14/DL15_11/01/2013
• LS01 Normally LS01/02/04/05/06 are mutually exclusive_ 02/20/2013
• LS02 Normally LS01/02/04/05/06 are mutually exclusive_ 02/20/2013
• LS04 Normally LS01/02/04/05/06 are mutually exclusive_ 02/20/2013
• LS05 Normally LS01/02/04/05/06 are mutually exclusive_ 02/20/2013
• LS06 Normally LS01/02/04/05/06 are mutually exclusive_ 02/20/2013
• LS10 Quantity normally not greater than 1_ 04/23/2013 ; Should not be used with LS01,LS02,LS04,LS05,LS06, LS07,LS08,LS09,LS11_04/23/2013
• LS11 Quantity normally not greater than 1_ 04/23/2013 ; Should not be used with LS01,LS02,LS04,LS05,LS06, LS07,LS08,LS09,LS10_04/23/2013
• PW01 Driver quantity should not be greater than 1_ 02/20/2013 ; Normally not taken with PW02,PW03,PW04_01/01/2012
• PW02 Driver quantity should not be greater than 1_ 02/20/2013 ; Normally not taken with PW01,PW03,PW04_01/01/2012
• PW03 Driver quantity should not be greater than 1_ 02/20/2013 ; Normally not taken with PW01,PW02,PW04_01/01/2012
• PW04 Driver quantity should not be greater than 1_ 02/20/2013 ; Normally not taken with PW01,PW02,PW03_01/01/2012
• PW15 Quantity normally not greater than 750_ 05/02/2014 ; Should not be used with PW14, TL05_05/01/2013
• PW20 Should not be used with PW22_ 02/20/2013
• PW21 Should not be used with PW22_ 01/25/2013
• PW28 Quantity normally not greater than 1500_ 04/23/2013 ; Should not be used with PW14_04/23/2013
• PW29 Quantity normally not greater than 1_ 08/13/2014 ; Should not be used with PW18,PW19,PW20,PW21_08/13/2014
• PW30 Quantity normally not greater than 1_ 08/13/2014 ; Should not be used with PW18,PW19,PW20,PW22_08/13/2014
• RG01 Driver quantity should not be greater than 1_ 02/20/2013 ; Should not be used with RG02/RG03/RG04/RG05_11/01/2013
• RG02 Driver quantity should not be greater than 1_ 02/20/2013 ; Should not be used with RG01/RG03/RG04/RG05_11/01/2013
• RG03 Driver quantity should not be greater than 1_ 02/20/2013 ; Should not be used with RG01/RG02/RG04/RG05_11/01/2013
• RG08A Driver quantity should not be greater than 1_ 08/04/2014 ; RG08A should not be used with Raw Land NSB_08/06/2014
• TC03 Should not be used with TC04_ 04/26/2013
• TL08 Quantity normally not greater than 500_ 04/23/2013 ; Should not be used with TL05_04/23/2013
• TS02 Driver quantity should not be greater than 1_ 02/20/2013 ; Normally TS02/03/04/05/06/07/08 are mutually exclusive_02/20/2013
• TS03 Driver quantity should not be greater than 1_ 02/20/2013 ; Normally TS02/03/04/05/06/07/08 are mutually exclusive_02/20/2013
• TS04 Driver quantity should not be greater than 1_ 02/20/2013 ; Normally TS02/03/04/05/06/07/08 are mutually exclusive_02/20/2013
• TS05 Driver quantity should not be greater than 1_ 02/20/2013 ; Normally TS02/03/04/05/06/07/08 are mutually exclusive_02/20/2013
• TS06 Driver quantity should not be greater than 1_ 02/20/2013 ; Normally TS02/03/04/05/06/07/08 are mutually exclusive_02/20/2013
• TS07 Driver quantity should not be greater than 1_ 02/20/2013 ; Normally TS02/03/04/05/06/07/08 are mutually exclusive_02/20/2013
• TS08 Driver quantity should not be greater than 1_ 02/20/2013 ; Normally TS02/03/04/05/06/07/08 are mutually exclusive_02/20/2013
• TS30 Quantity normally not greater than 36_ 02/20/2013 ; Normally not taken with TS01,TS02,TS03,TS04,TS05,TS06,TS07,TS08TS26,TS27,T S28,TS29,TS56_03/03/2014
• TS50 Quantity normally not greater than 3_ 04/23/2013 ; Should not be used with TS51,TS52,TS53,TS54,TS55_04/23/2013
• TS51 Quantity normally not greater than 3_ 04/23/2013 ; Should not be used with TS50,TS52,TS53,TS54,TS55_04/23/2013
• TS52 Quantity normally not greater than 3_ 04/23/2013 ; Should not be used with TS50,TS51,TS53,TS54,TS55_04/23/2013
• TS53 Quantity normally not greater than 1_ 04/23/2013 ; Should not be used with TS50.TS51,TS52,TS54,TS55_04/23/2013
• TS54 Quantity normally not greater than 1_ 04/23/2013 ; Should not be used with TS50,TS51,TS52,TS53,TS55_04/23/2013
• TS55 Quantity normally not greater than 1_ 04/23/2013 ; Should not be used with TS50,TS51,TS52,TS53,TS54_04/23/2013
• TW01 Quantity normally not greater than 200_ 02/20/2013 ; Should not be used with TW04/TW05/TW06/TW07/TW08/TW13/TW14_11/01/2013
• TW02 Quantity normally not greater than 300_ 02/20/2013 ; Should not be used with TW04/TW05/TW06/TW07/TW08/TW13/TW14_11/01/2013
• TW03 Quantity normally not greater than 500_ 02/20/2013 ; Should not be used with TW04/TW05/TW06/TW07/TW08/TW13/TW14_11/01/2013
• TW08 Quantity normally not greater than 12_ 02/20/2013 ; Should not be used with TW06,TW07_03/03/2014
• TW15 Quantity normally not greater than 10_ 02/20/2013 ; Should not be used with TW09.TW10,TW11,TW12_08/22/2013
• TW17 Quantity normally not greater than 3_ 08/13/2014 ; Should not be used with TW01,TW02,TW03,TW04,TW05,TW06,TW07,TW08,TW21,TW22_ 08/13/2014
• TW18 Quantity normally not greater than 1_ 08/13/2014 ; Should not be used with TW01,TW02,TW03,TW04,TW05,TW06,TW07,TW08,TW20_08/13/2014
• TW19 Quantity normally not greater than 1_ 08/13/2014 ; Should not be used with TW01,TW02,TW03,TW08_08/13/2014
• TW20 Quantity normally not greater than 1_ 08/13/2014 ; Should not be used with TW01,TW02,TW03,TW18_08/13/2014
• TW21 Quantity normally not greater than 3_ 08/13/2014 ; Should not be used with TW01,TW02,TW03,TW17,TW22_08/13/2014
• TW22 Quantity normally not greater than 3_ 08/13/2014 ; Should not be used with TW01,TW02,TW03,TW07,TW17,TW21_08/13/2014
• TW23 Quantity normally not greater than 1_ 08/13/2014 ; Should not be used with TW01,TW02,TW03,TW05,TW18_08/13/2014
• TW70 "Quantity normally not greater than 6_ 08/22/2013 ; Should not be used with AN70/AN71/AN72/AN73/
• AN75/AN78/AN80/AN81_11/01/2013"
• TW71 "Driver quantity should not be greater than 1_ 02/20/2013 ; Should not be used with AN70/AN71/AN72/AN73/
• AN75/AN78/AN80/AN81_11/01/2013"
• TW73 "Quantity normally not greater than 500_ 02/20/2013 ; Should not be used with AN70/AN71/AN72/AN73/
• AN75/AN78/AN80/AN81/TW70/TW71_11/01/2013"
• TW74 "Quantity normally not greater than 300_ 02/20/2013 ; Should not be used with AN70/AN71/AN72/AN73/
• AN75/AN78/AN80/AN81/TW70//TW72_11/01/2013"
• TW75 "Quantity normally not greater than 200_ 02/20/2013 ; Should not be used with AN70/AN71/AN72/AN73/AN75/AN78/AN80/AN81/TW70/TW73_11/01/2013"

And yes the way you have your table set up is how my table is setup. In addition a driver cannot be used twice on a project.

[twinnyfo]

This "may" be relatively "simple", at least in concept, but will require some work on your part.

Here is the concept:

I would recommend you create a new table "tblExclusions"

Field      DataType  Notes
Driver1    Number    FK to tblDrivers.Driver_ID
Driver2    Number    FK to tblDrivers.Driver_ID
Quantity   Number    Threshhold at which you start looking at exclusions
Exclusion  Text      Description of the Exclusion

Then, add all your exclusions:

"AE01 Normally not taken with AE02/05/06_ 02/20/2013
AE02 Normally not taken with AE01/03/05/06_ 02/20/2013
AN25 Quantity normally not greater than 6_ 08/13/2014 ; Should not be used with AN19 when an RRH, TMA or Diplexer are in the same position _08/13/2014"

becomes:

Driver1  Driver2  Qty  Exclusion
AE01     AE02     0    "AE01 Normally not taken with AE02"
AE01     AE05     0    "AE01 Normally not taken with AE05"
AE01     AE06     0    "AE01 Normally not taken with AE06"
AE02     AE01     0    "AE02 Normally not taken with AE01"
AE02     AE03     0    "AE02 Normally not taken with AE03"
AE02     AE05     0    "AE02 Normally not taken with AE05"
AE02     AE06     0    "AE02 Normally not taken with AE06"
AN25     AN19     6    "AN25 Quantity normally not greater than 6; Should not be used with AN19 when an RRH, TMA or Diplexer are in the same position"

Depending on your rules of operation, that last one may be two separate records: one for the quantity, one for the other driver.

The hard part: entering all those values into the Table.

Then, whenever you have a project entered, when the user selects a driver, another driver and a quantity, the form would look up those values in the table and if there is a match, the exclusion is returned, either as a text box or a popup message. You could also set options so that a mismatch results in clearing of the secondary driver/quantity.

"Conceptually" this is one option, and it seems fairly straightforward. Otherwise, searching for these exclusions whould require incredibly intense text searches which would probably take forever, even on the number of exclusions you have listed.

I'll stand by to provide additional assistance if you need it.

[dunlapc23]

What should the primary key be for tblExclusions?

[Rabbit]

It sounds to me like you're giving them a warning after the fact. That is, after they have made an "illegal" assignment. Perhaps you should think about checking the assignment before they actually save the data to the database.

It also sounds like mutually exclusive is defined by the first 2 characters, in which case you don't need an extra table but can just check to see if an existing record with the same prefix already exists.

[dunlapc23]

Hi Rabbit,

The user can still enter the data into the database they would just be required to enter driver exception notes.

The process is when the user enter 2 drivers that are mutually exclusive, a message is added to the Exception Reason field. Once the message is added then the user will be required to enter data into a note field.