nForums.net > MS access :grrr

Awai

May 18 2004, 01:01 PM

hiya all,

i have a small problem with ms access querys and visual basic - in short i have a nice new query laid out which seaches through my database and pulls out all the records with "555" in one of the colums. the next part of the query then uses a formula to generate a random letters/numbers which i'm using as the password for something else. as a demo what i'm looking to generate is a list which has the following look to it:

code serial randomthingy
555 0101 aaa111
555 0102 bbb121
555 0103 ccc114
555 0104 ddd511
555 0105 eee161

however my code (which will be posted below) only seems to execute once and gives the same random number to all my outputs so the randomthing colum is full of aaa111 rather than the mixed stuff i'm hoping for...

heres the code i've written into a module called Randomthing:

CODE

Public Function RandomPwd(ialph As Integer, inum As Integer) As String
'*************************************************
**************************************
' Procedure : RandomPwd
' DateTime : 2003-11-26 16:02
' Author : Glenn Lloyd - Argee Services
' Purpose : generate a random alphanumeric password with ialph letters and inum digits
' Returns : String
'*************************************************
**************************************
'

On Error GoTo RandomPwd_Error
Const clow = "A" 'generate only uppercase 'A' to 'Z'
Const chigh = "Z"
Const llow = 1 'we want lowest numeric to be 1

Dim lhigh As Long 'maximum value of numeric digits

Dim spword As String 'for our results

Dim cchr As String 'individual characters generated
Dim lnum As Long 'individual digits generated

Dim ictr As Integer

Dim sfmt As String

spword = "" 'start with an empty string

Randomize 'initialize the randome number generator

For ictr = 1 To ialph 'generate specified number of letters
cchr = Chr(Int((Asc(chigh) - Asc(clow) + 1) * Rnd() + Asc(clow)))

spword = spword & cchr 'add letters to result string
Next ictr

lhigh = 10 ^ inum - 1 'maximum numeric value of digits
'concatenate result string with specified number of digits

sfmt = Right(Str(lhigh), Len(Str(lhigh) - 1)) 'remove the leading character

For ictr = 1 To Len(sfmt)
Mid(sfmt, ictr, 1) = "0" 'convert format to zeros
Next ictr
'generate and format the numeric part

spword = spword + Format((Int(lhigh - llow + 1) * Rnd() + llow), sfmt)

RandomPwd = spword

RandomPwd_Exit:
On Error GoTo 0
Exit Function

RandomPwd_Error:

MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure RandomPwd of Module Module4"

Resume RandomPwd_Exit

End Function

i assume i need to either call a loop in the code or in the query which i invoke the code with: "pass: RandomPwd(2,3)"

any thought much appreciated.

Awai.

Awai

May 20 2004, 10:58 AM

no probs now guys have sorted the wee bugger - ahh its midday time for a celebratory beer

Awai.

This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.