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

[twgonder]

I haven't got a lot of experience with update queries, but I've gotten some to work.



This time, I'm trying to use a function to update one column. The function is written in VBA and, in simple terms, creates a sequential number.
When the query is in view mode, the VBA code seems to run for just the first row and then repeats that value on each subsequent row (as seen in 1st column).
What I need is for each row to have a new sequential number from the VBA function.
Is there a trick for this?



and the view

[June7]

Review http://allenbrowne.com/ranking.html

[CJ_London]

review post#10 in this link
https://www.access-programmers.co.uk.../#post-1798447

this sets numbering from zero - don't know what your function is doing but you could either incorporate the code into it, or add it to your function

yourfunction(....)+rownumber()

all you need is the start number

important it is reset the static variable using the criteria in the query per the link, otherwise it won't reset until you close/reopen your db

I often use if when importing data that does not have a uniqueID

[twgonder]

@June7 & CJ_London

I reviewed both articles, they are interesting, but not my situation.
My function just reads a sequential number from a table, based on some criteria. It works fine and updates the table fine.
That it does can be seen in the number in the first column, it's incremented since 100101 with every run.
This is a simple function, I plan to write one much more complicated to use in an update query,
but I can't do that until I figure out how to get SQL to run the function for each row, not just run it once
and then copy the one-time result to every row afterward.

[CJ_London]

Shame you don't actually try some of these suggestions and understand how they work. I suspect the only way your function has worked for you up up to now is you have been adding one record at a time. Otherwise it would have worked on this occasion.

If that is the case, the only way for your function as is would be to use vba to loop through the recordset and append each record one at a time. Otherwise you need a 'trigger' to ensure your function is called every time. I can't see the whole function but my guess is that it is only called once because the parameter values don't change.

If you had looked more closely at my link, you would have seen you need a parameter that changes for each row - and the parameter needs to be unique - perhaps your stCD column - to force the function to be called for each row. You don't have to do anything with the parameter, just declare it as a variant so you can pass, number text, whatever. No guarantee that will work for your function since I have no idea what it does, but that is how the code in my link works.

[twgonder]

Shame you don't actually try some of these suggestions....

Well my impatient one, I was busy with a few other things today, including my son's birthday. It's a long article with the typical (useless) questions of "why do you want to do this or that". I'm trying to get through all the stuff now, including your post again. I'm still trying to figure out how it applies to an append query ( I'm not clicking on rows as your post says to do), but once I understand what you've said, I'll test it and get back. It may be tomorrow, I hope that doesn't twist your knickers.

Instead of complaining that I don't understand what you're saying, maybe you could explain better as how it pertains to the problem at hand? I'm not sure if you mean unique parameter for the entire column. This was a simple example of needing a sequential number. Further down in the my second table of counties, codes for the counties do repeat as several states have counties with a non unique codes. In any case, I'm still baffled as how to apply your suggestion to an append query.

My function just gets a number and increments it, as yours does, it just reads a table to get the correct number to use, as there are many. The question is, why does it only run once in an append query? Of course the parameters don't change to get the function to do its job, they don't need to. Nor does any other field have to "change" to work when an update or append query runs. When I've used the function in the past, it's within the VBA code of a form, not an append query. In the VBA code, I haven't had the problem of VBA saying in essence, I just ran that function a second ago, I'm not going to run it again for you as the append query seems to be doing.

[June7]

Did you review article I linked to in post 2 including the Lebans link in that article?

[twgonder]

Did you review article I linked to in post 2 including the Lebans link in that article?

Well, the title was about numbering in forms, not in append queries, but I'll go look based on your suggestion again, and see if I can figure out what's going on.

[June7]

I've known this article for a long time but never used the code. I downloaded Lebans sample database and tested an UPDATE sql there.

I added a field named Seq to Hortacraft table.

I ran query:

UPDATE Hortacraft SET Seq = Serialize("Hortacraft","Code",[Code]);

The new field was updated.

[twgonder]

Shame you don't actually try some of these suggestions and understand how they work..

Okay, after some time staring at this way too late at night, I think what you're saying is there has to "be a change" for the function to run.
So, for grins I tried a slightly modified version of your code (ditching my code) and added what I think you're saying is needed to the append query (for a newer table).

Function fRowNumber(Optional r As Variant = -1) As Variant
  Static X As Long


  If r = -1 Then
    X = 100191
  Else
    X = X + 1
  End If
  fRowNumber = X


End Function

I ran it to see what happens, and I get nothing back for results. I'll play with it, but still not sure of what exactly I should be doing.

Here's the new query for counties:

Attachment 50006

The source table looks like this:

[twgonder]

@ June7, Post #9. Thanks, I'm too bleary-eyed to continue tonight, I just tested CJL's idea. I'll dig into Lebans tomorrow.

[CJ_London]

Looks like you didn’t pass a -1 parameter in the criteria per my original post




Or perhaps you were just clicking around - that will change the numbers - but you wouldn't be doing that running an append query under normal circumstances

[twgonder]

Looks like you didn’t pass a -1 parameter in the criteria per my original post

I noticed after posting that Access moved the criteria to a new column that was off the screen.
Although not ideal, I got the new function to work by putting a breakpoint in the code so I could manually set X to zero and then send the code on its way.
Another wrinkle appeared, I've got to do some testing on that before continuing here.

However, thank you for the suggestions, it took me a bit to understand the what and why.
It still seems odd to me that Access requires a change to iterate a function for a subsequent row.
I guess the solution for my function, which has several legitimate parameters for VBA/form use, is to add a dummy argument for update/append queries.

[CJ_London]

It still seems odd to me that Access requires a change to iterate a function for a subsequent row.

if it did you would no doubt find it odd (and slower) when it has all the same parameters and you wanted it to return the same value each time. No pleasing some people. Really not interested in your wrinkles. You start your thread with an append query then in a post you deleted you referred to an update query.

I'm still trying to figure out how it applies to an update query

So I'm dropping off this thread

[twgonder]

if it did you would no doubt find it odd (and slower) when it has all the same parameters and you wanted it to return the same value each time. No pleasing some people. Really not interested in your wrinkles. You start your thread with an append query then in a post you deleted you referred to an update query.

Who says a function has to return the same results each time it runs with the same parameters? For example (direct from post #1 of this thread) I feed the function some parameters defining that I want the next ID for a given table. The parameters don't change but the returned ID does. For all your experience, I'm surprised that simple concept escaped you (and MS).

As to the wrinkle, maybe someone else (you aren't the only one in this forum) will be interested in avoiding a pitfall. So, that's a very self-centered comment. Hmmm.

One day you accuse me of having too much in the post, without an example, and then other days you want to say I didn't add enough or changed the topic (from append to update in your dropping off post). I'm using both update and append queries in this project. Talk about trolls that can't be pleased, thanks for the perfect evidence.

So I'm dropping off this thread

Yes, as I've said many times in other posts that you've run from, you are free to do as you wish, but as explained above, your reason for doing so isn't logical and perhaps even childish.

I used to give prospective programmers a little test. A good programmer could accomplish it with less than ten lines of code, some of that being a good comment or two. There were times that a few prospects, that self-regarded highly their qualifications for the job, snuck out of the testing room for a bathroom break, and mysteriously disappeared. One even crawled out of the office, on hands-and-knees, in front of the receptionist's desk so as to not be seen leaving. We always got a good laugh at that. I will wager that those types are now lurking in forums giving poor, incomplete or hard-to-decipher hit-and-run "advice". The few that passed, that I hired and mentored are now VPs with some techno title in Fortune 500 companies.

Yes, I'm no expert in Access. Some days I wake up and ask myself, "Why are you doing {all} this {again}?" The Dot-com bubble was very good to me. I could be sitting on the French Riviera enjoying a nice cocktail, no? Or travel to the Alps to practice my favorite sport of paragliding. Perhaps build a new tennis court beside the hacienda for my personal enjoyment? Then I remember it's all for friends and family that, too, need a better future of self-reliance (rather than just sitting idle, waiting for their inheritance). My thanks, in proxy for them, when you do manage to be more helpful than arrogant.