The easiest thread today......probably? 1

[FatherJack]

I am new to Access VBA having spent several years programming Excel VBA. I am trying to write a small module to create 8 char passwords. As a start I have created a table with 2 fields and 36 rows.field 1 - key. field 2 chars a - z and 0 - 9. I have a sql string to query the table for the character according to the id which matches a random number generated each time (extract below).
QUESTION: how do you get the result of the select query into a variable?
Sub sql_module()
Dim db As Database
Dim rs As Recordset
Dim mysql As QueryDef
Dim myrndnum As Integer
Dim a As Integer
Dim t As String
Set db = CurrentDb
Set mysql = db.CreateQueryDef("&quot
Set rs = db.OpenRecordset("select * from [data]", dbOpenDynaset)
For a = 1 To 8
Randomize
myrndnum = Int((36 * Rnd) + 1) ' Generate random value between 1 and 36.
mysql.SQL = "Select data.letter from data where data.id = '" & myrndnum & "';"
t = ##result of select query##
Next a
End Sub

 

[christheprogrammer]

Jack:
TRY THIS (I didn't run this so there may be syntax errors.):
function x()
dim dbs as database, qdf as querydef, rst as recordset
dim qdf2 as querydef, rst2 as recordset
set qdf = db.CreateQueryDef("SOME_QUERY","SELECT * FROM [data];&quot
set rst = db.openrecordset("SOME_QUERY&quot
for a=1 to 8
randomize
myrndnum = int((36*rnd)+1)
set qdf2 = dbs.createquerydef("SOME_QUERY2",SELECT [data].letter FROM [data] where [data].id = " & myrndnum & ";&quot
set rst2 = dbs.openrecordset("SOME_QUERY2&quot
rst2.Movefirst
t = rst2!field0 'Check the name of the field here...
rst2.close
docmd.deleteobject acquery, "SOME_QUERY2"
next a
docmd.deleteobject acquery, "SOME_QUERY"
end function

 

[Amiel]

Padre (FatherJack) you appear to be selecting a single record from the table based on a random value of from 1 to 36. Interesting stuff.

At any rate use this ...

Dim rs as Recordset
Dim rs2 as Recordset
Dim stringVariable as String

Set db = DBEngine(0)(0) 'saves a gazillion clock cycles.
Set rs = db.OpenRecordset("select * from [data]", dbOpenDynaset) 'dont understand this.

For a = 1 To 8
Randomize
myrndnum = Int((36 * Rnd) + 1)' Generate random value between 1 and 36.

set rs2 = db.Openrecordset("Select [data].[letter] from data where [data].[id] =""" & myrndnum & """;&quot 'A string
debug.print rs2(0) ' print the value in the debug window

'Assign the value to a variable.
stringVariable = rs2(0)

Next a

Loop

End Sub

 

[FatherJack]

Thanks all. This is what I have ended up with - because chris came up with the goods 1st:

Function x()
Dim a As Integer
Dim t As String
Dim myrndnum As Integer
Dim mysql As String, mysql2 As String
Dim dbs As Database
Dim qdf As QueryDef
Dim rst As Recordset
Dim qdf2 As QueryDef
Dim rst2 As Recordset
start:
On Error GoTo errorhandler
mysql = "Select [data].letter from data"
Set dbs = CurrentDb
Set qdf = dbs.CreateQueryDef("SOME_QUERY", mysql)
Set rst = dbs.OpenRecordset("SOME_QUERY&quot
For a = 1 To 8
Randomize
myrndnum = Int((36 * Rnd) + 1)
mysql2 = "SELECT [data].letter FROM [data] where [data].id = " & myrndnum & ";"
Set qdf2 = dbs.CreateQueryDef("SOME_QUERY2", mysql2)
Set rst2 = dbs.OpenRecordset("SOME_QUERY2&quot
rst2.MoveFirst
t = t & rst2!letter
rst2.Close
DoCmd.DeleteObject acQuery, "SOME_QUERY2"
Next a
DoCmd.DeleteObject acQuery, "SOME_QUERY"

MsgBox "t = " & t

Exit Sub
errorhandler:
Select Case Err
Case 3012 'if query already exists
DoCmd.DeleteObject acQuery, "SOME_QUERY"
DoCmd.DeleteObject acQuery, "SOME_QUERY2"
GoTo start
Case Else
MsgBox "Err = " & Err
End Select
End Function

Why did i have to use ""rst2!letter"" rather than ""rst2!field___"" ? I could not get any field references to return a value

 

[christheprogrammer]

Jack, Glad I could help, I assume the reason you had to use rst!letter is because your query's fields are set (SELECT data.letter FROM data WHERE data.id = SOMERAND) by Microsoft's Query Engine. I used field0 because I usually just use this method to get a recordcount and the query contains no field names. ie.
"SELECT * FROM

;"
the_number = rst!field0

Cheers
Chris