Run an Append Query until the record count is null

[Mohsin Malik]

Hi, I have searched the internet to Run a query until the record count=0, i know that it may be done with loop, but did not know how can i do that? I want to run a query for insertion of data based on Criteria, the insertion of data is done on date basis, i want to run a loop to insert record until the record count =0, how to do this, thanks

 

[jdraw]

Record count = 0 and record count Null are quite different.

In your loop scenario you have 2 recordsets.

1)The record set of values that may be inserted, and
2)The recordset into which some values will be inserted.

The general logic

Open recordset 1
Open recordset 2

Do while Recordset1.recordCount <>  0
Read recordset1
  If Criteria Means OK to Insert then
     Insert recordset1 record's data into  Recordset2 (append a record in recordset2)
  end if
Move to next recordset1 record
loop
Close recordset1
close recordset2

Good luck with your project.

Edit:

As Pat says you can do this with a query along this format (no looping)

Insert into myTable (fld1, fld2,fld3... fldx)
Select field1,field2,field3...fieldx From MyInputRecords
WHERE MyInputRecords.Fldx matches the criteria

see Example #2 at http://www.techonthenet.com/sql/insert.php

 

[Pat Hartman]

Action queries can operate on multiple rows. No looping logic is required. Where is the data coming from? You can select rows from one table and append them to a different table (or even the same table). All the rows selected by the where clause will be appended to the target table.