Page 1 of 2 12 LastLast
Results 1 to 15 of 23
  1. #1
    twgonder is offline Expert
    Windows 10 Access 2021
    Join Date
    Jun 2022
    Location
    Colombia
    Posts
    521

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

    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?

    Click image for larger version. 

Name:	230328UpdtQ1.jpg 
Views:	25 
Size:	77.1 KB 
ID:	50002

    and the view



    Click image for larger version. 

Name:	230328UpdtQ2.jpg 
Views:	24 
Size:	63.2 KB 
ID:	50003

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,082
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    10,873
    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

  4. #4
    twgonder is offline Expert
    Windows 10 Access 2021
    Join Date
    Jun 2022
    Location
    Colombia
    Posts
    521
    @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.

  5. #5
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    10,873
    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.

  6. #6
    twgonder is offline Expert
    Windows 10 Access 2021
    Join Date
    Jun 2022
    Location
    Colombia
    Posts
    521
    Quote Originally Posted by CJ_London View Post
    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.

  7. #7
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,082
    Did you review article I linked to in post 2 including the Lebans link in that article?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  8. #8
    twgonder is offline Expert
    Windows 10 Access 2021
    Join Date
    Jun 2022
    Location
    Colombia
    Posts
    521
    Quote Originally Posted by June7 View Post
    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.

  9. #9
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,082
    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.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  10. #10
    twgonder is offline Expert
    Windows 10 Access 2021
    Join Date
    Jun 2022
    Location
    Colombia
    Posts
    521

    Trying, but still not getting it

    Quote Originally Posted by CJ_London View Post
    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).

    Code:
    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:
    Click image for larger version. 

Name:	230328UpdtQ4.jpg 
Views:	22 
Size:	53.9 KB 
ID:	50007

  11. #11
    twgonder is offline Expert
    Windows 10 Access 2021
    Join Date
    Jun 2022
    Location
    Colombia
    Posts
    521
    @ June7, Post #9. Thanks, I'm too bleary-eyed to continue tonight, I just tested CJL's idea. I'll dig into Lebans tomorrow.

  12. #12
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    10,873
    Looks like you didn’t pass a -1 parameter in the criteria per my original post
    Click image for larger version. 

Name:	image_2023-03-29_085836218.jpg 
Views:	23 
Size:	123.4 KB 
ID:	50008

    Click image for larger version. 

Name:	image_2023-03-29_085938914.png 
Views:	22 
Size:	9.5 KB 
ID:	50009

    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

  13. #13
    twgonder is offline Expert
    Windows 10 Access 2021
    Join Date
    Jun 2022
    Location
    Colombia
    Posts
    521

    Somewhat working

    Quote Originally Posted by CJ_London View Post
    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.

  14. #14
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    10,873
    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

  15. #15
    twgonder is offline Expert
    Windows 10 Access 2021
    Join Date
    Jun 2022
    Location
    Colombia
    Posts
    521

    Going again so soon?

    Quote Originally Posted by CJ_London View Post
    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.

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Update Query with Select Function to Complex
    By KelliganP in forum Queries
    Replies: 10
    Last Post: 01-27-2021, 02:50 PM
  2. Replies: 2
    Last Post: 03-03-2020, 12:34 AM
  3. Update query with replace function fails
    By Keven in forum Queries
    Replies: 5
    Last Post: 07-11-2016, 08:59 AM
  4. UPDATE query with CAST function
    By hsavignac in forum Queries
    Replies: 1
    Last Post: 12-07-2015, 12:22 PM
  5. [access 2007] Update Query And Function
    By Zoroxeus in forum Queries
    Replies: 4
    Last Post: 04-16-2012, 04:19 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums