recordset as a subset of a table

[lanelouna]

hi again
this is my 2nd question today i am sorry
well
this question should be easier to deal with
u see i am trying to create a recordset from a table, whereby the value of courrier is H(it is obtained from the fom, H can be 1 or 2 or 3)
is it possible to do this?
coz i on t think thati my code is really doingwhat i want
can u help?
thank you
Lanelouna

Set RS1 = db.OpenRecordset("A0TDEFAUT_SOUSPROT", dbOpenDynaset)
With RS1
.FindFirst "COURRIER = " & H & ""
.FindNext "COURRIER = " & H & ""
Do Until RS1.EOF
For i = 0 To nbr

If (RS1!Classe = mesClasses(i)) Then
dj = dj + 1
End If
Next i
RS1.MoveNext
Loop
End With
RS1.Close

 

[lanelouna]

it is working now but i used filter instead of with RS
Set RS1 = db.OpenRecordset("A0TDEFAUT_SOUSPROT", dbOpenDynaset)
RS1.Filter = "[COURRIER] = " & H & ""
Set RS3 = RS1.OpenRecordset
thank you

Lanelouna

 

[JeremyNYC]

It would be better to write sql to pull only the records you want. Right now you are pulling all of the records and then filtering, two operations, much more data over the wire. Much faster would be to do this:
Set RS1 = db.OpenRecordset("SELECT * FROM A0TDEFAUT_SOUSPROT WHERE [COURRIER] = " & H, dbOpenDynaset)

And even faster would be to figure out which fields you need and only pull across those fields.

Jeremy

PS: I would also suggest that you leave out the cutesy lingo like c y l8tr, as it's often taken by developers to be a sign that you don't want to be taken seriously. =============
Jeremy Wallace
Designing, Developing, and Deploying Access Databases Since 1995

http://www.AlphaBetCityDataworks.com

Take a look at the Developers' section of the site for some helpful fundamentals.

 

[lanelouna]

well thank you
I can try not to use those shortcuts
and i think that i have a problem here
coz i am using them even in formal letters!
well thanks for your suggestion concerning sql, i was wondering if i could make the code faster
regards
Lina