looping thru query

[slimjen]

All, using access 2010. I have some code to loop thru a table in a module. The following is a snippet:

Set rstCusts = CurrentDb().OpenRecordset("EA01_qryCust_email")
                    With rstCusts
                        ' Get the total e-mail count.
                        .MoveLast
                        intNumEmailsToCreate = Nz(.RecordCount, 0)
                        .MoveFirst

This code loops thru a table in my database, but the table has records and it causes duplication. I would like to instead use the query which I thought I was using but it still pulls from the table. How do I get the loop thru the query "EA01_qryCust_email" instead?
Thanks

[orange]

??? Your code seems to indicate you want the recordcount of the recordset.

Your post says you want to loop through the recordset.
Generic loop:

 dim db as dao.database
dim rs as dao.recordset
set db = currentdb
set rs = db.openrecordset ("your table or query name")
Do while not rs.eof
    ------------------------
    --do something here
    ------------------------
rs.movenext
Loop

I'm surprised that you have 500+ post and haven't programmed a Do -Loop with a recordset???
Did you try Google?

Good luck

[slimjen]

I appreciate your reply. I have and this is what I have now. I was just asking is there a way to loop thru the query and not the table which this seems to be doing in conjunction with the query. I thought maybe something like:

Set qdf = db.QueryDef

or something.

[orange]

What I posted was a method of looping thru a recordset. The recordset could be based on a Table or a Query (which Access treats as a "table").

[June7]

Don't have to loop through a recordset just to get the record count.

Since the code references the query as source for recordset, that is what is being looped.

[Snayjay]

If all you want is a record count, can't you just do a DCOUNT on the primary key of the table in question?

Dim x
x = DCount("[PRIMARYKEY_FIELD]","TABLE_NAME")

And if you only want to count certain records, use the WHERE part of the DCount.

Dim x
x=DCount("[PRIMARYKEY_FIELD]","TABLE_NAME","[TEXTFIELD] = '" & SEARCHWORD & "'")

Or if you have a Query made already, just change the Table Name to the query.

Dim x
x=DCount("[PRIMARYKEY_FIELD]","QUERY_NAME")

Hope that helps.