VB Function repeatedly called

Posted on 2006-04-26
Last Modified: 2008-02-01
   I have a VB function which is called to generate a psuedo-random number for a particular field in a query.  This works okay, except for that whenever the field is scrolled out of view, or the query result window resized, the values change.  I can't check if a value is already in the field as this causes a cyclic references.

Can someone suggest a way for the VBA function to be called once and only once (or at least not update the query field)

- Andrew
Question by:DiuQiL
    LVL 77

    Expert Comment

    Hi DiuQiL,
    What event are you calling this function from?

    LVL 1

    Author Comment

           It is being called from a query:

    SELECT 1 AS sib1PlmnScopeValueTag, "Active" AS [Cell State]
                   Get_Scrambling_Code([Cell State],[sib1PlmnScopeValueTag]) AS primaryScramblingCode,
    FROM CDRRaw_2;

    The VB:

    Public Function Get_Scrambling_Code(isActive, pib) As Integer
        If (StrComp(isActive, "Active") = 0) Then
            gScramblingCode(pib) = (gScramblingCode(pib) + 8) Mod 511
        End If
        Get_Scrambling_Code = gScramblingCode(pib)
    End Function

    where gScramblingCode is a global array.
    LVL 77

    Expert Comment

    I follow that bit.

    But to rephrase my previous Q, where is the query?
    It looks like a saved query, so is it being run in an event procedure or is referenced in a control property or what?

    LVL 58

    Accepted Solution


    You are looking for a forward-rolling query. That does not exist in Access. Even if you set the query type to Snapshot, your function will be called whenever Access decides it has to refresh the screen or the data. You have no control over that in a query.

    Perhaps the most likely chance to have the function called exactly once would be to use an update query, append query, or make-table query. But even then, I wouldn't bet a toenail on your function being called just once.

    So, question: why in a query?

    It seems you want to do that in a recordset, looping exactly once over the records.


    Featured Post

    Find Ransomware Secrets With All-Source Analysis

    Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

    Join & Write a Comment

    It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
    Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
    Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
    Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

    732 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    21 Experts available now in Live!

    Get 1:1 Help Now