looping records and adding to a query

[paulminne]

Hi,

I am trying to loop through a number of records in a query and select them based on a condition. Once the records are selected I want to create a record in another query and continue the loop until it is finished.

More simply.

1. Loop through records in query
2. Find record based on condition
3. Create new record in another query
4. Continue Loop
5. (If any more records are found, then continue to add them to the new query)

This is the code so far:
*********************************************
Dim strDate As String
Dim strStartDate As String
Dim strFinishDate As String
Dim i As Date

Dim rs As ADODB.Recordset
Dim db As Database
Dim strSQL As String
Dim qdef As QueryDef

stDocName = "DueCalibration"

strDate = DATE
strStartDate = DateAdd("m", -24, strDate)
strStartDate = Format(strStartDate, "Medium Date&quot
strFinishDate = DateAdd("m", -23, strDate)
strFinishDate = Format(strFinishDate, "Medium Date&quot

MsgBox "search range is from " & strStartDate & " to " & strFinishDate & "", vbExclamation

For i = strStartDate To strFinishDate

DoCmd.Hourglass True

Set rs = New ADODB.Recordset

rs.ActiveConnection = CurrentProject.Connection

stLinkCriteria = "[JobDate]= #" & strStartDate & "#"

strSQL = "SELECT DISTINCT * FROM JobsComplete WHERE " & _
stLinkCriteria

Set db = CurrentDb

'Open the recordset
rs.Open strSQL

If (rs.EOF) Then
strStartDate = DateAdd("d", 1, strStartDate)
strStartDate = Format(strStartDate, "Medium Date&quot
Else
db.QueryDefs.Delete "TEST"
Set qdef = db.CreateQueryDef("TEST", strSQL)
Set rs = Nothing
strStartDate = Format(strStartDate, "Medium Date&quot
End If

Next i

DoCmd.OpenReport stDocName, acViewPreview, , stLinkCriteria

DoCmd.Hourglass False

Set rs = Nothing
Set db = Nothing

***************************************************

Regards,

- Paul

 

[KenReay]

Hi

You do not add records to a query, you add records to a table.

You have a curious mixture of ADO syntax and DAO syntax, why not just stick with one or the other?

Why would you want to add the records matching the condition to another (table), when you can pick them out of the exsiting dataset via a suitable query? Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[paulminne]

OK, sorry I know that was a little confusing. In essence, what I want to do is loop through a number of records in a query. Then with all the records found matching a certain criteria, I want to place them into a report and print the report.

But I am unsure how I should go about this.

Any suggestions would be greatly appreciated...

Thanks,

-Paul

 

[KenReay]

Hi

OK like I said, you modify your existing query to include the further criteria you are using. If you are using this query elsewhere and do not wish to midify it, then copy it.

I cannot tell from your code what the furtehr criteria is, except that it appears to have something to do with 'StartDate'.

When you have the query modified as required, create a report based on that query, the report wizrd will guide you through this.

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK