HOW DO I GET AN ACCESS RECORD INTO A VARIABLE?

[MarcMellor]

I want to select a group of records from an Access database according to certain criteria and then pick one at random and deposit the IDNumber (the Primary Key AutoNumber) in a variable to be used later. This is what I have so far:

Data1.RecordSource = "SELECT * FROM QuestionsDatabase_
WHERE Subject = 'Biology'"
Data1.refresh
QuestionsLimit = Data1.Recordset.RecordCount
Data1.Refresh
CursorNumber = Int(Rnd * QuestionsLimit) + 1
Data1.RecordSource Move CursorNumber
QuestionID = Text1.Text 'QuestionId is my variable into
'which I want to deposit the ID number and
'Text1.Text is linked to the Access IDNumber
'field
Data1.RecordSource = "SELECT * FROM QuestionsDatabase_
WHERE [IDNumber] = QuestionID"
Data1.Refresh

Unfortunately I always get a value of 2 for my QuestionID variable even though I get random numbers for CursorNumber. Depositing the IDNumber in a text box and then picking it up in a variable seems a bit cumbersum and may simply be too slow. Is there a way of doing it directly? I had a look at the GetRows method but I can't really understand my book well enough to get it to work. Any ideas?

 

[onbleu]

Maybe the reason why you always have the value of two is because the number of rows processed by the Jet engine is just the first record.

I've read from Help that:
" The number of records returned by the Recordset can be determined by moving to the last record in the Recordset and examining the Recordset object's RecordCount property. Before you move to the last record, the value returned by the RecordCount property only reflects the number of rows processed by the Jet engine. "

Data1.RecordSource = "SELECT * FROM QuestionsDatabase_
WHERE Subject = 'Biology'"
Data1.refresh

'Add this code
DATA1.RECORDSET.MOVELAST

QuestionsLimit = Data1.Recordset.RecordCount

'Add this code
DATA1.RECORDSET.MOVEFIRST

Data1.Refresh

CursorNumber = Int(Rnd * QuestionsLimit) + 1
Data1.RecordSource Move CursorNumber


QuestionID = Text1.Text 'QuestionId is my variable into
'which I want to deposit the ID number and
'Text1.Text is linked to the Access IDNumber
'field

'or maybe you could directly link the record to your variable using this code instead of using the text1

QuestionID = data1.recordset!Fieldname
' where fieldname is the name of the field you wish to acess and i think in this case, the ID number

Data1.RecordSource = "SELECT * FROM QuestionsDatabase_
WHERE [IDNumber] = QuestionID"
Data1.Refresh


To have more information look on help about recordset.
Hope this helps!
Godbless

 

[MarcMellor]

I tried your suggestion, Onbleu, but exactly the same problem. I have some text boxes bound to the database temporarily so I can see what's going on. I've also got an MSFlexgrid control as COUNT doesn't seem to work without it. What seems to be happening is that the MoveLast takes us to the End of the selection, the MoveFirst takes us back to the beginning (which is IDNumber 2) and then the Move CursorNumber takes us nowhere at all, the text box shows the IDNumber of the current record as 2 but the following SELECT statement does not recognise QuestionID as having any value so I get an error statement.