Help with Variant

[Pat Hartman]

i would love to get the table, "tbl_Incident updated in the first instance, since it is not getting updated, thought of copying the data to tbl_Voucher. Perhaps this way i could copy the data.

This is a completely different issue than your original question. Your original question was about why a query wasn't working the way you thought it would. My answer was, you need to write specific functions that the query can run to return data instead of the column names from the rules table. This is EXACTLY what you did for the form. You now just need to do it again in a slightly different way to make it work in the query. This is the downside of the path you have taken. Everything requires code and more code.

Start a new thread if you have a new question please.

 

[lookforsmt]

Thanks dear Pat Hartman for your response, yes i did mention another query of copying the data on another form.
Coming to the earlier question on my db. How can i do it differently, if you can give me a clue i can work towards it. It will better to delay the project than to take the wrong path.
thanks again for the reply.

 

[Pat Hartman]

I told you how to get the query to work like your form. You need to create individual procedures that you call from the query to return each column value. The code is very similar to what you have in the form now. You just need to do it one field at a time.

 

[lookforsmt]

Thanks Pat Hartman, i have no clue on coding. Well i got the required support from this forum so came this far.
If you can give me the start code, probably that could help me to move forward.

 

[Pat Hartman]

You already have all the code you need. You just need to separate it into multiple small functions that return one value at a time.

 

[lookforsmt]

Thanks Pat Hartman, i have made it clear it don't have any idea in coding. From last 2-3 post i have been trying to say the same thing and you are trying to say the same thing again.
Anyway thanks for your time. I will check here if anyone else can give me the clue to give me a start of what you are talking.

 

[Pat Hartman]

lookforsmt,
Somehow you managed to get fairly sophisticated code to work and yet you are not willing to take a stab at breaking it up into "just the piece for fieldA" and turning it into a function. Although I post a lot of code here from existing apps and even write new code for people, I don't write code to overcome schema design issues because I strongly believe they should be fixed rather than circumvented. Sorry. Good Luck.

 

[plog]

Let's take a step back and tell us about the big picture. Give me 2 paragraphs:

1. Explain what it is your organization does. No database jargon allowed.
2. Explain what this database will assist you in achieving. A little database jargon allowed, but discouraged.

The big issue we have is tbl_Rules. You've essentially used it to store code. Most likely a lot of that should be in either in stand alone Module or in the VBA code that uses it.

For example, suppose you wanted to compile a birthday message to a customer that included how long they were a customer and use their gender (or sex, or birth designation or whatever its called in 2020). You could use your method (a table that tells a function how to compose that message) or you could just build a function that does it for you:

function get_BirthdayMessage(in_DOB, in_Gender, in_YearsCustomer)
' generates birthday message based on customer attributes

msg="Congratulations Birthday "
' start of message

Iif(in_Gender=="M") Then msg = msg & "Boy" Else msg = msg & "Girl"
' adds gender to it

msg=". Thanks for being a customer for "
...
...

get_BirthdayMessage = msg

All the code and logic is in that function and you pass it the data it needs. You're method does that clunkily in tbl_Rules and makes it hard for a human to follow it. Looking at the field [N2] that should be done in the above manner, instead of relying on a field to house the logic, you but it in a module and passes it the data it needs.