assigning items from a list of 1000 to a form field

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

sek0910 - 09 Aug 2004 19:48 GMT

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

Reply to this Message

Tim Ferguson - 10 Aug 2004 14:24 GMT

> 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

Reply to this Message