VB Function repeatedly called

   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
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

DiuQiLAuthor Commented:
       It is being called from a query:

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

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.
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?


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.


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.