Help with template (1 Viewer)

Hi! i have a small project which gathers information either by call or email on complaints received from customers in tbl_Incident
Based on the type of complain it should be able to decide which template it should take from tbl_Rules.

I have 2 rules set in tbl_Rules, when i capture info in tbl_Incident, based on field "SRSubType" & "SRSubSubType" it should be able to decide the template either 1st row or 2nd row is selected. It is displayed in "Form1" field "N1"

I have attached DB just for better clarification.
Any suggestions, pls let me know.

Database has tables but no data, no forms and no code. There are no records in tblRules.

So just how do you 'gather' information? Simple old-fashioned data entry directly to tables?

If you want to automate some process, then build forms and write code. When you have a specific issue, then post a question.

Agree with June. You've not given us enough information in your description nor your database. You can't just jump into the issue you are struggling with and hope we get it--you must tell us in a very generic way what the real life situation is that you hope to capture with your database. No database jargon--just pretend you are telling a group of elementary kids what it is you guys do and how this database will help you.

After that, complete your database. tbl_Rules has no data and no real fields and you haven't completed the Relationship tool so we don't know how all this data is tied together. Do that as well.

Finally, from what I do see in your tables, you need to learn about normalization (https://en.wikipedia.org/wiki/Database_normalization). The 2 tables that house most of your data are not properly laid out.

Here's the specific no-no's I see:

Duplicate tables --> tbl_IncidentDetails and tbl_VoucherDetails should be merged. They share at least 90% of the same fields, you can make the other 10% work in one table. For example, I see TemplateID in one table but not the other--when you merge them bring that field into the new table and just leave it blank if necessary.

Not properly using foreign keys --> You have tbl_StatusDetails which is fine and you even have an autonumber primary key in it (StatusId), which is great. But you don't use that autonumber primary key, instead you have 3 fields for Status information in both tbl_IncidentDetails and tbl_VoucherDetails. That is incorrect. You should only have StatusId in those 2 tables and not the 3 you have in there (STatus, StatusUpdate, StatusUpdatedBy). This is just an example, I see you have done this with a few fields (BankName/BankID;

Storing data in field names --> When you have multiple fields with similar names but prefixed/suffixed/numbered you need a new table. I see CorrectBeneficaryAC, WrongBeneficaryAC, ReversalCorrectionBenAC, etc. in tbl_VoucherDetails. Everything that comes before the 'AC' is data and should be in a field, not in a field's name. All those fields need to come out and go into another table (like you did with tbl_STatusDetails) and everything in the field name that comes before the 'AC' should go into a field called 'ACType'. Again, that's one example, you've done this a lot in your tables.

So 3 tasks--write an easy to understand overview of the big picture; read up on normalization; and work on your tables, complete the Relationship Tool in Access and post back a screenshot. Don't worry or even think about forms or reports, you've got a ton to focus on getting your tables correct.

Thanks June7 & plog for the advice. Apologize for the wrong db uploaded in post #1
I have deleted and uploaded revised v2.

As mentioned in post #1, i have set 2 rules in tbl_Rules.

Based on the type of complain it should be able to decide which template it should take from tbl_Rules.

I have 2 rules set in tbl_Rules, when i capture info in tbl_Incident, based on field "SRSubType" & "SRSubSubType" it should be able to decide the template either 1st row or 2nd row is selected. It is displayed in "Form1" field "N1"

thanks

Agree with plog. Your data structure needs work. Why would tbl_Status have StatusUpdate and StatusUpdatedBy fields? Save StatusID as foreign key into tbl_Incident.

You want combobox choice to determine which rule and expressions to pull from tbl_Rules? How is combobox choice of SRSubSubType supposed to indicate which rule to pull? - there are no values in tbl_Rules that match combobox items.

Hi! June7,
I have deleted tbl_Status, it was not part of this db. I just saw you amended the post#5 and tried to use in the code, however i am getting an error.

kindly note i have mentioned only 2 rules however there will be more than 30+ rules. If that is the right approach.

If you read version of my post with code, I removed it because it did not seem appropriate. Need to answer questions and clarify what you are really trying to do.

thanks June7

My project is to capture complaints received via email or calls.
User will capture the info in Form1.
Combination of text fields (ORG & SRSubType & SRSubSubType) gives
e.g. "TOP IN Clearing Amount claimed wrongly" match will give me corresponding result in field NR1 on Form1 which is the output of tbl_Rules from field N2 & N3

This is what i am trying to do.
Thanks

I do not know if you are saving anything by putting this in a table. Probably the easiest thing to do is is in the query just make all of the possible concatenations for N1, N2, NR1. Lets say you have 3 rules then you will have NR1_A, NR1_B, NR1_C. Then in the after update of the combos and on the on current event choose which NR1 to bind to your text box. So you change the control sources based on the rule. The easiest thing with this is there is no debugging because you will immediately see if the string resolves.

HI! MajP, thank you for responding.

I will not be saving the data, but i will be exporting the data on monthly basis.
The main reason behind this is to relive the user to not retype the data (NR1) end of the month.

I will do as you mentioned in query and show the result. but i would love to do this with vba. That is cause i am not sure how many rules will be there and later if i need to add any more rules have to keep on adding the query.

Let me try the query and see how it goes.
Thank you very much for your suggestion.

I will not be saving the data

Click to expand...

But you are saving it in a table if you want to accumulate it until the end of the month.

I'm not sure what the purpose of setting a value in txtValues is. You don't use it any other place. So the case statement can be reduced to:
If there was some reason to do something specific for each value then the case statement would look like:

HI! Pat Hartman, No i will not be saving the data to table, but will b exporting the data from the query with the rules set.

The txtvalue was just to capture the text after selecting in Option group. (OpsRisk) I can remove this field(txtvalue), it was added since I was not able to convert the numeric value to text
.
i have used the short version of select case provided by you. However the value does not change until i move to next page. I tried to use the requery but it does not change until i move to next page.

The code should work the same way your original code worked. It is just cut down to the essential elements.

Can you post what you have now please.

Thanks Pat Hartman,

i have updated a new version with 3 Rules in tbl_Rules as mentioned by MajP.
I am not able to put the code together to get the NR1 when i select SRSubType

i have added more fields to table, "tbl_Rules

let me know if this will be fine and how can i get this working pls.
i have attached txt file with the 3 rules which are available in tbl_Rules and modified the Rule to:
Rule1: NR1_IN_S1
Rule2: NR1_IN_E1
Rule3: NR1_OW_E1

thanks

HI!

Just wanted to clarify further the steps it should perform:

1) Check whether the difference between ChqAmount and ClaimedAmount is greater than Zero or less than Zero or is equal to Zero

2) Check the data input in SRSubType, e.g. IN Clearing; OW Clearing; etc.

3) Update field "Claim" as Excess; Short Or NA, (based on above 1 + 2)

4) Lookup in table "tbl_Rules" if the above is matching and select field NR1.
Point 4 i feel there is something missing for meeting this condition.

Although above looks simple, due to my poor knowledge in coding and other db objects, I have been struggling to get from point 2 onwards.

Thanks

OK, you changed the option group to use my second suggestion. Apparently, you are using this text field in some other process that is not visible in the form you posted.

But there is no other code in the database so I don't know what I am supposed to look at. I don't see anything related to "rules"

I'm not sure where MajP was going with this table. It looks like he suggested storing some pieces of code in a table and then using them in whatever it is you need to do. Although I like tablizing stuff, I only do it if doing so makes the process self running and I don't see that happening here. It looks like you still need code to determine which "rule" applies so giving up the benefit of using a text editor with intellisense doesn't make sense to me so far. Apparently this all relates to some other thread that I never saw. The others also recommended table changes which I think have not been done Having multiple threads going on the same topic gets confusing for all of us.

If you are asking us to write this code for you, you might get lucky and find someone with time to do your work for free but generally we don't do your work. We help you to do it. Advice is free but work should be paid for.

Keep working on the rules document. Reading it does not give me the information I would need to determine how to categorize each record. Which fields need to be examined? What values are you looking for to determine the processing flow?

Thanks Pat Hartman, for the advice and suggestions and yes your time. I have tried to answer your last post.

OK, you changed the option group to use my second suggestion. Apparently, you are using this text field in some other process that is not visible in the form you posted.

But there is no other code in the database so I don't know what I am supposed to look at. I don't see anything related to "rules"

Click to expand...

Well to the best of my ability i have tried to do some coding, thinking someone might correct it.

I'm not sure where MajP was going with this table. It looks like he suggested storing some pieces of code in a table and then using them in whatever it is you need to do. Although I like tablizing stuff, I only do it if doing so makes the process self running and I don't see that happening here. It looks like you still need code to determine which "rule" applies so giving up the benefit of using a text editor with intellisense doesn't make sense to me so far. Apparently this all relates to some other thread that I never saw. The others also recommended table changes which I think have not been done Having multiple threads going on the same topic gets confusing for all of us.

Click to expand...

Well i take advice/suggestion and try to implement, if it does not work, i will try something else.
I had mention to MajP that i wanted to keep the codes in the table to avoid changing the codes in future if at all i have to add more rules.

If you are asking us to write this code for you, you might get lucky and find someone with time to do your work for free but generally we don't do your work. We help you to do it. Advice is free but work should be paid for.

Click to expand...

I am not asking you to write the code nor enforcing my work on you. Just requesting for help. If you dont want to help, its fine.
i have zero knowledge on coding but i have got every possible help from all you guru's and thankful for that.

Keep working on the rules document. Reading it does not give me the information I would need to determine how to categorize each record. Which fields need to be examined? What values are you looking for to determine the processing flow?

Click to expand...

Well i will try to look on the net to find an alternate.
Thank you for your piece of advice and time.

Going from no coding to some coding is a pretty big step which is why I suggested working on the logic first. As I read your text file, I don't have enough information to actually write code. Code, especially something like VBA is a lot like English with more stringent syntax rules. Essentially your rules will end up being a series of If statements. even if you don't know the precise syntax, you need to get your logic down to the point where you can say something like

If fieldx = "some value" and field2 is > 1000 and field3 is > todaysDate, ....
Some conditions might be connected with OR rather than and but the point is, we need to get down to data fields and what you are looking for to determine how to classify something.

If you can get your logic to that point, helping you convert to VBA will be easy enough for us because at that point it becomes just a matter of getting the syntax right. But we aren't close enough to get to the point where we can help convert your logic to actual code.

I didn't mean to sound harsh. I was trying to manage expectations. We really do want to help you. It's just that some things are a step too far. Such as figuring out what the exact logic is to categorize each item.

Thanks Pat Hartman for responding. No you did not sound harsh at all.
I guess it becomes difficult sometimes to put on paper on what one want to express and expect the other side to understand it easily.

i know there are different ways to do this, like the iif; Select case; Nested if; Switch; lookup table; etc.. Before even opening a thread i do my research on the net look for match which can solve my problem. If i am lucky i will copy that in my project and build on it. But when i dont find the right answers i try to take help from you all here.
This is a great forum and i am lucky to find equally great people here.

I will try and put a logic hope that will make it somewhat easier to help me.

Lets say, i have multiple strings which i have Concatenated and put it a table "tbl_Rule" Each row is a rule in this table. (refer to field "NR1")

My goal is: When the user captures information on the Form1, it would lookup in this table and display the result on the form in field (NR1)

The below is what i am trying to explain the logic. Basically there are two things that i am struggling to get.
A) Update field "Claim" on Form1 based on 1 & 2
1) Check whether the difference between ChqAmount and ClaimedAmount is greater than Zero or less than Zero or is equal to Zero.

2) Check the data input in SRSubType, e.g. IN Clearing; OW Clearing; etc.

e.g. If > 0 And SRSubType = "IN Clearing" then Claim = "Short"

I placed the Afterupdate code on field "AdjustedAmount" I tried to put it on field "Claim" it gives me blank result.

and

B) Update field "NR1" based on point3 on Form1

3) Lookup in table "tbl_Rules" under field "N1Statement" if SRSubType and SRSubSubType is matching then select field "Rule" and display NR1 from this table on to the form txt field "NR1"

This was the difficult part to explain which i am hoping have explained it now.

I'm not asking you to write real code. We don't know yet whether it makes to use a case statement or a nested If or both. What you write will be considered pseudo code. It is English but at a very low level. Computers are very fast but very stupid. When you write code, you have to consider all the possibilities and provide a logic flow for each condition. If we write code for you, it isn't magic. We MUST know every detail of how you determine each of the three possibilities.

For example, condition A1 mentions three possibilities but doesn't specify what to do for any of them.

To get a record from the table, the logic needs to look at some fields and come up with one of three values.

Pretend you are talking to someone very stupid. Use small words and don't leave out any details. If you don't say it, it won't happen. Do not assume anything.

To help you organize the logic, use indentation to nest it.
This is read if the first condition is true continue on to the next and then the next until you get to some resolution.