Hello,
I'm trying to do some calculus on related tables.  It goes as follows:
table 1: principal
table 2: itemused
table 3: items

table 1 has a the field NumPrest and table 2 has the same and is linked to
that one
there are different entries in table 2 for any NumPrest in table 1
table 3 is linked to table 2 on itemcode and for each entry in table 2 there
is an entry in table 3, although they are often the same.

when I open the 3 tables,
jeu1=currentdb.openrecordset("Principal")
jeu2=currentdb.openrecordset("Itemused")
jeu3=currentdb.openrecordset("Items")
I can move around in jeu1, but jeu2 and jeu3 don't follow !

I mean, I go to a NumPrest in table 1 (e.g.  jeu1.movelast), but
I'm still on record1 in jeu2 and jeu3.

How do I do to switch to the related records in jeu2 and jeu3 ?

When there are only 2 tables I can do something like
temp=jeu1.numprest
do while not EOF()
    if jeu2.numprest=temp
        do calculus
    endif
loop

But, one, I can hardly believe this is the correct way, and two, it doesn't
work for the 3th table.

I'd appreciate any help or suggestion

Johan

Well, how you do this kind of thing depends on the
calculations you're doing and what you want to do with the
results.  Most often, you should create a query to connect
all the related data together into extended records.
Queries are the life {*filter*} of databases and the more you
know about them, the better off you'll be.  In this case it
might be that you can do what you want by JOINing the three
tables together.

click on the Query tab in the database window then click the
New button and select the three tables.  The top half of the
query design window should show the three tables and
probably two connecting lines between them.  Make sure the
lines connect the fields as you described above and that the
arrow points from the Principal.NumPrest to
ItemUsed.NumPrest.  Another arrow should point from
ItemUsed.ItemCode to Items.ItemCode.

Once that is set properly, then drag the fields from the
appropriate tables down to the fields area in the bottom
half of the window.  Try opening the query to make sure that
what you get is what you need for your calculations.

If all that is squared away, then you calculations code only
need to open one recordset based on the query and each
record in the recordset will have the related data for a
particular ItemCode.

On the other hand, if you really need to operate on three
recordsets, you could use the FindFirst method to locate a
related record.

jeu1=currentdb.openrecordset("Principal")
jeu2=currentdb.openrecordset("Itemused")
jeu3=currentdb.openrecordset("Items")

If jeu2.RecordCount > 0 Then
                ' find the first matching record in Itemused
        jeu2.FindFirst "NumPrest=" & jeu1!NumPrest
        If jeu2.NoMatch Then
                ' no matching records were found
        Else
                        ' find the first matching record in Items
                jeu3.FindFirst "ItemCode=" & jeu2!ItemCode
                If jeu3.NoMatch Then
                        ' no matching items were found
                Else
                        ' do some calculations
                End If
        End If
End If

You'll probably need a lot more than that, but I think that
should get you started.

--
Marsh
MVP [MS Access]