none
Creating a rules engine and manage it with a user interface RRS feed

  • Question

  • Hello,

    I have some rules that I'd like to apply to some fields in one of my Access tables.  This would be things like, if an item contains the word "network", then set another column in the same table to a value of network.  If an item contains the word "cell phone", then set another column in the same table to a value of cell phone.  I know one can do this via code.  But, to make it easier to maintain these rules, I would also like to be able to enter, change and run the rules via a simple user interface, most likely a user form. 

    The actual application is so small that it does not warrant purchasing a separate business rules software tool.  And, while the "code only" option is doable, it's not in our long-term best interest to maintain these rules in code since that would require a developer to do so.  Having a simple, yet effective interface to update the rules would allow a non-developer to keep these rules up to date.

    Is this too much for MS Access to do?

    Thanks,

    Scotty81

    Monday, October 31, 2016 1:44 PM

Answers

All replies

  • In some cases this is where Data Macros can come into play. See:

    That said, when I see statements like: "an item contains the word "network"", item contains the word "cell phone" I seriously question the normalization of your table structure.  These should be coming from lookup tables and the main table should only be storing foreign key values.  Should you wish to discuss this at all, we are always here to try and help.


    Daniel Pineault, 2010-2016
    Microsoft MVP Professional Support: http://www.cardaconsultants.com
    MS Access Tips and Code Samples: http://www.devhut.net

    • Marked as answer by Scotty81 Monday, October 31, 2016 5:15 PM
    Monday, October 31, 2016 2:01 PM
  • Daniel,

    Thanks for the references; I will check them out.  I certainly agree with your comment on the lack of normalization on the input data.  The rest of the database is normalized, but assigning line items to the various allowed categories (e.g. network, cell phone) is currently a manual process.  And, the input data comes from other sources outside the control of this database.  So, my goal was to add automated work flow around the import of the initial data.

    Regards,

    Scotty81

    Monday, October 31, 2016 3:48 PM
  • If a text box contains the words you wish to search, perhaps this function will help:

    https://support.office.com/en-us/article/InStr-Function-85D3392C-3B1C-4232-BB18-77CD0CB8A55B?ui=en-US&rs=en-US&ad=US&fromAR=1

    You can search specific strings in other strings to detemine if they exist. The function returns a number, so create a VBA routine in the text box After Update event:

    If InStr(start, string1, string2 , compare) >0 Then

      This text box field = "Network" or "Cell Phone" or whatever criteria is searched

    End If

    You should also check the Mid Function here:

    https://support.office.com/en-us/article/Mid-Function-427E6895-822C-44EE-B34A-564A28F2532C

    This might help as well.

    Monday, October 31, 2016 4:36 PM
  • "And, while the "code only" option is doable, it's not in our long-term best interest to maintain these rules in code since that would require a developer to do so."

    Just my experience/opinion, but I have always found macros much more difficult to work with than code!  it take me orders of magnitude more time to open, flip back and forth ... than it does to review code which has tools to perform Find/Replace and various tasks can be centralized for easy maintenance.  Also, based on needing to create Macros, one way or another, you need a developer.

    Just my 2 cents worth ...


    Daniel Pineault, 2010-2016
    Microsoft MVP Professional Support: http://www.cardaconsultants.com
    MS Access Tips and Code Samples: http://www.devhut.net

    Monday, October 31, 2016 5:01 PM
  • @Lawrence, Scotty81 was looking for a non-VBA solution.

    Daniel Pineault, 2010-2016
    Microsoft MVP Professional Support: http://www.cardaconsultants.com
    MS Access Tips and Code Samples: http://www.devhut.net

    Monday, October 31, 2016 5:02 PM
  • I can certainly see how the Access Data Macros will help.  I am familiar with Excel VBA, and was hoping not to reproduce something like that in Access.  So, considering I didn't want to have to integrate (and/or) buy another tool for this application, the data macros will fit the bill.

    Thanks!

    Monday, October 31, 2016 5:14 PM
  • You might also try putting the search function directly into a text box Control Source. If you have a text box for each criteria on your form ("Cell Phone", "Network" etc.), then you can still use the InStr or Mid function to get strings from another text box. For example, if you have a text box named CellPhone, you can use the functions in the CellPhone Control Source to display your criteria. If the text box you want to search is called MyText, then you can put the following in the Control Source of the CellPhone text box:

    =IIf(InStr(1, MyText , "Cell Phone" , 1)>0, "Cell Phone", Null)

    Monday, October 31, 2016 6:45 PM