Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 409
  • Last Modified:

VB Function repeatedly called

Hi,
   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
0
DiuQiL
Asked:
DiuQiL
  • 2
1 Solution
 
peter57rCommented:
Hi DiuQiL,
What event are you calling this function from?

Pete
0
 
DiuQiLAuthor Commented:
Pete,
       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.
0
 
peter57rCommented:
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?

Pete
0
 
harfangCommented:
Hi,

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.

Cheers!
(°v°)
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now