Results 1 to 2 of 2
  1. #1
    mcertini is offline Novice
    Windows 10 Access 2016
    Join Date
    Jul 2021
    Posts
    1

    Thumbs up Function For Repeating Prior Record Field Results

    Hi,

    I am currently trying to create a custom function that will accomplish the following:


    1. Look at a field (Field#3) in a record in a query. Field#1, Field#2 & Field#3 are coming from a different query.
    2. Determine if the field is populated
    3. If it is populated print the results to a different query field (Field#4)
    4. Save the populated field to a variable for use in the next record iteration
    5. Advance to the next record
    6. Determine if the field is populated (if it is not populated)
    7. Print to the query the saved data from #4 above to (Field#4)




    The following is the layout of the query:

    Field#1 Field#2 Field#3 Field#4
    xxx xxx 100505
    xxx xxx Null or ""
    xxx xxx Null or ""
    xxx xxx Null or ""
    xxx xxx 100490
    xxx xxx Null or ""
    xxx xxx Null or ""
    xxx xxx Null or ""

    What I would like to see:

    Field#1 Field#2 Field#3 Field#4
    xxx xxx 100505 100505
    xxx xxx Null or "" 100505
    xxx xxx Null or "" 100505
    xxx xxx Null or "" 100505
    xxx xxx 100490 100490
    xxx xxx Null or "" 100490
    xxx xxx Null or "" 100490
    xxx xxx Null or "" 100490

    The following is the function that I have started. The problem I have encountered is that I am losing the variable as described in #4 above. When the next record processes, the variable is wiped out. I don't know how to save it outside of the routine so the next iteration has a value to compare to. I am successful in the first If statement as the first encountered product number is being saved to the query. Where I am not successful is in retaining varSecondProd and triggering the Else. Every cell evaluates to greater than zero even though it is "".

    Code:
    Public Function ProdNum(Prod) As Variant
    
    Dim varFirstProd As Variant
    Dim varSecondProd As Variant
    
    varFirstProd = Prod
    
    If varFirstProd > 0 Then
        ProdNum = varFirstProd
        varSecondProd = varFirstProd
    Else
        If varFirstProd = "" Then
             ProdNum = varSecondProd
        End If
    End If
    
    End Function

  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,825
    Have to declare variable as Public or Global. That means it must be declared in module header, not within a procedure.

    Alternatives:

    1) store value in a textbox on form

    2) TempVars
    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.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 1
    Last Post: 02-26-2016, 01:41 PM
  2. Replies: 9
    Last Post: 06-17-2014, 08:15 PM
  3. Repeating Function
    By Code5OnYou in forum Forms
    Replies: 4
    Last Post: 01-12-2012, 01:55 PM
  4. Repeating data in a field for a new record
    By NOTLguy in forum Forms
    Replies: 3
    Last Post: 10-29-2010, 07:27 PM
  5. Replies: 1
    Last Post: 02-26-2009, 11:31 AM

Tags for this Thread

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