Looping a function in a query

[HanSolo]

Hey folks,

I'm attempting to use a random password generating function in a query,
but when running the query, the function is only called once. This
results in the same random password for all of my rows. Can anyone
help me to get the function to loop for each row (resulting in a
different random password in each row)? It's okay if I get duplicates
if it doesn't happen often.

Thanks and here's the current state of the random password generating
function:

Public Function fPasswordGenerator() As String


Dim intLoop As Integer
Dim strChar As String
Dim strDummy As String
strChar = "ABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890"
Randomize
For intLoop = 1 To 6
strDummy = strDummy & Mid(strChar, Int(Rnd * Len(strChar)) + 1,
1)
Next intLoop
fPasswordGenerator = strDummy
fExit:
On Error Resume Next
Exit Function

End Function

 

[Ken Snell \(MVP\)]

The query optimizer will not cause the function to be called for each
selected record unless the function has a field from the query's source
table(s) as one of its arguments. That forces the function to be called each
time because the value of the field may be different for each record.

Show us the SQL statement that you're using. Or just add a dummy argument to
your function to accept the value of a field from the query, and ignore that
value in your function. Then include a field from one of the query's source
tables in the function call.

 

[George Nicholson]

Rework your function a little so that it requires an argument, even if it
doesn't actually do anything with the argument:

Public Function fPasswordGenerator(Optional int as Integer) As String

Then, in your query:
Password: fPasswordGenerator(Len(AnyFieldInTheTable))

This forces the function to be recalculated for each record.

There may be a more efficient method to achieve the same thing, but I've
tested this and it seems to generate a bunch of different random passwords.

HTH,

 

[Marshall Barton]

I'm attempting to use a random password generating function in a query,
but when running the query, the function is only called once. This
results in the same random password for all of my rows. Can anyone
help me to get the function to loop for each row (resulting in a
different random password in each row)? It's okay if I get duplicates
if it doesn't happen often.

Thanks and here's the current state of the random password generating
function:

Public Function fPasswordGenerator() As String


Dim intLoop As Integer
Dim strChar As String
Dim strDummy As String
strChar = "ABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890"
Randomize
For intLoop = 1 To 6
strDummy = strDummy & Mid(strChar, Int(Rnd * Len(strChar)) + 1,
1)
Next intLoop
fPasswordGenerator = strDummy
fExit:
On Error Resume Next
Exit Function

End Function

Be very careful of this kind of thing, even after adding a
field as a dummy argument.

The function can be called multiple times for a single
record. This happenes whenever Access retrieves a record,
particularly for display purposes. A simple way to see this
effect is to open the query as a datasheet and note the
first record's value, then scroll the window so the first
record scrolls off the top and scroll it back into view.
The first record's value will probably be different what it
was before. The same effect can occur in many different
scenarios.

 

[HanSolo]

Thanks so much all, this solution worked perfectly! Marshall, thanks
so much for the warning. I've taken this into account and have worked
in a "make table" step.