Please HELP. Question on looping through records from query

[ChrisOjeda]

I am trying to write code that will run a select query. I then wish to step through the records one at a time to pull some data from them and send it to Excel. I am having issues with running the query and stepping through... Please help. Also, can someone explain the parameters for opening a recordset (I can't get explanations in the object browser)...

My situation:
Access 2000
Query in same database named C-PRIME DAILY SUMMARY
Code below...

Dim cnCurrent As ADODB.Connection
Dim rstDailyDQ As ADODB.Recordset

Set cnCurrent = CurrentProject.Connection
Set rstDailyDQ = New ADODB.Recordset
rstDailyDQ.ActiveConnection = cnCurrent
rstDailyDQ.Open ("C-PRIME DAILY SUMMARY&quot
With rstDailyDQ
.MoveFirst
Do Until rrstDailyDQs.EOF
MsgBox (rstDailyDQ![DAYS DQ])
.MoveNext
Loop
.Close
End With

 

[EasyTarget]

do something like this

Dim rstDailyDQ As ADODB.Recordset
Set rstDailyDQ = New ADODB.Recordset

With rstDailyDQ
Set .ActiveConnection = CurrentProject.Connection
.CursorType = adOpenStatic
.Open "tbl_name", options:=adCmdTable
end with

Do Until rstDailyDQ.EOF
MsgBox "yadda yadda"
rstDailyDQ.MoveNext

loop

 

[ChrisOjeda]

I tried that out but I got an infiniti loop? The query only brings back like 9 records but when I use that code it I just keep getting message boxes. Thanks for the try. Any other ideas?

 

[ChrisOjeda]

Okay it does not loop continuously with a Table. But it does with a query. I am sure it has to do with the parameter "adCmdTable"...What option should I use for a query that I just want to step through each record???

 

[ChrisOjeda]

According to this I can't use an Access query object for Recordset.Open...see below...

Use the Source property to specify a data source for a Recordset object using one of the following: a Command object variable, an SQL statement, a stored procedure, or a table name.

So then the question is how do open a Query in my DB to step throught he records???

 

[ChrisOjeda]

Can someone please give me help on this...?

 

[WildHare]

WHY are you doing this with code? Write a MAKE TABLE query that gets the data you want, and export it to Excel.

What can you do in a record-by-record loop that you can't do in SQL?

Jim



Me? Ambivalent? Well, yes and no....
Another free Access forum:

http://www.forumco.com/jmhare

More Access stuff at

http://www.jmhare.com/wildhare2.htm

 

[ChrisOjeda]

Thanks... Chris