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

 

[ChrisOjeda]

So far Good Friday isn't being to good to me...someone please post a reply?

 

[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 help me?

 

[JoyInOK]

'Bout to walk out the door, so I'll leave you with some standard code, and tell you that if you want to use a query for the open command, use the QueryDefs object to copy the SQL for the query into a string variable. You can then use the string variable in the open command.

If you're still in trouble Monday, I'll check back.

To create a recordset

Dim rstCurrent As Recordset
Dim strSQL As String
strSQL = "SELECT [4_FY94CLMS].VICTPAYEE, [4_FY94CLMS].AMT1, [4_SERVICE PROVIDERS].PAYEE, [4_FY94CLMS].REMARKS, [4_FY94CLMS].INVOICE FROM [4_SERVICE PROVIDERS] RIGHT JOIN 4_FY94CLMS ON [4_SERVICE PROVIDERS].SP_TAX_ID = [4_FY94CLMS].TAXID WHERE ((([4_FY94CLMS].REMARKS)='" & strDate & "' Or ([4_FY94CLMS].REMARKS)='" & str4DigitDate & "') AND (([4_FY94CLMS].INVOICE)='" & ClaimNumber & "'));"
Set rstCurrent = CurrentDb.OpenRecordset(strSQL)
If rstCurrent.RecordCount = 0 Then 'there are no payments issued
MsgBox "There were no payments issued for this board date on this claim.", vbOKOnly, "No Payments"
Set rstCurrent = Nothing
Exit Sub
End If
'move to the last record to get an accurate record count
rstCurrent.MoveLast
rstCurrent.MoveFirst
Set rstCurrent = Nothing

Loop Through a recordset

rstCurrent.MoveFirst
If Not rstCurrent.EOF Then
Dim j As Integer
For j = 1 To rstCurrent.RecordCount
If UCase(rstCurrent.Fields("VICTPAYEE&quot) = "P" Then
strPayeeList = strPayeeList & MixCase(rstCurrent.Fields("PAYEE&quot) & " " & Chr(9) & FormatCurrency(rstCurrent.Fields("[AMT1]&quot) & Chr(13)
sglPaymentTotal = sglPaymentTotal + rstCurrent.Fields("[AMT1]&quot
End If
rstCurrent.MoveNext
Next j
End If 'in case there are no payees