newbie needs help with recordset 
Author Message
 newbie needs help with recordset

Hi,

I'm new to VBA in Access and I would like to loop through the records
(and fields per record) in a query(table). I've read about recordsets
but don't know how to properly use them in code.

So I want something like this:

for i = 1 to NrRecordsInQuery
    for j = 1 to NrFieldsPerRecord
        <read field value>
        <process field value>
    next j
next i

any help appreciated,

Raaf



Sun, 16 Jan 2005 17:08:06 GMT  
 newbie needs help with recordset
Hi Raaf,
The following should help

Public Function FN_Demo()
'************************
'**** to access tables in VBA you need to set a few things
up first
'**** the first thing you will need to do is define some
variables to hold the
'**** database, and recordset object.

'**** First you will need to check that you have
referenced the DAO object in VBA
'**** on the menu click Tools then References, if you
cannot see
'**** 'Microsoft DAO 3.6 object library' with a tick next
to it at the top of
'**** the list, search down the list until you get to
'**** 'Microsoft DAO 3.6 object library', click the
tickbox.   Now close access and
'**** reopen it to make sure. the following should now
work.

Dim DB          As Database
Dim RST         As Recordset    '* this will hold the
contents of the table
Dim TXT_Return  As String       '* this will hold the
contents of the field you require

'**** now you need to reference the database.
Set DB = CurrentDb

'**** the CurrentDB reference above will reference the
database you are currently
'**** in, it is the easyest way.   there are other methods
that will allow you to
'**** access another database.

'**** now set up the recordset
'**** the table I will use in this demo is
named 'TM_Client' and has three fields
'**** Client_ID, TXT_Fname and TXT_SName, you should
change this to the name of the table you want to look at.

Set RST = DB.OpenRecordset("TM_Client")

'**** again there are other things you could add to the
above line but this
'**** does the job.

'**** now to get the data out of the recordset (table) we
can loop through
'**** it one record at a time using a Do Loop statment

Do Until RST.EOF '* the EOF means end of file (table)
    TXT_Return = RST.Fields(1)  '* this will place the
data held in the
                                '* FirstName field of the
table into
                                '* the var TXT_Return
                                '* in VBA the Fields are
numbered from 0 to Field(1) refers to TXT_FName.
    Debug.Print TXT_Return      '* prints the data onto
the debug window
                                '* the icon with the
exclamation mark will
                                '* open the debug window
    RST.MoveNext '* move to the next record
Loop

'**** to finish off you need to close the objects you have
opened
RST.Close
DB.Close

You can copy and past this into a module if you want, it
will make it easyer to understand.

Have fun

Mike J. Soames

Quote:
>-----Original Message-----
>Hi,

>I'm new to VBA in Access and I would like to loop through
the records
>(and fields per record) in a query(table). I've read
about recordsets
>but don't know how to properly use them in code.

>So I want something like this:

>for i = 1 to NrRecordsInQuery
>    for j = 1 to NrFieldsPerRecord
>        <read field value>
>        <process field value>
>    next j
>next i

>any help appreciated,

>Raaf
>.



Sun, 16 Jan 2005 18:50:39 GMT  
 newbie needs help with recordset
Thanks Mike, it works perfectly (I'm still using DAO 3.51 but that
seems to work fine too)

Regards,
Raaf



Tue, 18 Jan 2005 02:47:09 GMT  
 
 [ 3 post ] 

 Relevant Pages 

1. Oracle Newbie needs help getting recordset with multiple records

2. Newbie needs help writing to recordset

3. newbie needs help!!- looking up and displaying recordsets

4. Newbie...Need to grab Parent and Child Recordsets

5. Need Report to look at SQL Data source ( VB / CR newbie needs help)

6. Newbie needing help badly!! Please help

7. HELP! Newbie needs help with SQL for VB3

8. Please Help Newbie Crystal Report / VB help needed.

9. HELP - Newbie needs help on simple problem!

10. Newbie needs help running help file

11. Help..Newbie needs help with collections...and arrays

12. Help Newbie Looping through recordset

 

 
Powered by phpBB® Forum Software