assigning items from a list of 1000 to a form field

[sek0910]

I have a list of 1000 discrete passwords (and that list
may grow from time to time). I'd like a system where
when I click a command button ("Generate Password"),
Access will take the first password from the list, insert
it in a text box on a form (that will then be stored in a
master table with the record for the particular user),
and then delete that password from the list so it can't
be used again.
Again thought?
Thanks, Steve

 

[Tim Ferguson]

it in a text box on a form (that will then be stored in a
master table with the record for the particular user),
and then delete that password from the list so it can't
be used again.
Again thought?

public function GetNextPassword() as string

dim strSQL as String
dim strAnswer as string
dim db as Database

' get "next" password, whatever that means
strsql = "select top 1 ptext from passwords " & _
"order by ptext"

' handle to database
set db = currentdb()

' catch the null answer if there are no records left
' you could use a NZ() just as well
strAnswer = dfirst("ptext", strsql) & vbNullString

' was it okay?
If len(stranswer)>0 then
' yes; delete if from the list
strsql = "delete from passwords " & _
"where ptext = """ & stranswer & """"

' brave here -- no error trapping!
db.execute strsql, dbfailonerror

End If

' return the password
GetNextPassword = strAnswer

End Function


Not tested, so usual caveats apply, but it's not very complicated.
Hope it helps


Tim F