Record Finding (Novice) 1

[darrenhubbard]

Hi All,

A real beginners question, and I'm sure it's really simple to solve, but it's driving me up the wall:

Basically, I have a query and I need to be able to through it, record by record, and store various pieces of the data in a varable AS TEXT. ie. in verypseudocode:

For i= 1 to dcount(queryname)
var1 = query(queryname).record(i).field(fieldname)
var2 = query(queryname).record(i).field(fieldname2)
...
etc
next i

It's important that var1 and var2, etc are text for later manipulations.

Any thoughts?

Cheers,

Darren

 

[jrbarnett]

Hi Darren,

Use a recordset object (look this up in the help for more details about how to read data and manipulate it from within code). You don't say which version of Access you are using, but the code from any version since 97 will be similar to:

Dim db As Database
Dim rs As RecordSet

Dim var1, Var2 ' variables for pulling data off the system. Put data type on end for faster use.

Set db = CurrentDB
Set rs = Db.OpenRecordset ("queryname&quot ' can either be SQL SELECT statement or name of stored query.

Do Until rs.EOF
var1 = rs!field1 ' field1 and field2 represent columns in the query.
var2 = rs!field2
... ' do whatever you want to do with this data
rs.MoveNext
Loop

' tidy up afterwards
rs.Close
Set db = Nothing

If you are using Access 2002/XP you will need to go to Tools -> References and add the reference to the Data Access Objects 3.6 library, but it should be OK for 97 and 2000.

John

 

[darrenhubbard]

Cheer for that but I'm getting a "type mismatch" error on the OpenRecordset line... all I've done is made the line look like this:

Set rs = db.OpenRecordset("MemberAdded&quot

Where MemberAdded is a query. BTW, MemberAdded is a query on another query -- is that relevant?

Cheers,

Darren

 

[darrenhubbard]

I managed to fix the "type mismatch" error by removing the

Dim rs as Recordset

line (I rarely use Explicit anyway). And then it all works perfectly?!?!?!?