Get a function to run for each row in an update query?

[twgonder]

@CJL, post #12 So which did I use improperly, was it True of False?

Attachment 50010



Attachment 50011

You said you were going to drop off, but maybe someone else will be as confused as I am by your responses.

[Minty]

The normal Access way to use a function in a query that requires a different response per line is to feed it something in the parameters that changes per line?
It appeared that wasn't the case in your initial example
E.g.

Function fnAddOne (MyNumber as Long) As Long

     fnAddOne =  MyNumber + 1

End Function

Now I hope you wouldn't be surprised but if you only feed this with 2 on every record you will get 3 back on every line.
The code linked to showed how to feed the previous generated number back into the same function, hence getting an increasing value.

When previewing the results, because of the way Access work, if you scroll up and down in the query window access will reload the results causing the displayed preview calculations to go haywire.
Remember you are looking a dynamic interpretation of the results of an action query, not the actual query.

If you want to preview the changes when this type of query is running dump the results to a temporary table. That will cement the actual output and allow you to examine it correctly at your leisure.
You can't preview an action query in most RBDM systems, Access tries to let you but in certain circumstances (this is is one of them) the results are not reliable.

[twgonder]

I'll finish off this thread and return to the actual application problem with some observations that may help others in a similar circumstance.

I am building a comprehensive geography table. It includes levels of "places" from the planet earth down to neighborhoods.
I've had to cull and organize this data from many different spreadsheets or csv files.
Sequential IDs are used and it's nice to keep the table somewhat organized (but not absolutely necessary) by hierarchy.
As such, I've relied on the order of records as they appear in the datasheet view to make sense of it all.

I got the spreadsheet data to finally load with a sequential number, but ran into this wrinkle while viewing the data to make sure I was doing it right.

The source table looks like this:



While the result looks like this:



Look at Autauga Country in both images.
Notice that the append query didn't process the records in the same order that they appear in the datasheet view of the source table.
I could have very easily used the display order to associate counties with their states, based on a range and sequential numbers.
Again, it wasn't critical now, it made debugging and validation difficult. For some applications it may be a problem.

As to the two descriptions being the same (for those observants), I often do that on an initial load to get the complete name or description. Then I run an update query that compares the short and long description (the short may have been truncated), and if they are equal, then I delete the long description and maybe abbreviate the short description if they aren't equal.

[twgonder]

Now I hope you wouldn't be surprised but if you only feed this with 2 on every record you will get 3 back on every line....

I'm not sure if that first line is a declaration or interrogative.
Of course I wouldn't be surprised. But, as I explained, that's not what my function was doing. Posts #1 & #15 (maybe more).

However, thanks for the explanation of what I discovered through hard trial-and-error. The way Access works (different than 90% of the rest of the programming world). I'm sure others will also appreciate the reasoning too.

[Minty]

Well I'm afraid the actual results from your function would beg to differ with your expectations of what it should do.
I don't know what your function was trying to achieve as I haven't seen the code, which would have been pertinent for this question.

Yet again you are lambasting Access for not working the way you want it to work, is it possibly time to consider you either
a) are using the wrong tool.
b) need to accept what Access is with it's foibles (I'm not saying it's perfect!), and accept the work arounds
c) harping on that you know you know everything is better somewhere else, but aren't using this mythical other easy to use development tool that works your way?

It's quite tiresome to try and help and then be told we're not understanding, and we're always wrong, and that's "not the answer I'm looking for".
After it's happened dozens of times, perhaps the perceived problem doesn't lie with the responders?

[twgonder]

Well I'm afraid the actual results from your function would beg to differ with your expectations of what it should do.

Yes, you're correct, and hence the whole point of the thread

I don't know what your function was trying to achieve as I haven't seen the code, which would have been pertinent for this question.

It was explained several times, but so you don't have to go back and read the posts (#1, #15), its job is to return an ID. It's quite big, with multiple locks. It's not so important to post here. Since the parameters tell it which ID is needed, they don't change. Hence the problem.

Yet again you are lambasting Access for not working the way you want it to work, is it possibly time to consider you either...

Or MS could consider that when parameters don't change, that the actual function might legitimately return a different value? Hence, it needs to run for EACH row. This is an example of MS trying to be too clever for our own good.

It's quite tiresome to try and help and then be told we're not understanding, and we're always wrong, and that's "not the answer I'm looking for".
After it's happened dozens of times, perhaps the perceived problem doesn't lie with the responders?

Or maybe I give a legitimate reason, as in this example, and people take it as a personal affront rather than a observation that might enhance the functionality of Access, to be more in line with how other systems in the real-world work and with what advanced developers need?

For example, CJLondon shared the solution he uses (so, he's experienced the problem too; and I borrowed from his solution after getting a better explanation) to get around the problem contained in this thread, but even though he has a solution, that doesn't make the fundamental problem go away. As we all know, problems have a way of cascading. Or as the old saying goes, two wrongs (a stupid problem and a stupid solution) don't make a right.

[Minty]

Windowing/Ranking functions would be incredibly useful in Access, but I can't see Microsoft doing that any time soon, as it encroaches too much on what SQL server offers, and for whatever reason they still see the two products as rivals.

Look up Row_Number() or Rank() in a SQL Server help file, they do exactly what you are after natively and extremely efficiently.
Access in this respect is a poor relative and it's why a lot of developers switch to it as a BE storage option pretty quickly.

[twgonder]

@Minty, #22 Thank you for that clarification.