Public Function, Saved in Table as text, pull out result in Query

[mikejaytlabustro]

Hello access programmers!

Need some help please..

Problem explained in attached picture, sorry i cant explain it without illustration.

 

[arnelgp]

you can use Eval() on your query to return the results from the function:

Select ID, Description, Function, Eval(yourTable.[Function]) As Result From yourTable

or

Select ID, Description, Function, FormatCurrency(Eval(yourTable.[Function])) As Result From yourTable

**
note the result will be left-justified. to get it right-justtified, edit your query.
click on the "Result" column. On Property Sheet->Format put 0.

 

[The_Doc_Man]

Taking a literalist viewpoint for a moment, your function cannot be written as you explained it, though a similar function MIGHT be possible.

You show "GetValue1()" and "GetValue2()", which presumably leads to a large number of potential "getvaluexxx()" functions. If that number were a parameter and the evaluation of it could occur inside the single "GetValue" function call, you might make it work. But functions don't do well with the original syntax you showed us because function names cannot be late-bound. Everything has to be defined up front before you launch the app. You CANNOT define a function in a code section AND EXECUTE IT in a running system because the compiler needs to get in and adjust things. Doing so would cause a RESET of your project and cause all variables to re-initialize on you. In essence, you cannot do it that way.

Arnel's reference to the EVAL function would help to evaluate expressions at run-time. However, if the items named in the EVAL string didn't exist before you started execution, you could not expect to evaluate them because EVAL would be unable to find them.

Second, and in a more general viewpoint - you don't want to put stuff like that in a table anyway. From your brief discussion, you are using that to show that you want a value from some control on your form. This is normally done via form-binding. The strange part is that you SEEM to be implying multiple records being managed at once from a single form. I could have read that wrong, but that is how it appears. You are showing that the table has something in a slot from control100 and another record has something in the corresponding slot but from a different control.

OK, you can do a lot of stuff with code and make your app do anything you darned well please, but this seems to be a confused design that perhaps should be revisited. Therefore, I'm with Uncle G on this one. Tell us IN ENGLISH (no jargon please) what you really are trying to achieve. What is the BUSINESS goal?

 

[mikejaytlabustro]

I'd like to know a little bit more about what you're doing. How you got to this stage and where you are going next...

Kindly see the attached picture for more detailed explanation. Many thanks!

 

[mikejaytlabustro]

OK, you can do a lot of stuff with code and make your app do anything you darned well please, but this seems to be a confused design that perhaps should be revisited. Therefore, I'm with Uncle G on this one. Tell us IN ENGLISH (no jargon please) what you really are trying to achieve. What is the BUSINESS goal?

Kindly see the attached picture for more detailed explanation. Many thanks!

 

[mikejaytlabustro]

you can use Eval() on your query to return the results from the function:

**
note the result will be left-justified. to get it right-justtified, edit your query.
click on the "Result" column. On Property Sheet->Format put 0.

Eval() works! Another piece of magic code to add on my favorite lists. Thank you!
In case there is a better work around in my case, kindly see the attached picture for more detailed explanation

 

[The_Doc_Man]

This sort of thing looks more "Excel"-ish than "Access"-ish in nature.

Something that appears misleading is that I begin to get a sneaking suspicion that your use of the word "table" isn't fully conformant to the strict Access definition of that word. I can certainly be wrong, but what you are showing just doesn't require Access.

 

[mikejaytlabustro]

This sort of thing looks more "Excel"-ish than "Access"-ish in nature.

Something that appears misleading is that I begin to get a sneaking suspicion that your use of the word "table" isn't fully conformant to the strict Access definition of that word. I can certainly be wrong, but what you are showing just doesn't require Access.

This is really on access, please see attached screenshots.

The downsides:
1. Form should always be active
2. Every time i update/save the record, need to execute delete and append query.

Maybe i will try to use DLookup?

Iif([EntrySide]="Debit",Dlookup("FieldName","JournalTable","JournalID=" & Me.JournalID)

FunctionName will be replaced by FieldName

This will eliminate the above downsides, Im just worried if Dlookup will make the process slower..

 

[The_Doc_Man]

2. Every time i update/save the record, need to execute delete and append query.

In other words, you are not tracking transactions? You are updating a single set of records that contain some line items that are at best loosely bound to the form? (I say "loosely bound" because of these indirect pointers you are trying to build.) You are in essence replacing the previous values with current values?

At least at the moment, that is what I think I see. You are using the functions such as are shown in Capture2.PNG of post #9 so that you can pull data from the form using a short reference, though in fact you could use a longer reference. But that means that the tables you describe are actually METADATA, not data, and this is NOT a normal Access usage of a table. Not saying it can't be done, just that it is far from standard.

This data setup is SO not normalized that I have a hard time picturing the relationships in my head. The fact that you are not using transactions and the fact that you are splitting stuff that comes from a single form into multiple records in a table makes this a TOTALLY non-standard use of Access forms.

Your complexity (and difficulty) comes from a deep design issue and because you have obscured the problem with this complex design, I'm at a loss to offer advice. Your continued insistence on showing us pictures instead of actually describing the overall problem in English also confuses matters. My colleagues and I can be helpful even for this situation, but my alarm bells are going off in my head saying "seriously unnormalized data."